Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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!


    VB:
    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:

    VB:
    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,698

    Re: Changing properties to command button at runtime

    Hi Gary

    Try using code along these lines
    VB:
    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 15: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 25th, 2007, 23:12
  2. Adding Code To A Command Button At Runtime
    By Philip_Clarke in forum EXCEL HELP
    Replies: 6
    Last Post: October 1st, 2006, 15:51
  3. Execute Command Button Created at Runtime
    By adaniele in forum EXCEL HELP
    Replies: 6
    Last Post: September 28th, 2006, 11:42
  4. Command Button Properties
    By txaggie in forum EXCEL HELP
    Replies: 6
    Last Post: September 20th, 2005, 00:05
  5. Command Button Properties
    By BigPoppa in forum EXCEL HELP
    Replies: 3
    Last Post: July 23rd, 2004, 06: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