Add new button in row of an excel table and assign a macro

ginobuonpanino · Aug 4th 2015

Hi, I have an excel table with 16 columns. I want to add another one and in the 17th I want to put a button named "edit" and assign to it the macro "macro1". To write in the excel table I use this code:

Code

RowCount = Worksheets("Soci").Range("A1").CurrentRegion.Rows.Count
    
    With Worksheets("Soci").Range("A1")
        .Offset(RowCount, 0).Value = Me.lblMatricola.Caption
        .Offset(RowCount, 1).Value = Me.txtNome.Value
        .Offset(RowCount, 2).Value = Me.txtCognome.Value
    Select Case True
        Case Me.optFemmina
        .Offset(RowCount, 3).Value = "F"
        Case Me.optMaschio
        .Offset(RowCount, 3).Value = "M"
    End Select
        .Offset(RowCount, 4).Value = Me.txtAnnoIscrizione.Value
    If Me.chkScheda.Value = True Then
        .Offset(RowCount, 5).Value = "Si"
    Else
        .Offset(RowCount, 5).Value = "No"
    End If
        .Offset(RowCount, 6).Value = Me.cboTipoSocio.Value
    If Me.chkCarri.Value = True Then
        .Offset(RowCount, 7).Value = "Si"
    Else
        .Offset(RowCount, 7).Value = "No"
    End If
        .Offset(RowCount, 8).Value = Me.txtIndirizzo.Value
        .Offset(RowCount, 9).Value = Me.txtPaese.Value
        .Offset(RowCount, 10).Value = DateValue(Me.txtDataNascita.Value)
        .Offset(RowCount, 11).Value = Me.txtPaeseNascita.Value
    If Me.txtCodiceFiscale1.Value = "" Then
        .Offset(RowCount, 12).Value = Me.txtCodiceFiscale2.Value
    Else
        .Offset(RowCount, 12).Value = Me.txtCodiceFiscale1.Value
    End If
        .Offset(RowCount, 13).Value = Me.txtCellulare.Value
        .Offset(RowCount, 14).Value = Me.txtEmail.Value
      End With

Display More

So every time I write a new row to the excel table I add even a button. Is there a way?

Thanks

**cytop** · Aug 4th 2015

Re: Add new button in row of an excel table and assign a macro

Don't add a button in every row... Add one (Forms, not ActiveX) button, name it 'btnEdit' (or change the code to suit), assign your macro to it and paste the following in the worksheet class module...

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


   Dim rng              As Range


   On Error Resume Next


   If Target.Cells.Count = 1 Then


      Dim MaxRow        As Long
      MaxRow = ActiveSheet.Range("A1").CurrentRegion.Rows.Count


      If Target.Row > MaxRow Then
         ActiveSheet.Shapes("btnEdit").Visible = False
      Else
         
         Set rng = ActiveSheet.Cells(Target.Row, 17)
         
         With ActiveSheet.Shapes("btnEdit")
            .Top = rng.Top
            .Left = rng.Left
            .Width = rng.Width
            .Height = rng.RowHeight
            .Visible = True
         End With
      End If
   End If


End Sub

Display More

The button will move to Column 17 of whichever row is selected and sized to fit the cell. If a row number greater then the count of rows in the UsedRange is selected then the button will be hidden.

ginobuonpanino · Aug 4th 2015

Re: Add new button in row of an excel table and assign a macro

