running macros in locked sheets

[Warwick T]

Hi all,
I received help on this problem for a previous project from this forum, for which I was very grateful.
The code I was given is as follows:


Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Sheets
ws.Protect Password:="dataplan", UserInterFaceOnly:=True
Next ws
End Sub


this worked perfectly in the original project

I then tried to use the same coding in another project to do exactly the same thing.

It runs most of the code ok but baulks at hiding rows and columns - while it had the same function in the first project. An example is below - the third and fourth last rows (both starting .range) are where the debugger finds a problem.


Sub Tab_Term1()
With Sheet1
.Shapes("Term1On").Visible = msoCTrue
.Shapes("Term1Off").Visible = msoFalse
.Shapes("Term2On").Visible = msoFalse
.Shapes("Term2Off").Visible = msoCTrue
.Shapes("Term3On").Visible = msoFalse
.Shapes("Term3Off").Visible = msoCTrue
.Shapes("Term4On").Visible = msoFalse
.Shapes("Term4Off").Visible = msoCTrue
.Range("A:W").EntireColumn.Hidden = False
.Range("X:DZ").EntireColumn.Hidden = True
End With
End Sub




Any suggestions / solutions gratefully received.

regards

Warwick

 

[jmacleary]

Hi there. Modify your protect line like:

ws.Protect Password:="dataplan", UserInterFaceOnly:=True[COLOR=#ff0000], AllowFormattingColumns:=True[/COLOR]

(change in red)

 

[Warwick T]

Worked perfectly thank you. Curious why it wasn't a problem on the first project.
Greatly appreciated

 

[jmacleary]

Glad I could help and thanks for the feedback. I can't think why it worked before!