Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Changing properties to command button at runtime

  1. #1
    Join Date
    21st August 2005
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th December 2003
    Posts
    129

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    21st August 2005
    Posts
    6

    Re: Changing properties to command button at runtime

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

    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 at 16:29.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: April 26th, 2007, 00:12
  2. Adding Code To A Command Button At Runtime
    By Philip_Clarke in forum EXCEL HELP
    Replies: 6
    Last Post: October 1st, 2006, 16:51
  3. Execute Command Button Created at Runtime
    By adaniele in forum EXCEL HELP
    Replies: 6
    Last Post: September 28th, 2006, 12:42
  4. Command Button Properties
    By txaggie in forum EXCEL HELP
    Replies: 6
    Last Post: September 20th, 2005, 01:05
  5. Command Button Properties
    By BigPoppa in forum EXCEL HELP
    Replies: 3
    Last Post: July 23rd, 2004, 07:40

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno