Certain methods do not work

[Rohit Thomas]

Hello All,

I'm stuck on a problem where I run into run-time errors
with certain methods, in my particular case the Unprotect
Method. The code is executed from either a button on a
sheet or a button on a userform. In either case, I get run-
time error '1004'. The only way I get the same code to
work is if it is written on a regular module. However, I
am trying to avoid writing code to a regular module
because I do not want the user to execute it from Tools

Macros. I've been working on finding a solution for days

and have not had any luck. If anyone can point me in the
right direction, I would be greatful.

Thanks,
Rohit

 

[Rohit Thomas]

Trevor,
I tried what you suggested and I am still getting the same
error. Here's my code.

Module1:
Option Private Module
Sub UnprotectActiveSheet()
ActiveSheet.Unprotect Password:="ppsproofpay"
End Sub

UserForm1:

Private Sub CommandButton2_Click()
*****some code here****
Sheets("Payroll Template").Visible = True
Sheets("Payroll Template").Select
Module1.UnprotectActiveSheet
Range("A4:I201").Select
Selection.Locked = False
****some code here****
End Sub

What's puzzling to me is that this code will work fine
using XL97 on a Win2K pc but fails using XL97 on a Win98
or WinNT4.0 pc.

 

[Tim Zych]

Another thing to try is ActiveCell.Activate just before the problem code,
and/or setting the TakeFocusOnClick property of the worksheet command button
to FALSE.

 

[Dave Peterson]

This portion can cause trouble:

Private Sub CommandButton2_Click()
*****some code here****
Sheets("Payroll Template").Visible = True
Sheets("Payroll Template").Select
Module1.UnprotectActiveSheet
Range("A4:I201").Select
Selection.Locked = False
****some code here****
End Sub

When you have an unqualified range (like range("a4:i201")), it'll refer to the
activesheet when the code is in general module. But if the code is behind a
worksheet, it refers to the sheet that owns the code.

So if commandbutton2 were on sheet17, then you're really writing:

Private Sub CommandButton2_Click()
*****some code here****
Sheets("Payroll Template").Visible = True
Sheets("Payroll Template").Select
Module1.UnprotectActiveSheet
'here's the change!
worksheets("sheet17").Range("A4:I201").Select

End Sub

And you can't select a range on a sheet that isn't selected.

You could do this:

Private Sub CommandButton2_Click()
with sheets("Payroll Template")
.Visible = True
Module1.UnprotectActiveSheet
.Range("A4:I201").Locked = False
end with
End Sub

but even better is to drop the selection:

Option Private Module
Sub UnprotectSheet(str as string)
worksheets(str).Unprotect Password:="ppsproofpay"
End Sub

Private Sub CommandButton2_Click()
*****some code here****
with sheets("payroll template")
.Visible = True
call Module1.UnprotectSheet(str=.name)
.Range("A4:I201").Locked = False
end with
****some code here****
End Sub

 

[Rohit Thomas]

To All,

Thanks very much for your response. Tim's suggestion of
setting the TakeFocusOnClick to False did the trick. I was
still getting the same error with the other suggestions
but thank you very much for posting one. I've been
struggling with this for weeks and I thought that I would
have to go back to my manager with bad news. The help that
I receive from the folks in this newsgroup is tremendous.

Thanks again,
Rohit Thomas