Hi Gord,
I am using Excel 2007 and tried your macro you had given Kirstie long back. I am having problems using it.
I get a VB error
Run-Time error '1004'
Application-defined or object-defined error
My macro reads
Private Sub Workbook_Open()
Dim pword As String
With Sheets("Sheet1")
..Activate
..Unprotect Password:="gehan1"
..Cells.Locked = True
End With
pword = InputBox("Enter Your Password")
Select Case pword
Case Is = "ccc1": Columns(C).Cells.Locked = False
Case Is = "ddd2": Columns(D).Cells.Locked = False
End Select
ActiveSheet.Protect Password:="gehan1"
End Sub
What am I doing wrong ?
Thanks
Gehan
Gord Dibben wrote:
KirstieWhen the workbook opens the sheet is unprotected, all cells are set to
15-Jun-08
Kirsti
When the workbook opens the sheet is unprotected, all cells are set to locke
and the inputbox asks for a password
Depending upon password entered, column A or B will be unlocked and the shee
re-protected
No error-checking done..............you will have to decide what to do if a
incorrect password is applied
Gor
Previous Posts In This Thread:
Password Protect 2 columns with different passwords
Hi all
I have a spreadsheet which generates purchase order numbers, and we want 1
column "Invoice No." to be password protected and 1 column "Authorised By"
to be password protected - but i want 2 different passwords for them as they
will be used by 2 different people
By using worksheet protection the best i have been able to do is lock both
columns, but by entering the password it unlocks all protected cells
Also, it would be great if the password request popped up in a message box,
rather than having to go view the menu bars
Can this be done? Is there a work-around if not
I know if it can be, someone on here'll know how......!
Kirstie
One password to protect/unprotect the sheet but a select case to choose
One password to protect/unprotect the sheet but a select case to choose whic
user gets his column unlocked
Private Sub Workbook_Open(
Dim pword As Strin
With Sheets("Sheet1"
.Activat
.Unprotect Password:="justme
.Cells.Locked = Tru
End Wit
pword = InputBox("Enter Your Password"
Select Case pwor
Case Is = "Gord": Columns(1).Cells.Locked = Fals
Case Is = "Pete": Columns(2).Cells.Locked = Fals
End Selec
ActiveSheet.Protect Password:="justme
End Su
Gord Dibben MS Excel MV
Hi,thanks for your answer, would you mind explaining it to me a bit more, what
Hi
thanks for your answer, would you mind explaining it to me a bit more, wha
the diff sections do
Kirsti
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
Kirstie, it is not possible to have two different passwords protect a sheet.
Kirstie, it is not possible to have two different passwords protect a sheet.
One is all that can be used
However, you create a macro that prompts for a password. The macro can then
decide from the password entered what to unprotect. The password it prompts
for is not the sheet password, just a dummy password used by the macro. The
macro unprotects the sheet, sets the appropriate cell locking and unlocking
based on the password entered, and reprotects the sheet
Nothing prevents the user from cracking sheet protection using a password
cracker. So, your security is mimimal
Robert Flanaga
Macro System
http://www.add-ins.co
Productivity add-ins and downloadable books on VB macros for Exce
What version of excel are you (and your users!) using?
What version of excel are you (and your users!) using?
IIRC, xl2002 added an option to:
tools|Options|Protect sheet
It allows you to specify different passwords for different ranges.
Look for Allow users to edit ranges under that dialog.
Kirstie Adam wrote:
--
Dave Peterson
KirstieWhen the workbook opens the sheet is unprotected, all cells are set to
Kirstie
When the workbook opens the sheet is unprotected, all cells are set to locked
and the inputbox asks for a password.
Depending upon password entered, column A or B will be unlocked and the sheet
re-protected.
No error-checking done..............you will have to decide what to do if an
incorrect password is applied.
Gord
Gord,I am being thick probably, but still can't get this to work!!
Gord,
I am being thick probably, but still can't get this to work!!
What am i doing wrong? Is it the column references?
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(K).Cells.Locked = False
Case Is = "msauth": Columns(L).Cells.Locked = False
End Select
ActiveSheet.Protect Password:="kirst"
End Sub
Kirstie
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
got it!!
got it!!
Good to hear.
Good to hear.
Gord
wrote:
EggHeadCafe - Software Developer Portal of Choice
..NET Number and Currency Formatting
http://www.eggheadcafe.com/tutorial...603-285e82c5e6ef/net-number-and-currency.aspx
