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 wrote:

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.

-----Original Message-----
Rohit

put the code in a regular module and put

Option Private Module

as the first line. The subroutines will then not be
listed in the macro
dialogue box.

In the code for your button, you might need to qualify
the subroutine, for
example:

Module1.Test


Regards

Trevor


"Rohit Thomas" wrote in message
...
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


.


--

Dave Peterson