Announcement

Collapse
No announcement yet.

Changing properties to command button at runtime

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • GaryD
    started a topic Changing properties to command button at runtime

    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:
        ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
            , DisplayAsIcon:=False, Left:=325, Top:=24, Width:=50, Height _
            :=20).Select

  • Dave Hawley
    replied
    Re: Changing properties to command button at runtime

    Hi Gary

    Try using code along these lines
    Code:
    Dim cBut As OLEObject
    On Error Resume Next
        With ActiveSheet
             .Shapes("My Button").Delete
         Set cBut = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
               , DisplayAsIcon:=False, Left:=325, Top:=24, Width:=50, Height:=20)
        End With
        
        With cBut
           .Object.Caption = "Hello"
           .Name = "My Button"
           .Object.Font = "Verdana"
           .Object.Font.Size = 8
        End With
    See: Using ActiveX Controls on Sheets in the VBA help for more details.
    Last edited by Dave Hawley; August 23rd, 2005, 15:29.

    Leave a comment:


  • GaryD
    replied
    Re: Changing properties to command button at runtime

    Inserted the code and received an "Object required" (Error 424) error message. Any other thoughts?

    Leave a comment:


  • Brister
    replied
    Re: Changing properties to command button at runtime

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

    Code:
    Private Sub Worksheet_Activate()
    Sheet1.CommandButton1.BackColor = &HFF&
    Sheet1.CommandButton1.Caption = "New Name"
    Sheet1.CommandButton1.Font.Bold = True
    Sheet1.CommandButton1.Font.Name = "Arial"
    
    End Sub
    This is assuming CommandButton1 is in Sheet1

    Leave a comment:

Working...
X