macro runs, but not when assigned to a button

[CuriousGeorge2]

I have written a macro, which runs fine when I either go to tools>>>macros and run it from there, or if I assign it to a toolbar button.

But if I put a button on the spreadsheet itself and assign this code to the button, it errors out on the command

Range("A4").Select

What's wrong with that command? It works fine if I run the macro, but not when I click the button it's assigned to?


Range("A3:J17").Select
Selection.Copy
Sheets("INSTALL").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.Columns.AutoFit
Range("A3").Select
Sheets("SUMMARY").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("INSTALL").Select
Application.CutCopyMode = False
ChDir "N:\DISCONNECTS\PENDING\9568\Archive"
ActiveWorkbook.SaveAs Filename:= _
"N:\DISCONNECTS\PENDING\9568\Archive\MOC_INSTALLS.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

 

[PHV]

And this ?
Sheets("SUMMARY").Range("A3:J17").Copy

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CuriousGeorge2]

I'm not sure I understand. Is that line of code to replace something? I'm not getting an error on the "copy" part of them macro. Why is it causing an error on a simple "Range.Select" statement?

 

[fumei]

What kind of a button? A Forms button, or a Control button?

Gerry
See my Paintings and Sculpture

 

[CuriousGeorge2]

Control button.

 

[KenWright]

Use the Forms Toolbar and take the button from there instead. When you create it it will ask you to assign a macro to it.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[CuriousGeorge2]

Thanks, I'll try that. But what's bothering more than anything else is not so much how to get around this problem, but what is causing it to begin with? I've copied the macro text to a button many times with no problem, and I don't understand why it errors ONLY when I try to execute it with a button. Any idea why this is happening?

 

[PHV]

why this is happening
Because the button has still the focus when the code is running, I guess.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

Because the "TakeFocusOnClick" property of the button is set to true by default. This takes the focus AWAY from the sheet and you therefore cannot select a range on it.

You need to change this to false

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[fumei]

Could it be that, because it is a Control button, the code is in the wrong module, or is out of scope somehow?

Gerry
See my Paintings and Sculpture