Execute macro from a Commandbutton

[jadadad]

I'm at my end of my rope. I just can't seem to find the correct code to assign a caption, name, and a macro to a commandbutton. Need your assistance. See code below. The first line puts the button on the active sheet just fine. But, it's the next line (With statement) that's eating my lunch. The error I get is "Object required" starting at the 'With CommandButton1' line

Thanks a priori,

++++++++++++++++++++++++++++
Sub addbuttons()
'
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=599.25, Top:=26.25, Width:=48, Height:= _
24.75).Select
'
With CommandButton1
.Caption = "GO Home"
.OnAction = "gohome"
.Name = "Home"
End With
End Sub

 

[SkipVought]

Hi,

You were missing an End With since you have 2 With statements.

I added an object which removes the specificity of CommandButtonn

Sub addbuttons()
'
   With ActiveSheet
      Set cb = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
      , DisplayAsIcon:=False, Left:=599.25, Top:=26.25, Width:=48, Height:= _
      24.75)
      '
      With cb
         .Caption = "GO Home"
         .OnAction = "gohome"
         .Name = "Home"
      End With
   End With
   Set cb = Nothing
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884