Use VBA to add new button AND its code

[sspicer]

I have a spreadsheet which walks the user through how to add data to it in the correct manner.

As such, I want to be able to write a macro that will add a new page AND on that page there to be a button with code behind it (only simple code to take the user back to the main menu).

So far I have:

Sub NewPage()
 
Sheets.Add
ActiveSheet.Name = ComboBox1.Value
 
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=300, Top:=25, Width:=150, Height:=40) _
        .Select

This all works, but I'll need something to say that on the "click" method of the new button then sheets("HomePage").activate. That's all I'll need. Any ideas? Or is there an easier way to do this?

Thanks in advance!

Sam

 

[Jaafar Tribak]

Place this in your worksheet module :

Private WithEvents cmbEvents  As CommandButton
Private oNewSheet As Worksheet
 
Private Sub cmbEvents_Click()
 
    Sheets("HomePage").Activate
 
End Sub
 
Private Sub CommandButton1_Click()
 
    On Error GoTo err_Handler
    Set oNewSheet = Sheets.Add
    
    With oNewSheet
        .Name = (ComboBox1.Value)
       Set cmbEvents = _
       .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
       , DisplayAsIcon:=False, Left:=300, _
       Top:=25, Width:=150, Height:=40).Object
    End With
    Set oNewSheet = Nothing
    Exit Sub
err_Handler:
    
    MsgBox Err.Description
 
End Sub

Regards.