View original (French)

Excel - How to display the name of a button

Solved
mod77 Posted messages 1320 Registration date   Status Member Last intervention   -  
mod77 Posted messages 1320 Registration date   Status Member Last intervention   -
Hello,

I would like to know how to display the name of the button I click on?
I want to display it in cell A1, for example.

Thank you
Configuration: Windows XP Firefox 3.5.7

25 answers

  • 1
  • 2
  1. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Right-click on the button and its name will appear in the window to the left of the formula bar.

    --

    Best regards.
    Jean-Pierre
    5
  2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    It would be good to specify a bit:
    - where is your button?
    - how did you create it?
    - what software?
    - which version?
    - etc.
    --

    Always zen
    0
  3. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    Ok thanks Le Pingou.
    Actually, when I click on my command button called "BAT A", I display the name written on it using the formula:
    Range("A1").Value = "BAT A"
    And I think it would be simpler if I had a command that already knew the name written on my command button, so I wouldn't have to copy the name written on each command button into each of my display formulas.

    Hence my question...

    Because then I would like that each time I click on my command button named "BAT A",
    the next click displays "BAT A-1", on the following click "BAT A-2" ... incrementing like that as long as I click on it by adding "-x" to the name, where x is the number of times I have clicked on this button.

    And I ... I don't know how to do that.

    Thank you for your help.
    0
  4. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Good evening,
    You can use this small procedure that searches for the buttons and gives their names in the variable [nom], then it's up to you to process according to your needs. 
    Sub button() a = Shapes.Count For c = 1 To a nom = Shapes.Range(c).AlternativeText Next End Sub
    --

    Best regards.
    Jean-Pierre
    0
  6. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    Hello Le Pingou,

    I have inserted your formula into the code of a button, actually just: Shapes.Range(c).AlternativeText
    but it is not accepted.

    Private Sub CommandButton5_Click()
    g = Range("A1").Value
    cl = 12
    indente = Range("C1").Value
    cl = indente + cl
    Cells(3, cl).Value = " "
    nom = Shapes.Range(5).AlternativeText
    Cells(lg, cl) = nom
    Range("A1").Value = lg + 1
    End Sub

    I did this because I don't need to list these command buttons, but as I wrote before, just to avoid writing their names every time for display.

    Thank you nonetheless.
    0
  7. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    To assist you, I need to know the contents of cells [A1] and [C1]!
    Secondly, are you sure that the index of your command button is 5!
    --

    Best regards.
    Jean-Pierre
    0
  8. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    In fact, I put in too much now and it complicates things, so here it is:

    Private Sub CommandButton5_Click()
    name = Shapes.Range(5).AlternativeText
    Cells(lg, cl) = name
    End Sub

    The rest was positioning, not important!

    What is the index of the button? For me, it was the number on the line Private Sub Command..., right?!
    Otherwise, I don't know!

    Thank you
    0
  9. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    This is the number under which [CommandButton5] is registered by the system.
    And in this case, it is not number 5.
    Note: If I ask: To help you, I need to know the contents of cells [A1] and [C1]!
    Feel free to respond!
    --

    Best regards.
    Jean-Pierre
    0
  10. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    I'm sorry, I'm attaching my file, it will be easier:
    https://www.cjoint.com/?bvrLZv38EX
    The code is applied to the "Room 2" button

    Thank you
    0
  11. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    I'm not sure I understood correctly, it's up to you to decide: https://www.cjoint.com/?bwabTooA6a
    --

    Regards.
    Jean-Pierre
    0
  12. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Here is a small addition.
    To retrieve the displayed name of the command button, do as follows:
    Private Sub CommandButton2_Click()
    name = CommandButton2.Caption
    MsgBox("The name of the button is: " & name )
    End Sub
    --

    Regards.
    Jean-Pierre
    0
    1. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
       
      Hello Pingou,

      Thank you for the previous message that I hadn't read yet. It's exactly what I wanted to know! Thank you.

      I’m reaching out again because I’m trying to understand a line of code you sent me and I haven't been able to for 2 hours.


      Private Sub cmdsalbata_Click()
      Dim nb_sal As Byte
      lg = Range("A1")
      cl = 12 + Range("C1")

      nb_sal = Range("salbata") <- WHAT DOES THIS LINE DO PLEASE? What is "salbata"?

      ' **** Cells(3, cl).Value = " " ' this instruction clears the position marker = in line 3
      Cells(lg, cl) = "SALLE " & nb_sal + 1
      Range("salbata") = nb_sal + 1 <- WHAT DOES THIS LINE DO PLEASE? What is "salbata"?
      Range("A1").Value = lg + 1
      End Sub

      I tried to extend my list of command buttons but I can’t change the name like you do. It remains: Private Sub commandbutton10_Click() even if I change it to Private Sub cmdSal3_Click()
      VBA recreates another Private Sub commandbutton7_Click().

      Then it gives me an error on the line nb_sal3 = Range("sal3")

      I don't know if I'm doing it right?!

      Thank you for your help.
      0
  13. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    Hello Le Pingou,

    You understood me! I struggled last night to make progress. But I destroyed more of what I had done
    than made advancement. In fact, I spent more time trying to recover what I had done.
    That's why I greatly thank you for your expertise and patience.

    ' **** Cells(3, cl).Value = " " ' this instruction clears the position marker "=" in row 3 on the layout side

    Otherwise, for the rooms, they are not just for one building, so there are no "room building A", I had simplified (yet again) the names.
    In each building, there may be one or more video room(s), lab room(s), room A, room B...
    but I will correct that.

    I will now examine the program code, and I’m interested in it, as it additionally contains what I wanted to do:
    clear the layout for other configurations. It's great!

    I will post another question later, as I need to describe the interior of the rooms in a table,
    so I'll need to take back the names of the rooms.

    In any case, thank you very much!

    See you later.
    0
  14. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    The Pingou,
    I don't understand these instructions either, could you explain these lines to me please?

    ' the range of arrangements column K from row 4
    covide = Range("IV3").End(xlToLeft).Column? what is "IV3"?
    Range(Cells(3, 12), Cells(3, covide)).ClearContents?
    Range(Cells(4, 11), Cells(Cells(1, 1), covide)).ClearContents?
    Cells(1, 1) = 4

    Thank you
    0
  15. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    covide = Range("IV3").End(xlToLeft).Column ? What is "IV3"?

    "IV3" is the last cell on the right of row 3.
    However, with 2007/2010 it would be better to replace this instruction with this one that works in any version. 
    covide = Cells(3, Rows(3).Cells.Count).End(xlToLeft).Column


    Range(Cells(3, 12), Cells(3, covide)).ClearContents ?
    Range(Cells(4, 11), Cells(Cells(1, 1), covide)).ClearContents ?

    This clears the data in the range.
    --

    Always zen
    0
  16. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Thank you to gbinforme for the explanation of post 14.
    The continuation can be found here: https://www.cjoint.com/?bzrOyb4fWh
    --

    Best regards.
    Jean-Pierre
    0
  17. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    Hello and thank you!

    However, what is "covide"? An instruction or some kind of variable?
    Also, in the instruction End(x'?'Toleft), is there indeed a '1' between the "x" and the "T..."?

    In fact, "IV3" is the last cell on the right of "column" 3, right?!

    Finally, in the last 2 instructions Range(Cells... what defines the range to be cleared, exactly?
    I don't understand where the beginning and the end are.

    Could you please indicate that to me?

    Thank you
    0
  18. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    Hello Le Pingou,

    I hadn't seen your post before my message. Thanks to gbinforme, I have solved the mystery.
    I'm checking out your link, Le Pingou. Thank you.

    See you!
    0
  19. mod77 Posted messages 1320 Registration date   Status Member Last intervention   53
     
    A big thank you to both of you for your explanations! It's clearer for me now.

    Pingou, I couldn't find the name manager in the menu. But it's not a big deal.
    I went through a cell. (Can you believe I was stuck on that since this morning!)
    I added a button and it works!

    There's a function you transformed for me, which is the deletion of the last item. You changed it to deletion of the layout and that's great, better even. So I recreated it, but I can't manage the room number that I delete, as it depends on the room I'm deleting.

    Can you please help me? Thanks.
    0
  20. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Upload the file to https://www.cjoint.com/ and I will give you an explanation based on your file!
    Thank you for the Excel version you are using, I forgot!
    --

    Best regards.
    Jean-Pierre
    0
  21. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    For comparison: I have separated your original [Feuil2] from my proposal [Le_Pingou]: https://www.cjoint.com/?bzwe5rZ8CN

    --

    Best regards.
    Jean-Pierre
    0
  • 1
  • 2