Use a Macro/VBA Command Button or Not?

kstvns

New Member
Joined
Feb 27, 2011
Messages
20
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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
When do you want the message triggered?
Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top