Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default Certain methods do not work

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default Certain methods do not work

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



.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 41
Default Certain methods do not work

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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Certain methods do not work

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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default Certain methods do not work

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



.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Available methods and properties through OLE interface Greg Luce Excel Discussion (Misc queries) 3 December 13th 07 08:40 PM
Budget Spread methods Doug Excel Worksheet Functions 0 October 17th 07 12:42 AM
Leveling Methods build Excel Worksheet Functions 3 October 13th 07 12:44 AM
How to set up easy lookup methods ET Excel Discussion (Misc queries) 2 October 13th 06 09:48 PM
Std.Dev.methods NOT WORKING SixSpeedShifter Charts and Charting in Excel 1 July 20th 05 06:32 PM


All times are GMT +1. The time now is 08:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"