Command Button Not Responding

sjs87

New Member
Joined
Jul 13, 2007
Messages
11
I've just written a few lines of VBA code that apply to a command button to clean up a template that I use, i.e. deleting column contents, moving info around, etc. When I press F8 and go through the VBA editor line by line, the code does what I want it to do. However, when I go to click the command back in the excel spreadsheet, it does nothing at all. The code I've written is as follows and any help would be much appreciated!

Private Sub CommandButton_Click5()

'ASK IF THEY WANT TO USE THE BUTTON
response = MsgBox("Prepare inventory template ready for consolidation?", vbYesNo, "Confirm")
lineNo = 1

If (response = VbMsgBoxResult.vbYes) Then

'OPEN UP THE TEMPLATE PRE-CLEANING
Workbooks.Open fileName:="c:\KPMG\MoW KPMG Report Consolidated Report TEMPLATE.xlt"


'GO TO THE SUMMARY SHEET 'CLEAR J:K
Worksheets("Baseline Inventory - Summary").Activate
Worksheets("Baseline Inventory - Summary").Range("J2:K1000").ClearContents

'PASTE M:N IN TO J:K
Worksheets("Baseline Inventory - Summary").Range("M2:N1000").Copy _
Destination:=Worksheets("Baseline Inventory - Summary").Range("J2")

'CLEAR M:N
Worksheets("Baseline Inventory - Summary").Range("M2:N1000").ClearContents

'FILTER BY NOT EQUAL TO COMPLETE IN COLUMN K
Selection.AutoFilter Field:=11, Criteria1:="<>COMPLETE", Operator:=xlAnd

'CLEAR COLUMNS F:H AND P:R
Worksheets("Baseline Inventory - Summary").Range("F2:H1000").ClearContents
Worksheets("Baseline Inventory - Summary").Range("P2:R1000").ClearContents

'REMOVE FILTER
Worksheets("Baseline Inventory - Summary").AutoFilterMode = False

'GO TO BASELINE INVENTORY FULL
Worksheets("Baseline Inventory - Full").Activate

'FILTER BY NOT EQUAL TO COMPLETE IN COLUMN K
Selection.AutoFilter Field:=11, Criteria1:="<>COMPLETE", Operator:=xlAnd

'CLEAR COLUMNS F:I AND K:L
Worksheets("Baseline Inventory - Full").Range("F2:I1000").ClearContents
Worksheets("Baseline Inventory - Full").Range("K2:L1000").ClearContents

'REMOVE DATA FILTER
Worksheets("Baseline Inventory - Full").AutoFilterMode = False

'SAVE AS {MOW KPMG REPORT TEMPLATE.XLT} IN C:/KPMG
ActiveWorkbook.Save

End If
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
What type of button do you use?

1) ActiveX, from [Control Toolbox] OR
2) Button from [Forms toolbar]

If 1 then is this code in the Worksheets code module?
If 2 then is this code in a Std Module?
 
Upvote 0
I've already exited out of design mode and there are a couple of other buttons that a colleague of mine wrote that work fine. As far as Active X versus Form goes, I'm not too sure what you mean... I'm a bit of a novice. All I've done is create a button in an Excel spreadsheet then gone to the corresponding worksheet page in VBA and writen code to apply to that button. Any ideas where I've gone wrong?

Thanks.
 
Upvote 0
If your button is from the Control Toolbox and named CommandButton5 your procedure should be:

Private Sub CommandButton5_Click()

note:

Private Sub CommandButton_Click5()
 
Upvote 0
Thanks very much, Andrew! Can't believe it was a tiny error like that. Great spot. Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top