Runtime Error when running VBA

sco7ty

New Member
Joined
Mar 19, 2018
Messages
3
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.



Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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]
Code:
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" :)
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
With the following code, I scrolled through to December and back with no issue.

Code:
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.
 
Upvote 0
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 ?
 
Upvote 0
With the following code, I scrolled through to December and back with no issue.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top