Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run-time adjustment of commandbutton (WinXP/Office2003)

    Hi,

    A simple question (hopefully)

    Would anyone know how to modify a normal commandbutton placed on a worksheet at the "Workbook_open" event.

    I would like my button to reflect the name from a specific cell and that name may change from time to time.
    So once the wbk opens I would like it to do something like this... myButton.caption=range("XY").value

    Any hints...?

    Thx.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time adjustment of commandbutton (WinXP/Office2003)

    Is this one from the Control toolbox or from the forms toolbar?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time adjustment of commandbutton (WinXP/Office2003)

    It's from the Visual Basic toolbar and claims to be the "Control toolbox"

    ;o)
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time adjustment of commandbutton (WinXP/Office2003)

    In that case, you can use something like this:

    Private Sub Workbook_Open()
    Worksheets("Sheet1").MyButton.Caption = Worksheets("Sheet2").Range("C3")
    End Sub

    Substitute the appropriate names. You can see the name of the command button by activating design mode, clicking on the button, then clicking the Properties button on the Control Toolbox.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time adjustment of commandbutton (WinXP/Office2003)

    Thought that was what I tried to do, but I keep getting error: Compile error - Method or data member not found.

    I've copied the buttons object name from the click event to exclude potential typos - but I keep getting this error and can't figure out what causes this error...??

    Below is my code...

    Private Sub Workbook_Open()
    Dim wshTestSheet As Worksheet
    Dim wshTest As Worksheet

    Set wshTestSheet = Worksheets("My_Compliance")
    Set wshTest = Worksheets("My_test")

    wshTestSheet.cmdCompany1_Compliance.Caption = "Refresh info for" & Chr(13) & " - " & wshTest.Range("Company_Name1").Value

    End Sub
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time adjustment of commandbutton (WinXP/Office2003)

    Not sure if it works, but it might be necesary to place the code in a normal module.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time adjustment of commandbutton (WinXP/Office2003)

    OK, try the code in the attached workbook. The Workbook_Open event procedure calls a procedure in the worksheet module for My_Compliance. (Later: replaced attachment)

Posting Permissions

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