+ Reply to Thread
Results 1 to 6 of 6

Finding a command button in a row and deleting it.

Hybrid View

Ataraxicatom Finding a command button in a... 12-18-2014, 08:33 PM
berlan Re: Finding a command button... 12-18-2014, 08:59 PM
Ataraxicatom Re: Finding a command button... 12-18-2014, 09:24 PM
Ataraxicatom Re: Finding a command button... 12-19-2014, 11:39 AM
berlan Re: Finding a command button... 12-19-2014, 11:42 AM
Alf Re: Finding a command button... 12-19-2014, 12:17 PM
  1. #1
    Registered User
    Join Date
    07-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    57

    Finding a command button in a row and deleting it.

    Hi there!
    I am trying to find a way to select a command button in a row and delete it. There will always be a command button in the row. All of the command buttons have the same text.

    Sub BeginRemoval(RemItem() As String)
    Sheets("A-M").Activate
    Dim Rng As Range, Cel As Range
    Dim LastRow As Integer, x As Integer, i As Integer
    Dim RemObj As Object 'Not sure if this is correct or if it should be shape?
    
    With Sheets("A-M")
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Range("A2:A" & LastRow)
    
    For i = LBound(RemItem) To UBound(RemItem)
        x = 2
        For Each Cel In Rng
            If Cel.Value = RemItem(i) Then
                RemObj.Delete 'This is where I am stuck, I just put this here as a place holder.
                Rows(x).EntireRow.Delete
            End If
        x = x + 1
        Next Cel
    Next i
    End With
    
    End Sub
    RemItem is an array that was loaded in a previous sub. I do not have problems with that sub. The data in the array is string data (names), but if you think it is relevant, then I can put that here... Really just wondering if there is a way to select a command button in a specific row.

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Finding a command button in a row and deleting it.

    Hi Ataraxicatom,

    try this as an alternative, which looks at the row of each commandbutton and matches with the cell.

    Sub BeginRemoval(remitem() As String)
        
        Dim e, rngFind As Range, C As OLEObject
        
        With Sheets("A-M")
            For Each e In remitem()
                If e <> "" Then
                    Set rngFind = .Columns(1).Find(e, , xlValues, xlWhole)
                    If Not rngFind Is Nothing Then
                        For Each C In .OLEObjects
                            If TypeName(C.Object) = "CommandButton" Then
                                'this is what you are looking for, matching commandbutton row with the cell
                                If C.TopLeftCell.Row = rngFind.Row Then C.Delete
                            End If
                        Next C
                        rngFind.EntireRow.Delete 'or from your code, .Rows(2).delete
                        Set rngFind = Nothing
                    End If
                End If
            Next e
        End With
    
    End Sub
    Regards,
    berlan
    Last edited by berlan; 12-18-2014 at 09:01 PM.

  3. #3
    Registered User
    Join Date
    07-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Finding a command button in a row and deleting it.

    That did not work. It just shrinks the buttons up on row 2. The rest of it works just fine. I'll play with it a bit and see if there is something I am missing.

    -Ok! I lied! That works just fine. I am using form control buttons and not active x buttons. Is there a way to do the same thing for form controls that you did for the active x objects?
    Last edited by Ataraxicatom; 12-18-2014 at 10:44 PM.

  4. #4
    Registered User
    Join Date
    07-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Finding a command button in a row and deleting it.

    Ok, so I found a way to do it! I wouldn't have been able to if you had not helped me out. Thanks a million berlan!

    Sub BeginRemoval(RemItem() As String)
    Sheets("A-M").Activate
    Dim Rng As Range, Cel As Range
    Dim LastRow As Integer, x As Integer, i As Integer
    Dim RemObj As Shape
    
    With Sheets("A-M")
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Range("A2:A" & LastRow)
    
    For i = LBound(RemItem) To UBound(RemItem)
        For Each Cel In Rng
            If Cel.Value = RemItem(i) Then
                For Each RemObj In ActiveSheet.Shapes
                    If RemObj.TopLeftCell.Row = Cel.Row Then
                        RemObj.Delete
                    End If
                Next RemObj
                Cel.EntireRow.Delete
            End If
        Next Cel
    Next i
    End With
    
    End Sub
    Just in case anyone else needs it in the future. :D

  5. #5
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Finding a command button in a row and deleting it.

    Glad it worked out, thanks for the kind feedback and positive rep added.

    Best,
    berlan

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,790

    Re: Finding a command button in a row and deleting it.

    Perhaps something like this as well?

    Option Explicit
    
    Sub DelShap()
    Dim i As Integer
        For i = ActiveSheet.Shapes.Count To 1 Step -1
        ActiveSheet.Shapes(i).Delete
        Next
    End Sub
    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Not deleting the selected data from listbox by one click on command button
    By biznez1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-31-2013, 12:05 PM
  2. [SOLVED] Move record to different sheet and then deleting it through a userform command button
    By dev111ski in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2012, 06:30 AM
  3. Replies: 1
    Last Post: 09-17-2007, 09:57 PM
  4. Deleting Command Button
    By James W. in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2005, 05:06 PM
  5. deleting a row with checkbox and command button
    By student123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2005, 02:19 PM

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