Solved: Add a Clear Button

[coliervile]

I want to add a Clear Button to a "WorksheetForm"so that a user can clear the "WorksheetForm" and start over if they mess up...CAN YOU HELP?

Best Regards,

Charlie

[coliervile]

I've got to stop doing this so late at night.....

Sub clear_IT()
'
' clear_IT Macro
' Macro recorded 12/6/2002 by ...
'
'
Range("E3,E5,W5,E7,S7,L9,M11,E13,E15,R15,A18,A20,A22,A24,A28,J28,L28,N28,P28,R28,T 28,A34,L30,P30,S30,L31,P31,S31,L32,P32,S32,L33,P33,S33,A34,A38,F38,E41,E43, E45,K45,A53,A63,A65,A67,A69").Select
Range("E3").Activate
Selection.ClearContents
End Sub

Regards,

Charlie

[geekgirlau]

Your solution above will work, but if you make ANY changes to the layout of your input sheet, your macro will also need to be adjusted, so it's not the ideal method.

The merged cells in your input sheet are going to create a problem. Off the top of my head I can think of a couple of options:

Unmerge your cells. You don't actually have to have them merged in order to have your text span several cells across, and rather than merging vertically you can just increase the height of the row. This would allow you to create a macro that looped through the sheet, and cleared the contents of any cells that have the colour applied.
Name the ranges. You could create named ranges "dat1", "dat2" etc. until all the input ranges are named. You need to be careful still with the merged cells; for example, your first range needs to be E3:I3, not just E3. If you do this, you could use the following code to clear them all:
Sub DataClear()
Dim i As Integer


On Error GoTo ExitHere
Do
i = i + 1
Range("dat" & i).ClearContents
Loop

ExitHere:
End Sub

[coliervile]

Thamks for the tip. I'll take a look at itwhen I'm not so tired. How do you name the ranges???? Is it the same as naming the ranges for a "list" not quite sure.

Regards,

Charlie :hi:

[geekgirlau]

Insert | Name | Define

You can use the Name box to the left of the formula bar, but it's not going to work correctly with your merged cells.

[coliervile]

Thanks for you time on this. How will it work when someone is typing in data into one of the a large cell areas, cell A18 " Describe event* (including date, approximate time, and callsign(s) if applicable". It seems that the typed data would just fill=in ceel A18 and not acrossed?

When I "Insert | Name | Define" do I put all of the "dat1", "dat2", and so on in a single column as I've done on my work sheet "LookupList" and used on the "Input" worksheet, cell W5????

Best regards,

Charlie

[mdmackillop]

Keep a blank copy of your form as a hidden sheet. Your macro can copy and paste from that to reset to initial values. Renaming can also be considered, but might give problems with linked data.

[coliervile]

Good morning Sir. I made a copy of the "Inpput" worksheet form, but after that I'm not following what your saying.....

:Thinkingo

Best regards,

Charlie

[coliervile]

Here's a copy of the workbook. I've made a "name list" for the "dat's", but not sure how to apply what geekgirlau was trying to convey for me to do??? Take a look see.

Regards

Charlie

[mdmackillop]

Here's a Clearit Version using a hidden copy
BTW, Using "." in file names can make code manipulation awkward. Best avoided.

[coliervile]

Sorry for the delay had to drive to work. I've downloaded your edit version to the workbook but I'm not getting any data to go into the "template"??? Is the idea to input data into the "Input" worksheet and if the Clear button is activated the data goes from the "Input" worksheet to the "Template" worksheet or is it the other way around???

Also, do you see an issue, like "geekgirlau" does with merged cells?

Appreciate you time helping out on this.

Regards,

Charlie

[mdmackillop]

I agree with GG about merged cells etc. but basically my solution should clear whatever you have entered. One you have sorted out "Input" to work properly, save a copy of the blank form as "Template" and hide it. Click the ClearForm button to clear the form. If you check the macro, you'll see how I've amended the code.

[coliervile]

I'll take another look at the macro. How can I have larger cell to input information without merging the cell in the same row??? The cell would have to be able to wrap the text back around again????

In your opinion, as GG suggested use the dat1, dat2, and dat3, naming each input cell by these "dat", and using GG macro to clear the worksheet form???

Regards,

Charlie

[mdmackillop]

I confess, I've not looked at the detail of your form; only how to clear it as per your initial request. I'll have a look at your merge cell problem.

[coliervile]

Thank you for time and efforts thus far.

Regards,

Charlie