|  | Columns & Passwords | | |
| | | Kirstie Adam |  |
|  Posted: Wed Jun 18, 2008 12:26 pm Post subject: Columns & Passwords |  |
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 | |
| | Posted: Wed Jun 18, 2008 5:15 pm Post subject: Re: Columns & Passwords | |
| |  | |
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
Kirstie Adam wrote:
| Quote: |
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 |
| |
| | | Dave Peterson | |
| | Posted: Wed Jun 18, 2008 5:37 pm Post subject: Re: Columns & Passwords | |
| | | |
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.
Dave Peterson wrote:
| Quote: |
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
Kirstie Adam wrote:
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
|
--
Dave Peterson |
| |
| | | Kirstie Adam | |
| | Posted: Thu Jun 19, 2008 7:43 am Post subject: Re: Columns & Passwords | |
| | | |
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
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:485963E5.79746FA0@verizonXSPAM.net...
| Quote: | 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.
Dave Peterson wrote:
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
Kirstie Adam wrote:
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
--
Dave Peterson |
|
| |
|
|
