Results 1 to 2 of 2

Creating a Button with VBA that can delete itself + the Row it's created on

Threaded View

  1. #1
    Registered User
    Join Date
    03-10-2014
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Creating a Button with VBA that can delete itself + the Row it's created on

    I am automatically generating rows on my Excel with user input data from a form
    They click a button, it pops up the Form, they fill in their data and it adds it to the rows.

    With this I want to create a "remove row button" at the end of these inserted rows.
    that the button when clicked will remove the row and the button.

    Declared variables

    Dim iRow As Long
    Dim ws As Worksheet
    Dim btn As Button
    Set ws = Worksheets("Sheet1")

    I create the rows

    ws.Cells(iRow, 2).Offset(1).EntireRow.Insert
    ws.Cells(iRow, 6).Copy
    ActiveSheet.Paste Destination:=ws.Cells(iRow, 6).Offset(1)

    the data fills into the rows from all the user fields...

    I then create the button

    Application.ScreenUpdating = False
    Set btn = ws.Buttons.Add(ws.Cells(iRow, 9).Left, ws.Cells(iRow, 9).Top, ws.Cells(iRow, 9).Width, ws.Cells(iRow, 9).Height)
    With btn
        .Caption = "Remove Row"
        .Name = "remove" & iRow
        .OnAction = DoNothing
    End With
    Application.ScreenUpdating = True

    This lands it in the 9th Column of the Row I just created
    I currently have OnAction set to do nothing, just so it doesn't error out

    but I have tried setting the OnAction to remove the button itself
    ws.Shapes("remove" & iRow).delete

    but it errors out, as I am not sure what is off with this.

    I also tried setting the OnAction to delete the Inserted Row it was created on, but couldn't get that to function either.
    and I need the created button to do both actions.
    As later on I want to lock the excel, and leave these generated buttons as the only way to remove the rows they create.
    Last edited by ALoelke; 06-16-2015 at 11:30 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating a button to delete entries one by one in My TIMER macro...???
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-11-2014, 06:27 PM
  2. [SOLVED] Created add-in - how to get it on ribbon with button
    By D_N_L in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-02-2014, 12:29 PM
  3. Click events on buttons created at runtime only work on the last button created
    By Treacle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2010, 05:44 AM
  4. [SOLVED] Button not bring up Macro I created
    By A.S. in forum Excel General
    Replies: 1
    Last Post: 07-22-2005, 06:05 PM
  5. Can I delete .xlk backups and why are they created?
    By dgoranson in forum Excel General
    Replies: 4
    Last Post: 07-11-2005, 07:05 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