Announcement

Collapse
No announcement yet.

Changing properties to command button at runtime

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

  • 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

  • #2
    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

    Comment


    • #3
      Re: Changing properties to command button at runtime

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

      Comment


      • #4


        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.

        Comment

        Working...
        X