• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Possible to have "click here to clear" button/text for worksheet?

B

BigD

Member
I have a form that I would like to place a button at the top that says "Clear sheet" and it will clear the contents out of a certain cells. Is this poissble? I had no luck with google.
 
Sure thing. First, I'd select all the cells you want to have cleared (use Ctrl/Shift to select multiple cells). Give these cells a name, such as "MyDataCells". You can use the Name Manager to do this, or type the name in the top lefft hand box, to the left of formula bar.

Then, your macro is a simple:
Code: 
Sub ClearCells()
Range("MyDataCells").ClearContents
End Sub
Assign the macro to button of your choice, and you are good to go.
 
Luke, you make it seem easy to know all this stuff. Perfect! Worked like a charm.

Thank you, sir.
 
I figure I would find something to make this harder than it should be. This will not with work with merge cells. Does this make sense? Any way to make this macro work with the cells being merged?
 
Ugh, Merged Cells...the bane of a excel programmer. :(

Thankfully, it is possible to work around in this case. Quick explanation:
When you merge cells, say A2:C2, and then click on the cell and type something, XL is actually storing the value in cell A2, and sort of "hiding" the other 2 cells. SImilarly, when you select the merged cell and give it a name, it only applies the name to A2.
Now, all of this would be great, except when you get to VB. When VB tries to use the name, and goes to edit only A2, it freaks out because A2 is sorta wishy-washy tied into B2:C2, and it's won't modify part of a merged cell. Makes no sense at all, I know, but there it is.

So, this leads us to the work-around. In the defintiion of your named range, need to modify the cell addresses to include all the cells in the merged cell. So, say MyDataCells is supposed to be cell A2, C5, and merged cells
E4:E10. If you go to the Name Manager, you'll see that it would just like:
=Sheet1!A2,Sheet1!C5,Sheet1!E4

Need to change this to include all the cells, like so:
=Sheet1!A2,Sheet1!C5,Sheet1!E4:E10

Then, the VB will have all the cells' addresses and it should work.
 
Any idea why the button has a run time errror '1004' when the sheet is protected (the cells that you can enter data is not locked), but runs perfect when not locked?
 
MY first thought would be to check that the cells that are part of the Merged Cells (I told you they were fun) are also unprotected. Run this short diagnostic to check?
Code: 
Sub LockCheck()
Dim c As Range
Dim i As Integer

i = 0
'Test all of our cells
For Each c In Range("DataCells").Cells
    If c.Locked Then
        Debug.Print c.Address
        i = i + 1
    End If
Next

'Give us a report
If i = 0 Then
    MsgBox "All good!"
Else
    MsgBox i & " cells were locked. See VBE Immediate window"
End If
End Sub
 
check that the cells that are part of the Merged Cells (I told you they were fun) are also unprotected.

This was the problem. Thank you, AGAIN (*100).
 
You must log in or register to reply here.
Back
Top