Running macros on a protected workbook?

[Mark McInerney]

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

 

[Gary McMaster]

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:

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]

Hi Gary,

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