Sleeper: Click Button to select Button's Cell

[dcmHRA]

OK, this is what I'm attempting to do....

When I click a "button" (created from the "Forms" toolbar), I want the cell that contains the "button" to be selected. Is there a way to do that?

My original formula looked like this:


ActiveWorkbook.Save
ActiveSheet.Unprotect
Selection.End(xlToLeft).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
MsgBox "Was the info deleted correctly? If not, please close the file without saving.", vbYesNo, "NOTICE!"

However, my coworkers believe it is too hard to select a cell and press ctrl+q to get the macro to run...

So I thought a button would be easier....This is what I have now, but it doesn't work!!!


ActiveWorkbook.Save
ActiveSheet.Unprotect
CommandButton.TakeFocusOnClick.End(xlToLeft).Select
Range(Selection, CommandButton.SpecialCells(xlLastCell)).Select
CommandButton.EntireRow.Delete
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
MsgBox "Was the info deleted correctly? If not, please close the file without saving.", vbYesNo, "NOTICE!"



Also, to add to my exciting project....Is there a way to have a macro run when "NO" is selected on the final Message Box?


Don

[Zack Barresse]

Hi Don! Welcome to the board!!

I have a feeling that this is the hard way of going about things. While I don't think your co-workers are correct to say it's impossible, but it is however difficult and obtuse (imho). Can you explain your process and what it is that you are trying to do? This will give us a better idea of how to give you the most optimum solution for your situation. Is there any way you can upload a zipped sample file? (Limit is ~250 kb)

[dcmHRA]

I've attached a sample...

What the macro does is delete everything from the selected "delete here" cell to the bottom of the spreadsheet. It deletes from cell A10:IV65536.

Thanks for your help!

Don

[Norie]

Don

I've download your file but there are no buttons in it.

[dcmHRA]

Sorry! I'm losing my mind... I've attached the file again....the file with buttons! It contains the macro that works, but you have to select cell A10 when you run the macro or press the button if you want it to work correctly...


Thanks,

Don