Command button to run a macro

[sparkes84]

Hi, really stuck. Thought I had written the code correctly but when I click
on my command button to run my macro, it comes up with 'Object required'.
When I click on Tools and then Macros, my macro on there is called
Sheet1.RemoveThem

My code looks like this - where have I gone wrong?
Private Sub CommandButton1_Click()
On Error GoTo Err_Command1_Click

Dim stMacroName As String

stMacroName = "Sheet1.RemoveThem"
DoCmd.RunMacro stMacroName

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Thanks for any help

 

[Bernie Deitrick]

Try changing

DoCmd.RunMacro stMacroName

to

Application.Run stMacroName

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Is this code in the same worksheet module that owns the commandbutton (Sheet1)?

If yes, you could just call the macro directly:

Option Explicit
Private Sub CommandButton1_Click()
Call RemoveThem
End Sub
Sub RemoveThem()
MsgBox "hi"
End Sub

And if the removeme procedure is in a different worksheet module than the
commandbutton_click:


Behind the sheet with the commandbutton:
Option Explicit
Private Sub CommandButton1_Click()
Call Sheet1.RemoveThem
End Sub

Behind Sheet1:
Option Explicit
Sub RemoveThem()
MsgBox "hi"
End Sub

Application.run doesn't make a lot of sense here -- unless you're building the
name of the procedure or the procedure is hidden/private.

 

[ryguy7272]

Couple good tutorials here:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm
http://www.mrexcel.com/tip068.shtml


Good luck,
Ryan---