Help with assigning a macro to a commandbutton

D

Dave Potter

I need to assign a macro to a commandbutton. I've recorded the macro
and tried the code below but I get a "Expected End Sub" error

Private Sub CommandButton1_Click()
Sub Openproductmix351()
'
' Openproductmix351 Macro
' Macro recorded 8/30/2003
'

'
Sheets("PRODUCT MIX 351").Visible = True
Sheets("351").Visible = True
End Sub
End Sub


What did I do wrong?
TIA,
David
 
A

Alan

I think it should be:-

Sub Openproductmix351()
'Openproductmix351 Macro
' Macro recorded 8/30/2003
Sheets("PRODUCT MIX 351").Visible = True
Sheets("351").Visible = True
End Sub

ie delete

Private Sub CommandButton1_Click()
and the second End Sub

Regards,
Alan.
 
T

Tom Ogilvy

If you want the commandbutton to run the macro, right click on the tab of
the sheet module of the sheet containing the commandbutton and paste in your
code:

Private Sub CommandButton1_Click()
Sub Openproductmix351()
'
' Openproductmix351 Macro
' Macro recorded 8/30/2003
'

'
Sheets("PRODUCT MIX 351").Visible = True
Sheets("351").Visible = True
End Sub
End Sub

The remove the inner declarations - you can't have embedded subs.

Private Sub CommandButton1_Click()
'
' Openproductmix351 Macro
' Macro recorded 8/30/2003
'
Sheets("PRODUCT MIX 351").Visible = True
Sheets("351").Visible = True
End Sub

Or you could move your recorded macro back to a general module and just call
it from the click event

In the sheet module

Private Sub CommandButton1_Click()

OpenProductMix351

End Sub

in a general module


Sub Openproductmix351()
'
' Openproductmix351 Macro
' Macro recorded 8/30/2003
'

'
Sheets("PRODUCT MIX 351").Visible = True
Sheets("351").Visible = True
End Sub
 
Top