Columns & Passwords

[Kirstie Adam]

Hi all,

Gord Dibben of thes boards recently helped me out with some code to unlock
certain columns on my spreadsheet, which works fine, but now i need an
amendment......

The code below unlocks one column at a time, but i need to unlock 3 columns
at a time, columns 1,2,3 and 11 and 1,2,3 and 12 respectively.

I have tried creating a union, and then unlocking the columns in that union,
but don't know enough and wasn't sure i was doing it correctly.

I also tried .Columns(1,2,3,11) and .Columns(1),(2) etc, but again, i just
can't get it!

Any help would be appreciated!

Private Sub Workbook_Open()
Dim pword As String
With Sheets("Sheet1")
.Activate
.Unprotect Password:="kirst"
.Cells.Locked = True
End With

pword = InputBox("Enter Your Password")
Select Case pword

Case Is = "gainv": Columns(11).Cells.Locked = False
Case Is = "msauth": Columns(12).Cells.Locked = False

End Select
ActiveSheet.Protect Password:="kirst"
End Sub

Thanks,

Kirstie

 

[Dave Peterson]

I'm not sure how 1, 2, 3, and 11 get to be 3 columns, but maybe you could change
this portion:

Private Sub Workbook_Open()
Dim pword As String
With Sheets("Sheet1")
'.Activate 'shouldn't be required.
.Unprotect Password:="kirst"
.Cells.Locked = True

pword = InputBox("Enter Your Password")

Select Case pword
Case Is = "gainv"
.Columns(1).Cells.Locked = False
.Columns(2).Cells.Locked = False
.Columns(3).Cells.Locked = False
.Columns(11).Cells.Locked = False

Case Is = "msauth"
.Columns(1).Cells.Locked = False
.Columns(2).Cells.Locked = False
.Columns(3).Cells.Locked = False
.Columns(12).Cells.Locked = False

End Select

.Protect Password:="kirst"
End with

End Sub

 

[Dave Peterson]

Ps. You could replace this:
.Columns(1).Cells.Locked = False
.Columns(2).Cells.Locked = False
.Columns(3).Cells.Locked = False
with
.Range("A:C").Cells.Locked = False

But sometimes it's nicer to see each of the ranges on a separate line in your
code. Then you can comment out the lines you don't want when (not if!) things
change.

 

[Kirstie Adam]

Hi,

I actually ended up with this....

Private Sub Workbook_Open()
Dim pword As String
With Sheets("Sheet1")
.Activate
.Unprotect Password:="admin"
Columns(11).Cells.Locked = True
Columns(12).Cells.Locked = True
End With

pword = InputBox("Enter Your Password")
Select Case pword

Case Is = "gainv": Columns(12).Cells.Locked = False
Case Is = "msauth": Columns(11).Cells.Locked = False

End Select
ActiveSheet.Protect Password:="admin"
End Sub


as i realised i only really needed those two columns locked and everything
else could be unlocked.....

thanks for the help though, have copied those solutions down for future use!

kirstie