Changing properties to command button at runtime

  • The following code places a command button directly on a worksheet. It is working fine and executes the code I have associated with it okay. What I want to do is to change the properties of the button itself at "runtime". I want to change the button caption, background color, focus, font, etc. from the defaults. On thin ice :? Help!



    Code
    1. ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
    2. , DisplayAsIcon:=False, Left:=325, Top:=24, Width:=50, Height _
    3. :=20).Select
  • Re: Changing properties to command button at runtime


    Well if it is just changing the properties, you can do this:


    Code
    1. Private Sub Worksheet_Activate()
    2. Sheet1.CommandButton1.BackColor = &HFF&
    3. Sheet1.CommandButton1.Caption = "New Name"
    4. Sheet1.CommandButton1.Font.Bold = True
    5. Sheet1.CommandButton1.Font.Name = "Arial"
    6. End Sub


    This is assuming CommandButton1 is in Sheet1

  • Re: Changing properties to command button at runtime


    Hi Gary


    Try using code along these lines

    See: Using ActiveX Controls on Sheets in the VBA help for more details.