View original (French)

Excel VBA Programming, button linked to a row

Solved
PANTPUFLE -  
PANTPUFLE Posted messages 10 Status Member -
Hello,

I have been looking for a solution to my problem on different forums without success. I created an Excel document to schedule various orders and their delivery dates. I added a "Schedule" button in front of each line to delete the row where it is located. However, the button is programmed with the cell number, which means that if I add a row in the meantime, the button will not be in front of the correct line.

I hope I'm being clear, but basically, I need to delete columns C to G but only on the line of the button. I’m not sure if this is possible.

Thank you in advance for your response.

Configuration: Windows / Firefox 80.0

3 answers

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     
    Hello, I don’t understand the connection between your first and second paragraphs.
    I wonder if it's a good idea to have a button on each line.
    Can you share your file?
    0
    1. PANTPUFLE Posted messages 10 Status Member
       
      Hello, I replied to your message below :)
      0
  2. PANTPUFLE Posted messages 10 Status Member
     
    Hello, I'm aware that I'm not being very clear in my statements, so I'll try to clarify.

    First of all, I'm providing you with the link to the Excel file because I couldn't integrate it in any other way:
    https://www.cjoint.com/c/JIxinC7lWYa

    On my order sheet, we can see for the client SNCF (which is an example) that there are different orders, different dates, etc. In front of each line, there is a button associated with the line. When my order is delivered, it would be more convenient for me to delete my data from the table in the cells from C to G. My problem is the following. If I have an additional order one day, I would need to add a line to this table, thus changing the cell numbers. The button not remaining in its assigned cell, the VBA code would then be incorrect as it would delete the data from another cell.

    I would therefore like to either be able to link my button to a cell, regardless of line addition or deletion, so that the buttons remain on the correct cell number assigned to them. Or to know if it is possible to program the button so that it deletes the data from its cell without having to specify the numbers in Range("C32:G32").

    Thank you very much for your response.
    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       
      ```html 
      Private Sub CommandButton1_Click() Dim line As Long line = Me.CommandButton1.TopLeftCell.Row Cells(line, "C").Resize(1, 5).ClearContents End Sub
      ```
      0
      1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         
        or, considering that the button is in column A: 
        Private Sub CommandButton10_Click() Me.CommandButton10.TopLeftCell.Offset(0, 2).Resize(1, 5).ClearContents End Sub
        0
      2. PANTPUFLE Posted messages 10 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         
        Thank you very much, but after trying it, it erases the first row of my first table but not the row where the button is located.
        I will try to see if with your program I can find an alternative, but if you had a solution to my problem, it would greatly help me.
        Thank you for your help.
        0
      3. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > PANTPUFLE Posted messages 10 Status Member
         
        Can you show your modified code? Everything is going well on my end.
        0
  3. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     
    another method would be to use a single macro and associate all the buttons with this macro. 
    Sub button() ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(0, 2).Resize(1, 5).ClearContents End Sub

    you can even copy the button across all rows.
    0
    1. PANTPUFLE Posted messages 10 Status Member
       


      I tried with the latest method, it says it doesn't recognize "Buttons".
      "Cannot read property Buttons of class Worksheet"
      0
      2. PANTPUFLE Posted messages 10 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         
        Alright, I'll take a look at that. Thank you very much for all your help anyway :)
        0
      3. PANTPUFLE Posted messages 10 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         
        It works very well, thank you very much!
        0