Problem asttaching macro to form button

[dianeha]

Good morning to all!

I am trying to attach a macro I created to a form button in excel 2003. I
click the button however the macro will not run. I thought it might hav
ebeen the security level but I changed that. If anyone can assist me it
would be appreciated. I am new to VBA so please be kind.

Thanks!
Hamm


Sub Clean_for_New_Month()
'
' Clean_for_New_Month Macro
' Macro recorded 12/3/2007 by Diane Hamm
'
' Keyboard Shortcut: Ctrl+q
'
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("ATL").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I41").Select
Selection.Interior.ColorIndex = xlNone
Sheets("BAC").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("BLV").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I41").Select
Selection.Interior.ColorIndex = xlNone
Sheets("CAC").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("CCR").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("CHE").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("CLV").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I41").Select
Selection.Interior.ColorIndex = xlNone
Sheets("COU").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("FLV").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I45").Select
Selection.Interior.ColorIndex = xlNone
Sheets("FTN").Select
Range("H8:I46").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("GBI").Select
Range("H8:I46").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("GTU").Select
Range("H8:I46").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("HBR").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("HLT").Select
ActiveWindow.SmallScroll Down:=-6
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("JOL").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("LAD").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("LAS").Select
ActiveWindow.SmallScroll Down:=-9
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I41").Select
Selection.Interior.ColorIndex = xlNone
Sheets("LAU").Select
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("MET").Select
ActiveWindow.SmallScroll Down:=-9
Range("H8:I41").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Range("H25").Select
ActiveWindow.SmallScroll Down:=18
ActiveWindow.ScrollWorkbookTabs Sheets:=16
Sheets(Array("NKC", "NOR", "REN", "RIN", "RLV", "SAC", "STL", "STU",
"TAH", "UBC", "UEL", _
"UHA", "UTU", "WCL")).Select
Sheets("WCL").Activate
ActiveWorkbook.Sheets("WCL").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("UTU").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("UHA").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("UEL").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("UBC").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("TAH").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("STU").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("STL").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("SAC").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("RLV").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("RIN").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("REN").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("NOR").Tab.ColorIndex = -4142
ActiveWorkbook.Sheets("NKC").Tab.ColorIndex = -4142
Sheets(Array("NKC", "NOR", "REN", "RIN", "RLV", "SAC", "STL", "STU",
"TAH", "UBC", "UEL", _
"UHA", "UTU", "WCL")).Select
Sheets("NKC").Activate
ActiveWindow.SmallScroll Down:=-9
Range("H8:I44").Select
Selection.ClearContents
Range("G8:I46").Select
Selection.Interior.ColorIndex = xlNone
Sheets("X").Select
Range("H67").Select
ActiveWindow.SmallScroll Down:=-54
Sheets("WCL").Select
Range("K45").Select

End Sub

 

[JLGWhiz]

Hi Dianeha, I have been through that routine myself. Right click the button
and see if the dialogue box appears with a list of macros for that workbook.
If it does, see if the macro you thought you had assigned is in the narrow
window at the top of the dialogue box. If not, select the correct macro,
make sure it appears in the narrow window after you click it, then click OK.

If the dialogue box doe not appear when you right click your button, then
the button was not selected from the Forms tool bar, but was probably from
the Control Toolbox Toolbar. In this case, you will need to click
View>Toolbars>ControlToolbox
and then click on the design mode icon in the upper left corner. This will
allow you to access the button properties by double clicking the button. You
should see the code module appear with Private Sub CommandButton1_Click()
already entered.

Put your macro name between the two lines of code like so:

Private Sub CommandButton1_Click()
macroName
End Sub

Good Luck.

 

[Joel]

The code doesn't produce any errors if you havve all the worksheet. It must
be something failing when the code is called.


1) Put break point on Sub line to make usre code is or is not getting
called. Click SUB line and then press F9 to set break point. Run code and
see if it stops at first line. then step through code using F8 to find where
code is failing

2) If you don't get to the break point.
a) Check if you are in Design Mode. Right click button. If you don't
see anything you are not in design mode which is good. If you do see
something you need to exit the design mode.
b) from worksheet menu: View - Toolbars - comand and control. Press the
Triangle. then try button again

3) Check to make sure code is located in the correct place in VBA. Enter
Design mode by doing step 2B above. Pressing Triangle toggles in and out of
Design Mode. When in design mode double click button. the VBA code should
appear. If not place your code into the macro templet that appears. then
exit design mode and try button again.

 

[dianeha]

Thanks guys for helping! I used both sugestions and edited the code a bit
and it works great!