Dynamic macro button label
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Dynamic macro button label

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there a way to make the label of a macro button dynamic so that it is labeled as the results of a specific cell. (i.e., If I type the name of a person into a cell, can I have a macro button labeled as the same name as the contents of that cell, even if I change the name?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assuming your button is called CommandButton1 and the cell you want the caption to appear in is A1 try this:-

    Right click the worksheet tab and choose View Code. Then try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    Me.CommandButton1.Caption = Target.Value
    End If
    End Sub

    HTH,
    D

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've tried that every possible and I can't get it to work. Is there anything more specific I could be looking at?

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    dk is right on. Make sure that you have the code in the Worksheet object, and not in a module. Follow his instructions to the "T".

    What does that mean, anyway, "To the T"? Anyone?

    -Russell

    [ This Message was edited by: Russell Hauf on 2002-02-19 16:21 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Follow his instructions to the "T".
    What does that mean, anyway, "To the T"? Anyone?
    `````````````
    old latin convertion for"letter" but cant remember the T word now... latin has all but dided out now bar doctors and legal peoples

    Rdgs
    Jack

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jack ! how about "formulae" we still use Latin although lots of people say "formulas"


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Am curious, can a macro be assigned to that button BUT the macro assigned depends on the name currently on the button? If possible, would the button name and macro have to be the same? Just curious.

    THANKS!

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-10 13:49, Cosmos75 wrote:
    Am curious, can a macro be assigned to that button BUT the macro assigned depends on the name currently on the button? If possible, would the button name and macro have to be the same? Just curious.

    THANKS!
    Hi,

    It can be done, but the trigger mechanism to change the caption would/should also trigger the procedure to run, like the following pseudo code.

    If range1 >10 then
    button.caption = "Forget this"
    Call "forget_this_macro"
    Else if ....

    would be the most frequent route. You could test the caption, as you wish to do:

    If button.caption = "Run Me" then
    "Run_Me"
    Else
    "Run_something_else"
    End if

    It just seems backward to me.

    HTH,
    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com