+ Reply to Thread
Results 1 to 9 of 9

Dynamic Button Name?

  1. #1
    Mike
    Guest

    Dynamic Button Name?

    Hi everone,

    Can a button name be made dynamic?

    For example, you create a button and want its name be read from cell
    A1. So if A1 read "Mike", the button name automatically become "Mike"
    and so on.

    Thanks,
    Mike


  2. #2
    JE McGimpsey
    Guest

    Re: Dynamic Button Name?

    for a button from the Forms toolbar, you can use this event macro. Put
    it in the worksheet code module (right-click the worksheet tab and
    choose View Code")

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then _
    Me.Buttons(1).Caption = Range("A1").Value
    End Sub




    In article <[email protected]>,
    "Mike" <[email protected]> wrote:

    > Hi everone,
    >
    > Can a button name be made dynamic?
    >
    > For example, you create a button and want its name be read from cell
    > A1. So if A1 read "Mike", the button name automatically become "Mike"
    > and so on.
    >
    > Thanks,
    > Mike


  3. #3

    Re: Dynamic Button Name?

    You can put following code in your worksheet.
    Here, put the correct button name!
    ===================================================
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
    commandbutton1.Caption = Target.Text
    End If
    End Sub
    ===================================================
    Hope this helps.

    ~Yogendra


  4. #4
    Mike
    Guest

    Re: Dynamic Button Name?

    Thank you JE and Yogendra.

    However, I already have a macro under that button like this:

    Sub UnhideSubsystem1()
    Range("System").Select
    Selection.EntireRow.Hidden = False
    End Sub

    How this would become after I add your "Private Sub" to it?

    Mike


  5. #5
    Gord Dibben
    Guest

    Re: Dynamic Button Name?

    Mike

    JE's code would be in addition to the code you have assigned to the button.

    The code is event code and is fired when a change is made in A1 and will
    change the Button Nmae to whatever you entered in A1.

    Don't touch your existing code.

    Right-click on the worksheet tab and "View Code".

    Copy/paste JE's code into that module.


    Gord Dibben Excel MVP

    On 8 Feb 2005 06:36:17 -0800, "Mike" <[email protected]> wrote:

    >Thank you JE and Yogendra.
    >
    >However, I already have a macro under that button like this:
    >
    >Sub UnhideSubsystem1()
    > Range("System").Select
    > Selection.EntireRow.Hidden = False
    >End Sub
    >
    >How this would become after I add your "Private Sub" to it?
    >
    >Mike



  6. #6
    Mike
    Guest

    Re: Dynamic Button Name?

    I tried this but not working! Let me explain one thing please. I have 4
    buttons, each is assigned to a sub. The 4 subs are in one module. Each
    button is assigned to one sub.

    Two questions: take button #1
    1)It is already assigned to sub #1, how would I assign it to JE's sub?
    2)How would I put sub #1 and JE's sub togother? Next to each other like
    this:

    Sub UnhideSubsystem1()
    Range("System").Select
    Selection.EntireRow.Hidden = False
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then _
    Me.Buttons(1).Caption = Range("A1").Value
    End Sub

    Thanks,
    Mike


  7. #7
    Dave Peterson
    Guest

    Re: Dynamic Button Name?

    JE's code wouldn't be assigned to the button. It's the worksheet event that
    actually adjusts the caption of the button (from the Forms toolbar).

    And my question: Don't you want the button to run the same sub--no matter what
    the caption is? If that's true, you shouldn't need to reassign any macro to
    that button.

    On the other hand, if you want to run different code based on the caption, then
    you'll have to share those details.

    ps.

    Read JE's post once more. You'll see that his code doesn't go into a general
    module (like your 4 subroutines). It goes behind the worksheet that should have
    that "recaption the button" behavior.



    Mike wrote:
    >
    > I tried this but not working! Let me explain one thing please. I have 4
    > buttons, each is assigned to a sub. The 4 subs are in one module. Each
    > button is assigned to one sub.
    >
    > Two questions: take button #1
    > 1)It is already assigned to sub #1, how would I assign it to JE's sub?
    > 2)How would I put sub #1 and JE's sub togother? Next to each other like
    > this:
    >
    > Sub UnhideSubsystem1()
    > Range("System").Select
    > Selection.EntireRow.Hidden = False
    > End Sub
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Not Intersect(Target, Range("A1")) Is Nothing Then _
    > Me.Buttons(1).Caption = Range("A1").Value
    > End Sub
    >
    > Thanks,
    > Mike


    --

    Dave Peterson

  8. #8
    Mike
    Guest

    Re: Dynamic Button Name?

    If I to assign JE code to the worksheet, and since I need to make each
    of the 4 buttons have a dynamic name read from a different cell, how
    the worksheet would recognize that?

    Again, I need to make each button has a dynamic name read from a cell.
    So, how JE code here would be used?

    Mike


  9. #9
    JE McGimpsey
    Guest

    Re: Dynamic Button Name?

    One way:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1, B2:B3, D4")) Is Nothing Then
    Me.Buttons(1).Caption = Range("A1").Text
    Me.Buttons(2).Caption = Range("B2").Text
    Me.Buttons(3).Caption = Range("B3").Text
    Me.Buttons(4).Caption = Range("D4").Text
    End If
    End Sub



    In article <[email protected]>,
    "Mike" <[email protected]> wrote:

    > If I to assign JE code to the worksheet, and since I need to make each
    > of the 4 buttons have a dynamic name read from a different cell, how
    > the worksheet would recognize that?


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1