Thanks for the help cytop... I've tryed and it works, but not as I wish. The button don't fit completely the cell (it's a little on the left) and moreover it's not always visible. I'm not an expert one in vba, but there is not a way to just use the .offset (rowcount, 18) to put the button in this cell (everytime I add a new row to the excel table? Or to make it always visible?

**cytop** · Aug 5th 2015

Re: Add new button in row of an excel table and assign a macro

Quote

...The button don't fit completely

Can't see how - the button is sized to the cell using the cell co-ordinates and measurements. Also can't see how it's not always visible. As long as the row is less then the number of used rows in the range then it will be visible and it uses the same calculation you originally quoted to find the number of rows - so other parts of your code should be displaying the same issue. If you simply added a button to every row, you (presumably) would have the same sizing problem.

Post a sample workbook that displays these issues.

Please do not quote entire posts in your reply - if anyone wants to read them they can simply scroll up a little...

ginobuonpanino · Aug 5th 2015

Re: Add new button in row of an excel table and assign a macro

that's the file I'm working with... It's in italian I hope it's not a problem, in case I will traslate it... There is only 2 rowas by now. As you can see the button don't fit perfectly and it appears only when I select the row, and finaly just for the selected row.

https://www.dropbox.com/s/elm70ngkv9tzun7/Sample.xlsm?dl=0

Thanks for the help

**cytop** · Aug 5th 2015

Re: Add new button in row of an excel table and assign a macro

The banded table formatting draws some odd borders, bu tI didn't think it was that noticable. In any case, the 'Modify' column should not really be part of the table - you should change the table definition.

The button name was "Button 7" - you were referring to it as "Pulsante 7", which is understandable I suppose, but the code needs to refer to "Button 7", or whatever you rename it to.

You can rename the button (don't confuse the Name with the Caption) by clicking on Design Mode on the Developer tab, then clicking once on the button ans changing the name in the 'Name' box which is displayed to the left of the Formula Box, just under the Ribbon.

The code below has been modified so the button will only be displayed when the cursor in actually in the data table. This has the slightly unfortunate effect of making the button disappear if the user moves the cursor to column R, but preferable, in my opinion, to having one button per row.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    Dim rng              As Range
     
    On Error Resume Next
     
    If Target.Cells.Count = 1 Then
         
        If Intersect(Target, ListObjects("Tabella1").DataBodyRange) Is Nothing Then
            ActiveSheet.Shapes("Button 7").Visible = False
        Else
             
            Set rng = ActiveSheet.Cells(Target.Row, 18)
             
            With ActiveSheet.Shapes("Button 7")
                .Top = rng.Top
                .Left = rng.Left
                .Width = rng.Width
                .Height = rng.RowHeight
                .Visible = True
            End With
        End If
    End If
     
End Sub

Display More

If you really, must have one button per row, then change the Procedure cmdIscrivi_Click on the userform to include the following code block

Code

...
      ...
      .Offset(RowCount, 11).Value = Me.txtPaeseNascita.Value
      If Me.txtCodiceFiscale1.Value = "" Then
         .Offset(RowCount, 12).Value = Me.txtCodiceFiscale2.Value
      Else
         .Offset(RowCount, 12).Value = Me.txtCodiceFiscale1.Value
      End If
      .Offset(RowCount, 13).Value = Me.txtCellulare.Value
      .Offset(RowCount, 14).Value = Me.txtEmail.Value


      '// ***********************************************************************
      '// Additional code to add button to every row...
      '// ***********************************************************************
      Dim btn           As Button
      Dim t             As Excel.Range


      Set t = .Offset(RowCount, 18)
      Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)


      With btn
         '// The code from the Workbook_Open event was moved to a new procedure
         '// 'ShowForm'. This procedure is called by a button click AND the
         '// Workbook_Open event to test but you need to code as you require.
         .OnAction = "ShowForm"
         
         '// Yes, I understand 'Pulsante' is 'Button' - just didn't know what
         '// caption you wanted. 
         .Caption = "Pulsante"
      End With
      '// ***********************************************************************
      '// End of additional code
      '// ***********************************************************************


   End With
   ' Reset campi
   ... 
   ...

Display More

(You will need to change all references to Col 18 to 17 to agree with your initial request).

ginobuonpanino · Aug 5th 2015

Re: Add new button in row of an excel table and assign a macro

Ok, it works great now, but you make me think. Is there a way to show the edit button just in the selected row next to where I click? It's just an idea, maybe it's impossible, but I want to ask. Anyway Thank a lot!

**cytop** · Aug 6th 2015

Re: Add new button in row of an excel table and assign a macro

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

Add new button in row of an excel table and assign a macro

Participate now!

Share