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

Try changing

DoCmd.RunMacro stMacroName

to

Application.Run stMacroName

HTH,
Bernie
MS Excel MVP


"sparkes84" <(E-Mail Removed)> wrote in message
news:2611D0CD-D6A0-4DEC-903C-(E-Mail Removed)...
> 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

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.




sparkes84 wrote:
>
> 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

--

Dave Peterson

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


Good luck,
Ryan---


"Dave Peterson" wrote:

> 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.
>
>
>
>
> sparkes84 wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>