Ask Your Question

Delete button on spreadsheet from a macro (solved) [closed]

asked 2016-03-04 18:06:22 +0100

Pansmanser gravatar image

updated 2016-03-09 17:12:57 +0100

Edited Demo.ods

The attachemnt is a stripped down version of a spreadsheet for recording expenses. Periodically I archive the data by duplicating the 'TripRecords' via the 'Save Data' button on the sheet. The associated macro copies 'TripRecords' to 'Newsheet' (simplified here) and 'freezes' the data by getDataArray/setDataArray. This is all working.

Can I now delete the button on the copied sheet to avoid the data being corrupted?

Thanks to Pierre-Yves Samyn, the job is mostly done. RemoveByIndex() & Dispose() dissociate the macro from the button and the control from the shape (although I'm not sure I fully understand my own code), but the shape is still there. The data is now 'safe', but it would be tidy to delete the button from the sheet, which should be quite simple ...

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-31 15:26:42.053066


Would you mind to attach an example document demonstrating your issue?
"Macros are bad" is a not too excentric position among spreadsheet users. If you do not need the maxros at all, you can delete them from the containing module.
Archiving data in the full sense, however, may require to detach them from formulae. This is not a corruption but a change of purpose. Doing this the archive must not overwrite the functional spreadsheet, of course.

Lupp gravatar imageLupp ( 2016-03-04 18:53:55 +0100 )edit

I'll act on this as soon as I have time. Sorry for delay. How do I make an attachment to my comment?

Pansmanser gravatar imagePansmanser ( 2016-03-06 10:03:08 +0100 )edit

Sorry! No attachments to comments. To attach a file you have to edit your question (or an answer).

Lupp gravatar imageLupp ( 2016-03-07 00:04:34 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2016-03-09 17:12:08 +0100

Pansmanser gravatar image

This turned out to be a simpler problem than I realised. I had to get the range of ALL cells overlapped by the button and clear objects therein.

oRange = oSheet.getCellRangeByPosition(left,top,right,bottom) oRange.clearContents(

I had previously assumed that the cell containing the anchor would be sufficient, but not so.

Thanks to those who helped my journey of discovery.

edit flag offensive delete link more

answered 2016-03-05 13:41:42 +0100

pierre-yves samyn gravatar image


See examples in DeleteButton.ods


edit flag offensive delete link more


Many thanks, Pierre-Yves. This does almost exactly what I want. What I have here does the important stuff, but doesn't actually delete the button. It seems to have one more level of 'nesting' (form within forms) than I expected. (Sorry - this listing loses its layout) oForms = oSheet.DrawPage.Forms For i = 0 to oForms.Count - 1 oForm = oForms.getByIndex(i) If oForm.hasByName("SaveData") then oForm.removeByIndex(i) oForm.dispose() exit sub Endif Next i

Pansmanser gravatar imagePansmanser ( 2016-03-06 23:57:16 +0100 )edit

Hi - I do not understand your comment.

You asked "how to delete button" and that is what my code do. What you add is the removal of the form which is quite something else and was not asked.


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-03-07 09:29:57 +0100 )edit

Perhaps my terminology is wrong Pierre-Yves; I understood that the button was a form. Your subroutine did exactly what I wanted,but it was called from a button on the same sheet. The only difference is that mine is called from a button on a different sheet, so the method oEvent.Source.Model.Parent did not connect to the sheet I was working on. It's very close to perfect. I have attached the spreadsheet to the question, incorporating parts of your code. Mercie mille fois pour votre aide!

Pansmanser gravatar imagePansmanser ( 2016-03-07 11:16:58 +0100 )edit

Question Tools

1 follower


Asked: 2016-03-04 18:06:22 +0100

Seen: 1,648 times

Last updated: Mar 09 '16