Link to home
Start Free TrialLog in
Avatar of chris pike
chris pikeFlag for Canada

asked on

Need additional macro to clear columns with user choosing which dates to start from.

I need a modification to my sheet.
Currently the sheet prompts to add data from column "E"  to all date columns from last open.

What I need now is the ability to do the same thing but from a user given date, this should clear out all previously written data with the new data from "E"

So, If the sheet was last updated Nov 1 and it is now Dec 1 ,  I need the ability to update data from Nov 15th or whatever date.
User generated imagedate-prompt.mp4
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

So when the workbook is opened or when the 'Update From Today' button is clicked I assume you want to be able to update from a given date to the present. I assume you want to be asked for a start date whenever the button is clicked but do you want to be asked every time the workbook is opened?

BTW, I've never liked the name 'Update From Today'. How about something like just 'Update' or 'Update Points'?
Avatar of chris pike


Ummm, not on open.... just a button, to update from chosen date.
Can we kill the prompt for base rate to update on open?
Instead when open it, it will prompt to update from last date through to today...… Or NOT to update at all.
I didn't think it would be as easy as it is to just click and it populates the dates and data from "E" so when it's open just click and bammmm/
Love it...

So yah, I think a button AFTER open, to choose date to start the clear and update from.
Maybe ……
ENTER "Clear From Date"
ENTER "Reset From Date"

So are you suggesting that there be no updating at all upon open?

For the rest of your requirement I believe that I can just modify the code behind the ''Update From Today' to ask for the start date. And talking about that button, what about its name?
On open we can still ask if the user wants to update.... and it will update what ever is already in "E".. It will create dates and fill in data from "E"

Or.... if user decides to NOT update then it will just open, and then they can click a button that will prompt a date or they can enter a date into a cell at the top or something. This is where they will pick a date that they want to update FROM with whatever adjustments they already have made from the base or the bonus. Make sense?
Here is a Mock Up of button set Up
User generated imageCopy-of-29165656c_1.xlsm
If you wouldn't mind a userform which would show you a calendar being displayed every time the current Update button is clicked, there'd be no need for two buttons.
So maybe if they click todays's date it will just fill in all dates..... to today,
And if the select any other date in the past it will start from there
I don't mind two buttons
Just wanted to make it easier for you to create a new button that would just select and clear as in the video
But which evr way you think
Okay I'll modify the current button's code. And no big deal but can I change its name?
Yes for sure... OK
In this workbook I did not make any changes to the Workbook_Open procedure. Let me know if a change is needed.

When the calendar form opens and you want to "update from", you must explicitly click one of the dates (which are actually buttons). For example today is the 3rd and when the calendar opens you would see this,
User generated imageand to actually update from the 3rd you would have to click that button, and instead if, say, you wanted to update from 11/10/18 you would need to manipulate the month and year comboboxes and then click the 10th.

If you just want to do the normal updating then click 'Close' or the form's "X" button directly.
Hang on, making a small change.
I am online.... Watching Stephen King's It Chapter 2
I've replaced the workbook.
OK.  I see you said hang on making a change so I didn't download the above
If you haven't done so already you can download it now.
Might be a MAC thing but unless I delete a range of dates it will now update.
Please give me an example of what you mean.
I am at home using my iMac...... When I click update points the calendar pops up, I double click on the date I want to update from, nothing happens, but if I delete the date and hot the button again . it puts the correct dates in the blank spots and the data
I am at home using my iMac...
What version of Excel are you using?
...I double click on the date I want to update from, nothing happens...
Don't double-click. Just click the date and either press 'Close' or the X.
I tried that.. I didn't try the x
Hang on
Mac Excel 2019
Tried single clicking and clicking x
Tried single clicking and close

Nothing happens. I made sure I changed the data in the total column first...
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yah still only pushes when I delete the range.
I tried single click and ex
I tried single close but neither pushes any data

If I delete even ONE column....Like works. looks like one column has to be deleted.
Could you just precede your code to find todays date and delete that column before it does the prompt?
Wait nope///// with one column deleted it doesn't update all column to today. Just today
Morning Just got in to work.
Let me try, not that I am on a PC
So far so good.
Wow Amazing.
This works great.....Posting another question Another version.
Use this version instead if you ever plan on changing Sheet1's name.
Awesome …. So I can change the name of the first sheet?
Is it supposed to take  along time to update? It took 15 seconds when I created just 4 days
I did add 71 names mind you
Could you make those comments in the new thread please, because that is the current code.