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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,647
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:

sco7ty

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

Well-known Member
Joined
Dec 15, 2004
Messages
2,647
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:

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,647
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.
 

JOCY

New Member
Joined
Mar 3, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 3, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

firefly10

New Member
Joined
Jun 10, 2017
Messages
8
Office Version
  1. 2019
jproffer thank you, old thread but the solution worked perfectly for my similar issues.
 

Forum statistics

Threads
1,136,326
Messages
5,675,116
Members
419,551
Latest member
thangxpm

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
Top