Running macros on a protected workbook?

Mark McInerney

Board Regular
Joined
Apr 4, 2012
Messages
210
Hi All,

I am trying to decipher how to run a macro when I have protected all the worksheets on my spreadsheet. I am not an experienced VBA person so appreciate any help on this.

My attempt (which is largely driven by using the 'record macro' option) is as follows:

Sub Split_Shift()
'
' Split_Shift Macro
'


'
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="W0UGHREA"
Next ws



Columns("E:O").Select
Selection.EntireColumn.Hidden = True
Columns("D:P").Select
Selection.EntireColumn.Hidden = False
Range("B13").Select

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="W0UGHREA"
Next ws
ActiveSheet.Select
End Sub

The problem I have is that I keep ending up on a different worksheet and not the one that I ran the macro from?

Any help appreciate
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Save a reference to the active sheet before your code changes the active sheet so that you can put it back just before your code finishes.

Changes / additions to your code in blue:

Code:
Sub Split_Shift()
'
' Split_Shift Macro
'
'
Dim ws As Worksheet
[COLOR=#0000ff]Dim oActive As Worksheet

Set oActive = ActiveSheet[/COLOR]

For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="W0UGHREA"
Next ws

Columns("E:O").Select
Selection.EntireColumn.Hidden = True
Columns("D:P").Select
Selection.EntireColumn.Hidden = False
Range("B13").Select

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="W0UGHREA"
Next ws

[COLOR=#0000ff]oActive.Activate[/COLOR]

ActiveSheet.Select

End Sub


Gary
 

Mark McInerney

Board Regular
Joined
Apr 4, 2012
Messages
210
Hi Gary,

Thanks very much for your help - really appreciated. Many Thanks. Mind Yourself - Mark.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,351
Messages
5,601,132
Members
414,429
Latest member
Bilaal xaka

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