+ Reply to Thread
Results 1 to 28 of 28

Deleting a button in VBA

  1. #1
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Deleting a button in VBA

    Hello,

    I am trying to delete four cells in a Macro, and one of those cells contains a Button. Because the section on my sheet contains multiple rows, each with a button, I cannot write the name of the button into the Macro.

    My macro searches for a value in Column E, then deletes that cell along with cells in the same row for columns F, G, and H. Column H includes the button that must also be deleted.

    I tried "Application.CopyObjectsWithCells = True", but that doesn't work for deleting, and replacing the word Copy with Delete doesn't work.

    How do I delete the button?

    Here's my code: (I forget what to write before and after this to make it appear correctly in this forum. It would be nice if that guide was posted on the page where threads are written):

    Please Login or Register  to view this content.
    Last edited by jeffcp66; 07-14-2019 at 07:56 PM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    The button is a shape so use ActiveWorksheet.Shapes
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    Yes, but how do I identify which shape to delete? Each row has a button, and I cannot identify the button in the macro because we are first searching for the value in column E.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    You need to identify them, the shape type is Commanbutton probably followed by a number
    I suggest you read up on shapes
    Some links (wjhen you Google)
    https://docs.microsoft.com/en-us/off...apes.selectall
    https://www.breezetree.com/articles/...autoshapes-vba
    etc.

  5. #5
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    I'm aware each button has a name... my buttons are CREATED by a separate macro, to add a new record. When a record is added, data is filled in the next available row in columns E, F, and G, and then a button is COPIED and PASTED into column H. Thus, column H is a series of buttons, all named automatically by excel, (e.g. "Button 134").

    So when I go to delete a record, I need to delete the info in the corresponding row, including the button in Column H. Because the macro will NOT know which button is being deleted, I cannot name the button in the macro. So I must find a way to identify the button once the macro finds the proper row.

    So, is there a way of returning the name of a button in VBA? If I can do that, I can delete it.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Deleting a button in VBA

    Do you want to delete the button you just clicked, or another button?
    Also are they ActiveX buttons, or Forms Control ones?

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    When you add a button the numbering is incremented. you can infact create a button and give it a name like for example the cell's address button@ the cells actual address, then all you need to do is delte the button with that name.

    Example: button@$B$3

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    Could you attach a sample file with the macro code you have to add the button?

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Deleting a button in VBA

    Re: I forget what to write before and after this to make it appear correctly in this forum
    A sure way is to read the forum rules. (#2 in your case)

  10. #10
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    I want to delete a button that was created at another time. It's not being pressed to run this macro.

  11. #11
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    Yes, jolivanes, here is the code that creates the section on my worksheet. The main data of the worksheet is entered in the "Journal" section. When data is entered, the macro inserts the 4 cells and button with this code in a section below "Journal". When the data in "Journal" is deleted, the corresponding 4 cells & button must also be deleted. So, given that multiple rows have been added, each with a button, I need to write code that will delete the CORRECT cells and correct button. The code I posted on my original post works fine for deleting the cells, but the button remains.

    Here is how the 4 cells and button are ADDED: (the range "jumpbutt" is where the button lives, so I copy and paste it in column H)

    Please Login or Register  to view this content.
    Last edited by jeffcp66; 07-14-2019 at 07:54 PM.

  12. #12
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Deleting a button in VBA

    Please Login or Register  to view this content.

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    Still, an attachment with dummy-data and the the macros will really help to paint the complete picture.

  14. #14
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    Thanks, Keebellah, but the document is pretty involved and large, it would be too tough to explain. I'm wondering if there's just a way to IDENTIFY the name of a button, if given its cell location.

    The macro will search for and find an adjacent cell, and then 3 columns over in the same row will be the button that needs to be deleted. If I can get code to return the name of that button, I can then have the code delete it, maybe?

    Thanks, jolivanes, for the suggestion. I've been trying to avoid a loop because the number of buttons may get quite large. I'm hoping there's a way to more easily identify the button, since the code will quickly determine where it is located.

  15. #15
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Deleting a button in VBA

    How long did it take when you tried it?

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    Could you just copy the worksheet with the buttons to a new file (right-click worksheet tab) move-copy into a new workbook (create copy) and store it without macros but with the buttons as you have them now?

  17. #17
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    Okay I have attached a stripped file. The "Add file" purple fields are drop-down menus that run a macro to add a file, such as the one included entitled "file 1". When this macro is run, it also adds the file name, date, and ID # (from cell I10) to the "Jumper" table below. It also adds the "jump" button, which when clicked will take the user back up to the original file.

    Once this document contains dozens of files, it is handy to quickly jump to the desired file by using the Jump buttons. Each row of "Jumper" will have its own button.

    So when a file from the upper section is deleted via a drop-down menu macro, the macro ALSO deletes the corresponding line on Jumper, by first locating the ID number on the left of Jumper. When the macro deletes the four cells (in the case of example file, E22:H22), the button is NOT being deleted. So eventually I end up with large numbers of "Jump" buttons piled on top of each other as files are added and later deleted.

    How can the "delete" macro also identify and delete the button in the appropriate row?
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Deleting a button in VBA

    Is the button designed to take you back to B7 in your sample?
    If so, rather than having loads of buttons, why not use a hyperlink?
    If you want to stick with buttons, I suspect that your only option would be to use the code jolivanes supplied.

  19. #19
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    I haven't used hyperlinks before, so I've been at it for the last hour but can't get it to work. The below code is accepted and the macro runs, but it does not generate the hyperlink. It seems that the "SubAddress" is the problem; if I put "Journal!B7" in subaddress, it will work, but I need it to return the Active Cell's address, AND I need that address to change if new files are inserted above the current ones. I don't think hyperlinks can do that, can they?

    Please Login or Register  to view this content.

  20. #20
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Deleting a button in VBA

    AFAIK the address of the hyperlink is fixed, so if you insert new rows the link will take you to the wrong place.

  21. #21
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    Ok, thanks for confirming that. So... hoping there's a way to delete the button without the loop, but I may have to use it.

    jolivanes, can you explain the loop a bit? I'm not familiar with how it works.

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    Meanwhile, here's a code to add a button in column H for the row number passed as a paramter, and the button's name will be "Jump" + address of the cell in column F

    The 0.9 and 0.25 values are to make the button a little smaller that the cell dimensions in column H so they do not opverlap.
    You will have to add your part for the On Action ... to link the jump code you have

    Please Login or Register  to view this content.
    The syntax is

    Please Login or Register  to view this content.
    and you'll get a button in cell H22 with name Jump$F$22

    So if you delete the filename in F22 you can add the code to delete that button too

    Please Login or Register  to view this content.

  23. #23
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Deleting a button in VBA

    If you want to avoid a loop.....
    You have a routine that adds a button and associates (places it) with a cell.
    You could add to that routine a bit that adds the button's name to the cell's comment.
    That way, when the time came to delete the button, the assoication from cell to button would be there without looping. (But you would loose the ability to comment these cells.)


    On the other hand, you probably don't have enough buttons to make the loop noticeable. Unless you are deleting many (thousands) of these buttons at one time, you probably won't notice the time a loop would take.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  24. #24
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    Thanks very much! Can you help me through it a bit? As I'm not sure how to incorporate into my full macro.

    You showed shpRow as =22, but I need it to be whichever row I'm adding to the Jumper section. So should I write "shpRow as Range(row number of jmpX variable?)" (jmpX is Public Range variable) How do I return the row number of jmpX?

    Then why is the first part a "Delete" command?

    And lastly... because the Jumper will be moved down the sheet as new files are added, I can just name it "Jump" & the ID number, which is being copied into column E anyway. So I can write:

    .Name = "Jump" & ActiveCell.Offset(3, 7).Value

    Would that work? That way, no matter where the button is moved to, it is easily locatable by its name. Thanks again!

  25. #25
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    Actually I got it working. I would like to understand the reason for the "Delete" command coming first when CREATING the button, if you don't mind.

    Now I'm working on getting the button its macro assigned

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    I understood that you had a button for each row.
    The fact that the first command is a delete command is to avoid a button with the same name is already present.
    The syntax addAshape shpRow:=22 is how to incorporate it in your own macro.
    Let's say that you have a macro that loops through each row in column F and you want to add a button for each row.
    Your code would look something like this:

    Please Login or Register  to view this content.
    The value in the () is tha parameter you pass to the function so you don't have to write a function each new row.
    Avoid repetive code

    You can also do the same to delete all buttons

    Please Login or Register  to view this content.

  27. #27
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Deleting a button in VBA

    I appreciate all the help! I've got it working properly now. Oddly enough, I don't need to add a separate delete command for the button; when I delete the four cells, the Jump button is deleted, too. So in the end, I don't need to title the buttons, but I'm doing it anyway in case it becomes necessary later. Only one file can be created at a time, so I don't need a loop to create multiple buttons. But your input on building the button with the "c" variable and sizing works great.

  28. #28
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Deleting a button in VBA

    Great, this just a failsafe I always implement

+ 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. Error deleting button shape
    By Neil Humphries in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-08-2018, 10:52 AM
  2. [SOLVED] Finding a command button in a row and deleting it.
    By Ataraxicatom in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2014, 12:17 PM
  3. type mismatch '13' after deleting field with DEL button
    By garbage in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-09-2011, 06:50 PM
  4. Deleting a Button on a Worksheet
    By Tim Childs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2006, 04:55 PM
  5. [SOLVED] Deleting a button
    By Johnny Bright in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-14-2005, 05:06 PM
  6. Deleting Command Button
    By James W. in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2005, 05:06 PM
  7. 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