unprotecting a worksheet

[mchugh]

hi

i'm currently working on a simple module for a spreadsheet that my father uses at work. the problem i am having is related to protection. certain columns have to be protected, so that people that use the spread sheet cant just input any old values. setting this up is fine. the problem is, that i need to edit the value of the cells in VBA. now i have tried to unrpotect the sheet in code, but it simply wont work. i'm trying to do something like this:

WorkSheets("Price Match&quot.Activate
ActiveSheet.Unprotect("password&quot

i've tried this lots of different ways round, but everyway i've tried it, i get a run time error - 1004 "unprotect method failed" or something similar

does anyone have any ideas? only its driving me crazy!

thanks in advance
Craig

 

[xlbo]

2 Options
1: In workbook open event use
Sheets("Sheetname&quot.protect userinterfaceonly:=true

This will mean that you can make changes via code but users cannot make changes manually

2: Sheets("Sheetname&quot.unprotect password:= "password"
should work but may fail if your sheet is hidden

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

hi

thanks for the reply

i tried it out but i get the same kind of result :s

i'm using excel 97 if that makes any difference?

 

[xlbo]

shouldn't make any difference whatsoever
Can you post all the code you are using and let us know where it is placed (ie in a standard module / sheet level event / module etc)

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

ok, its a nasty big function - i could break it down, but its only meant to be a quick fix. this is just in a module
Sub findDetails()

'define variables
Dim currentPart As String
Dim testString As String
Dim price As String
Dim found As Boolean
Dim dateTestString As String

'stops the screen from flickering
Application.ScreenUpdating = False

'select the main sheet
Worksheets("Price Match&quot.Select
'Worksheets("Price Match&quot.Activate

'********** THIS IS WHERE IT BREAKS **************

Sheets("Price Match&quot.Unprotect password:="password"

'*************************************************

'move to the first cell
Range("F3&quot.Select
dateTestString = ActiveCell.Text

'get to new records
While (dateTestString <> &quot;&quot
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
dateTestString = ActiveCell.Text
Wend

ActiveCell.Offset(rowOffset:=0, columnOffset:=-3).Activate

'get first value
Sheets(&quot;Price Match&quot.Select
currentPart = ActiveCell.Text

'LOOP STARTS HERE -----------------------------
Do While (currentPart <> &quot;&quot


Sheets(&quot;Prices&quot.Select
Range(&quot;A2&quot.Select

'put a dummy values in the test string
testString = &quot;z&quot;

Do While (testString <> &quot;&quot

testString = ActiveCell.Text

'MsgBox (&quot;Current Part No: &quot; & currentPart & &quot; Current Test No: &quot; & testString)

If (testString = &quot;&quot Then
MsgBox (&quot;WARNING! Part No. &quot; & currentPart & &quot; is either not on record, or is invalid. Please ensure you have entered the part number correctly. If the problem persists, please enter the price manually&quot
found = False
Else
If (StrComp(currentPart, testString) = 0) Then
ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Activate
price = ActiveCell.Value
found = True
testString = &quot;&quot;
End If
End If

'move down to the next one
If (testString <> &quot;&quot Then ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

Loop

'when we get to here, we have obtained the price
'so lets take it back to the other spread sheet, and put it in
Worksheets(&quot;Price Match&quot.Activate
ActiveCell.Offset(rowOffset:=0, columnOffset:=3).Activate

'Deal with the date
ActiveCell.FormulaR1C1 = &quot;=TODAY()&quot;
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'end of date stuff
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate

If (found = True) Then
ActiveCell.Value = price
Else
ActiveCell.Value = 0
End If

ActiveCell.Offset(rowOffset:=1, columnOffset:=-4).Activate
currentPart = ActiveCell.Text
Loop
'LOOPS ENDS HERE ------------------------------

MsgBox (&quot;Done! Please manually add prices for any part numbers that were not found on the system.&quot

EndOfFunction:

Application.ScreenUpdating = True

End Sub

 

[xlbo]

okaaaay - not sure what's going on here 'cos as long as you have a sheet called &quot;price match&quot;, the code works fine. Only thing I can think of is that the password is not &quot;password&quot; ie you are not passing the correct password in the code

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

well, password isnt the password, but i know what the password is, and its definately correct in the code.

maybe it could be some setting in excel? only the spreadsheet was sent to me with some existing validation etc. and i'm having to work around stuff? maybe theres some other projection/options i dont know about?

 

[xlbo]

nope - the line:
Sheets(&quot;Price Match&quot.Unprotect password:=&quot;password&quot;
is the correct syntax to unprotect a sheet. Are you sure that there isn't an extra space or something in the password or the sheet name ??

Only other thing I can think that would stop it working is if the workbook is &quot;Shared&quot;....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

nope, its not shared, i just looked

doh, this is annoying :s

the password i am using is definately correct, because i can unlock the spreadsheet with it in excel, and its the same password i use for everything on that machine (my dads home computer).

i cant think for the life of me what is stopping it from working. i'll keep at it, and let you know if i figure it out.

 

[xlbo]

Sorry I can't be more help without seeing the file
I have created a new workbook with a sheet called price match and protected it. Whatever I do, the line:
Sheets(&quot;Price Match&quot.Unprotect password:=&quot;password&quot;
works
what is the EXACT error message you get ?? is it the same every time ??

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

the exact error is...

Run-time error '1004':

Unprotect method of Worksheet class failed

continue (greyed out), end, debug, help

 

[ssVBAdev]

Here's some thoughts... not sure if it'll help or not:

1) There shouldn't be a problem with the name of the sheet (ie extra spaces etc...). If so, the line:
Worksheets(&quot;Price Match&quot.Select would fail prior to the call to unprotect the sheet. Therefore, as long as each reference to Worksheets(&quot;Price Match&quot is the same in code then that should be okay.

2) If it is a password problem, the error you should be getting is a #1004 and it'll say something to the effect of &quot;The password you supplied is not correct...&quot; If you are getting another error, I would look at something else as being the problem.

3) I know Excel's Password protection is a bit screwy. I've protected some sheets through code but have been unable to type the same password into the Tools-Protection-Unprotect Worksheet dialog box and be able to actaully unprotect the sheet. Just to be safe, I would make sure that the password you are using is alpha-numeric and that you only use the password either through code or through the Excel menu but not both.

 

[mchugh]

&quot; Just to be safe, I would make sure that the password you are using is alpha-numeric and that you only use the password either through code or through the Excel menu but not both.&quot;

that sounds like a good idea, i'll give that a go

 

[mchugh]

well i tried activating the protection in vba, instead of from the menu, and that worked fine - i still get the same error with the unprotect line though :s

 

[ssVBAdev]

What is the exact error you are getting?

 

[xlbo]

&quot;only use the password either through code or through the Excel menu but not both.&quot;

Gotta say I've never had a problem switching between the 2.....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[ssVBAdev]

xlbo:
I believe it is a similar bug (If you'll alow me to call it that) that enables much of the &quot;Breaker&quot; Subs to Crack a protected spreadsheet. When you crack a worksheet and print the resulting password to the Immediate Window, the password will be very different from the one that the worksheet was locked with. But, at one point, I was working on a spreadsheet and had to unprotect it and could not through the Excel Menu. I had to use the code.

 

[mchugh]

its really frustrating, because it should be a 5 minute job, but its taken me 3 days now, working on one line, which still doesnt work :'(

ssVBAdev - the exact error was posted above

 

[xlbo]

If this is a real problem, you could try what I suggested first. In the WORKBOOK_OPEN procedure, use
Sheets(&quot;Price Match&quot.protect userinterfaceonly:=true
This will mean that for a user, the worksheet is protected but you can change it via code WITHOUT first unprotecting...
This needs to be in the wb_open procedure as it is reset on closing the workbook

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[mchugh]

i fixed it!

its a bit odd though - all i did was move the two activate/select and unprotect lines into another function, and it worked first time! :S

i'm not too bothered why to be honest, just glad it works now.

thanks for all your suggestions