Macro Button

[Guest]

I have written a macro to change the currency format of certain cells on a
number of different sheets in one workbook. It works when i run it through
tools/macros/ run, but goes into error when i try and run it from a click
button, any ideas

 

[papou]

Hello Gary
If you are running code from a commandbutton from the Controls tool box then
consider setting the TakeFocusOnClick property to false.

HTH
Cordially
Pascal

 

[Guest]

I still get "select method of range class failed" and highlights the following

Range("E:E,G:G,H:H,I:I,J:J,F24:F26").Select

 

[papou]

Gary
Your code works fine on my Excel version (2003 SP3).
There must be some other reason like protected cells or merged cells ?
Or ?

HTH
Cordially
Pascal

 

[Dave Peterson]

An unqualifed range will refer to the activesheet if the code is in a general
module.

An unqualified range will refer to the worksheet that owns the code if it's in a
worksheet module.

So if you do:
worksheets("sheet99").select
range("E:E,G:G,H:H,I:I,J:J,F24:F26").select

This will work in a general module. That range will be selected.

But when the code is behind a worksheet, the activesheet is sheet99. But you're
trying to select a range on the sheet that owns the code.

Since you can only select a range on the active sheet, your code bombs.

You could use:

worksheets("sheet99").select
worksheets("sheet99").range("E:E,G:G,H:H,I:I,J:J,F24:F26").select

or
with worksheets("sheet99")
.select
.range("E:E,G:G,H:H,I:I,J:J,F24:F26").select
End with

But better is to drop the select's and just do the work on the range you want:

worksheets("sheet99").range("E:E,G:G,H:H,I:I,J:J,F24:F26").numberformat = "0"