how may i delete a row from a table by selecting the row in excel without adding too many objects(bu-CodePudding

im currently trying to improve my actual solution by adding a new one. Summarizing ive a table in excel that creates a delete button for each new row inserted. But if i keep doing like that its going to slow down my file over time cause hundreds of lines are gonna be inserted, consequently alot of objects(buttons). So i need it to be one button that moves around to whichever row is selected by the user.

Thats my currently solution(which isnt the best):

And thats wat im trying to active:

Than i ended up stumbled with this code made by

CodePudding user response：

Please, copy the above code in the sheet shtList code module. Then, associate remove_item sub to the necessary 'button'. It looks to be a shape:

Option Explicit

Private actCell As Range
Private Const myTblName As String = "Table3"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim btnRemoveItem As Shape, tbl As ListObject

      Set tbl = shtList.ListObjects(myTblName)
      If Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then
            Set actCell = Target
            Set btnRemoveItem = shtList.Shapes("btnRemoveItem")
            btnRemoveItem.top = actCell.top
     End If
End Sub

Sub remove_item()
    Dim answer As VbMsgBoxResult, tbl As ListObject
    answer = MsgBox("Are you sure you want to remove this item?", vbYesNo   vbQuestion, "Remove Item")
    If answer <> vbYes Then Exit Sub 'exit if "Yes" not chosen
    
    Set tbl = shtList.ListObjects(myTblName)
    tbl.DataBodyRange.rows(actCell.row - tbl.DataBodyRange.row).Delete
End Sub

The necessary button will jump on the row of the table where a cell is selected. If selection is done somewhere else than in the table DataBodyRange, nothing will happen in SelectionChange event...

CodePudding user response：

I think FaneDuru is right regarding the reason for your 424-error.

But I would like to suggest you a less complex solution. You don't need shapes (that slow down everything) - instead you can use hyperlinks.

You add a hyperlink per cell with this code (placed in a normal module - and called whenever you need it):

Public Sub writeDeleteButtons(rg As Range)
Dim c As Range
For Each c In rg.Cells
    c.Hyperlinks.Add c, vbNullString, , , "delete"
Next
End Sub

This could look like this:

In your worksheets-module you have the following event which is triggered whenever a hyperlink is clicked. Target.Range returns the cell that was clicked.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

If Target.Range.Value = "add" Then
    'addRow Me   'propably you want to implement the same for adding a row
ElseIf Target.Range = "delete" Then
    deleteRow Me, Target.Range.row
End If

End Sub

And finally you have your routine to delete the row through a sub that is again placed in a normal module:

Public Sub deleteRow(ws As Worksheet, rowIndex As Long)
If vbNo = MsgBox("Are you sure you want to remove this item?", vbYesNo   vbQuestion, "Remove Item") Then
    Exit Sub
End If

ws.Rows(rowIndex).Delete
End Sub

You could adjust deleteRows to pass the clicked cell itself and go on from there.

A completely different solution would be to have a custom ui context menu ... but this would need a lot more explanations ...