Changing Button Caption using VBA & Assigning Macro to button using VBA

[jamesblack90]

Hi All,

This post has two questions, so thanks in advance!

I am creating a new sheet in this instance, and placing a button to go back to the Home Page.

Here is the code:

Private Sub create_home()
Dim btnHome As OLEObject
Dim code As String

' create home button
Set btnHome = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=417.75, Top:=3.75, Width:=72#, Height _
    :=31.5)

btnHome.Name = "btnGoHome"
'btnHome.Caption = "Home"

' create code to insert into new sheet
code = "sub btnGoHom_Click()" & vbCrLf
code = code & "goHome" & vbCrLf
code = code & "end sub"

'insert the code
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
    .insertlines .CountOfLines + 1, code
End With

End Sub

Question 1: I am trying to change the caption of the button to say "Home". It is giving me Object Not Supported error.
I have tried .Caption and .Selection.Text I've also tried diming the btnHome as both OLEObject and Object. No luck.

Second of all, I need to assign macro to button click. I am facing two issues, 1) My security won't allow VB to access VB and 2) If I disable this and allow it to, then it gives me a runtime error, so I tried just having a sub btnGoHome_Click() in a module instead of the worksheet, but it still doesn't work. Finally, given this is to be used on company computers, I doubt the users are going to know how to change their security settings each time they run this app.

Any suggestions?

Thanks again

 

[wellsr]

Might I suggest making a form control button instead of an OLEObject button? If your macro is always going to be the same, you can have a generic macro already coded up in your VBA and just assign it to the new button.

Sub create_home()
Dim btnHome As Object
Set btnHome = ActiveSheet.Buttons.Add(417.75, 3.75, 72, 31.5)
btnHome.Name = "btnGoHome"
btnHome.OnAction = "GenericAction"
btnHome.Characters.Text = "Home"
End Sub

Sub GenericAction()
goHome
End Sub

If you decide to stick with the OLEObject, which I don't recommend, you would change the name to "Home" with the following line:

ActiveSheet.OLEObjects(btnHome.Name).Object.Caption = "Home"

I hope this helps!

 

[jamesblack90]

Thanks wellsr!

Worked like a charm