Runtime Error when running VBA

[sco7ty]

I want to protect a worksheet so that certain cells can't be edited, when I protect the sheet the I get the a run-time error when I click the next and previous buttons on my excel.

The code is below, what do I have to do so that it will work when protected?

Thanks in advance.

Sub PreviousMonth()
If ActiveSheet.Range("A3").Value = 1 Then
    Exit Sub
Else:
    Range("A3").Value = Range("A3").Value - 1
    LeaveTracker.Columns("B:NI").Hidden = True
    LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
End If
End Sub

Sub NextMonth()
If ActiveSheet.Range("A3").Value = 12 Then
    Exit Sub
Else:
    Range("A3").Value = Range("A3").Value + 1
    LeaveTracker.Columns("B:NI").Hidden = True
    LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
End If
End Sub

 

[jproffer]

Something like this. It will also work with a sheet name if the sheet you're working with isn't always the activesheet. [Sheets("NAME").UnProtect/Protect]

Sub PreviousMonth()
ActiveSheet.UnProtect "[COLOR=#ff0000]your password here, in quotes[/COLOR]"
If ActiveSheet.Range("A3").Value = 1 Then
    Exit Sub
Else:
    Range("A3").Value = Range("A3").Value - 1
    LeaveTracker.Columns("B:NI").Hidden = True
    LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
End If
End Sub

Sub NextMonth()
If ActiveSheet.Range("A3").Value = 12 Then
    Exit Sub
Else:
    Range("A3").Value = Range("A3").Value + 1
    LeaveTracker.Columns("B:NI").Hidden = True
    LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
End If
Activesheet.Protect "[COLOR=#ff0000]your password here as well[/COLOR]"
End Sub

needless to say, you want to change the red parts or your new password will be "your password here as well"

 

[sco7ty]

Thanks for your reply, however it doesnt seem to be working.

The previous button works but says the sheet is unprotected even though it is.

The next button is the opposite. I get a run time error but it says the sheet is protected.

Would it help if I attached the Excel?

 

[jproffer]

It might. I'm happy to take a look.

If you do, either unprotect all the sheets before you attach it, or make the password something you're willing to give to me.

 

[sco7ty]

Unprotected - https://www.dropbox.com/s/utm7gx0ciqareu2/Holidays 2018v2.xlsm?dl=0

 

[jproffer]

With the following code, I scrolled through to December and back with no issue.

Sub PreviousMonth()
    ActiveSheet.Unprotect "password"
        If ActiveSheet.Range("A3").Value = 1 Then
            GoTo Finish
        Else:
            Range("A3").Value = Range("A3").Value - 1
            LeaveTracker.Columns("B:NI").Hidden = True
            LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
        End If
Finish: ActiveSheet.Protect "password"
End Sub

Sub NextMonth()
    ActiveSheet.Unprotect "password"
        If ActiveSheet.Range("A3").Value = 12 Then
            GoTo Finish
        Else:
            Range("A3").Value = Range("A3").Value + 1
            LeaveTracker.Columns("B:NI").Hidden = True
            LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
        End If
Finish: ActiveSheet.Protect "password"
End Sub

BTW, the workbook you posted was completely unlocked (all cells unlocked). I locked all cells and ran it through to December again...no issue.

 

[JOCY]

Hi,

I have encountered the same issue.
After applying the code as provided, the macros worked even with protected sheet.
But then, another issue arises, at the beginning & end of month (January & December), if I keep on clicking on the arrow when it hits January & December, the sheet will automatically be unlocked.

Any idea how to solve this ?

 

[JOCY]

With the following code, I scrolled through to December and back with no issue.

Sub PreviousMonth()
    ActiveSheet.Unprotect "password"
        If ActiveSheet.Range("A3").Value = 1 Then
            GoTo Finish
        Else:
            Range("A3").Value = Range("A3").Value - 1
            LeaveTracker.Columns("B:NI").Hidden = True
            LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
        End If
Finish: ActiveSheet.Protect "password"
End Sub

Sub NextMonth()
    ActiveSheet.Unprotect "password"
        If ActiveSheet.Range("A3").Value = 12 Then
            GoTo Finish
        Else:
            Range("A3").Value = Range("A3").Value + 1
            LeaveTracker.Columns("B:NI").Hidden = True
            LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
        End If
Finish: ActiveSheet.Protect "password"
End Sub

BTW, the workbook you posted was completely unlocked (all cells unlocked). I locked all cells and ran it through to December again...no issue.

Hi,

I have encountered the same issue.
After applying the code as provided, the macros worked even with protected sheet.
But then, another issue arises, at the beginning & end of month (January & December), if I keep on clicking on the arrow when it hits January & December, the sheet will automatically be unlocked.

Any idea how to solve this

 

[firefly10]

jproffer thank you, old thread but the solution worked perfectly for my similar issues.