Use a Macro/VBA Command Button or Not?

[kstvns]

Excel 2003 using Win XP

I'm creating a timesheet. Cell B4 is where the 1st day of each month is entered. In column A, each day of the month is based on the date in B4.

I want the data in range F8:P38 to be erased automatically in one step versus manual deletion. My initial thought is to have some type of pop-up message asking the user if he is sure he want to erase the data when he enters a date in B4. If "Yes", delete. If "No", data remains.

Any ideas?

 

[Simon Lloyd]

When do you want the message triggered?

Dim Ans
Ans = MsgBox("Clear Data?", vbYesNo, "Start with clean sheet")
If Ans = vbYes Then
Range("F8:P38").ClearContents
MsgBox "Range Cleared", vbOKOnly, "Action Confirmed"
Else
MsgBox "Range Not Cleared", vbOKOnly, "Action Confirmed"
End If

 

[Greg Truby]

A couple of things to bear in mind:

1. How widely will this be distributed?
   • Just you?
   • Your Dept?
   • Your Facility?
   • Your Company?
2. Depending on the answer to question #1, you may need to know about versions of Excel being used due to your proposed solution for handling macro security issues
3. What are your proposed solutions for macro security?
4. As Simon asks, what is your desired trigger?
   • Changing a particular cell?
   • Clicking a button on a toolbar?
   • Clicking a button on the worksheet?

 

[kstvns]

Good questions, Simon and Greg! Thanks for asking as they allow me to really think about it.

1. The timesheet is accessible from our intranet. Employees will also be able to save the timesheet on their computers.
2. We all use Excel 2003.
3. Macro security? Please elaborate. The current timesheet has a macro. For it to work, the employees must enable macros when the Security Warning pop-up box appears.
4. I decided I will create a Reset button in cell A39. So, to answer Simon's question, clicking on this button will be the trigger.

 

[Simon Lloyd]

well in that case you can use the code i supplied directly in the CommandButton_Click event

 

[kstvns]

I have. I had to modify it a bit.

Thanks for you assistance!