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

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


.

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.


"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

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

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

-----Original Message-----
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.


"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


.