Command button (Active X) multiple tasks

Status
Not open for further replies.

Eikon

New Member
Joined
Jan 26, 2018
Messages
2
I’m working on a VBA formula for a weekly schedule that will fill in cells with the current date and time when clicked. That part I have figured out with the formula below:


Private Sub CommandButton3_Click()
Sheets("Event Scheduler").Range("K20,K35,K50,K65").Value = NOW



The Command button is on the “Daily Schedule” sheet, and the cell(s) that will be populated with the date and time when the Command button is pressed is on the “Event Scheduler” sheet.
The ranges in the current formula represent the following:


K20 = Tuesday
K35 = Wednesday
K50 = Thursday
K65 = Friday


With my current formula, all four cells populate.


What I’d like to happen is for K20 to only fill in the current date and time on Tuesdays, K35 to only fill in on Wednesdays, K50 only on Thursdays etc., all within this single Command Button. If it is not that current day (let’s say it is Thursday) the other cells Tues-Weds will be left unchanged.



I’m using =DAY(DateVal) in cell B2 on the “Event Scheduler” sheet to link the weekly date number that corresponds to the Ranges given. For example:

E20 is a date cell. That cell will have the number 23 in it representing Tuesday’s date for K20 (I will manually update these numbers at at the beginning of each week)

E35 will have the number 24 representing Wednesday’s date for K35

E50 has the number 25 for Thursday’s date for K50

E65 has the number 26 for Friday’s date for K65


I’ve used a variation on IF, THEN commands, but am a self-learner/fairly new to VBA coding, thus keep running into errors and or formulas that don’t produce what I’m looking to do.


Pretty sure I’d be able to figure this out if every Tuesday - Friday dates were the 23rd through the 26th, but that’s not the case.

Praying that I explained this correctly without too much confusion; I know it's a lot. Any and all help is appreciated. :pray:

Best regards,


Eikön
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Duplicate https://www.mrexcel.com/forum/excel...tton-active-x-multiple-tasks.html#post4997308

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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