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

chris pike
chris pike used Ask the Experts™
on
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.
Prompt user to choose date FROM to Last date Cleardate-prompt.mp4
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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'?

Author

Commented:
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"

Thoughts?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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?
Thanks

Author

Commented:
Here is a Mock Up of button set Up
date from to currentCopy-of-29165656c_1.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
Hmmmmm
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
Thoughts>?

Author

Commented:
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
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Okay I'll modify the current button's code. And no big deal but can I change its name?

Author

Commented:
Yes for sure... OK
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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,
Calendarand 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.
29165932.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Hang on, making a small change.

Author

Commented:
I am online.... Watching Stephen King's It Chapter 2
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I've replaced the workbook.

Author

Commented:
OK.  I see you said hang on making a change so I didn't download the above
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If you haven't done so already you can download it now.

Author

Commented:
Might be a MAC thing but unless I delete a range of dates it will now update.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please give me an example of what you mean.

Author

Commented:
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
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
I tried that.. I didn't try the x
Hang on

Author

Commented:
Mac Excel 2019

Author

Commented:
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...
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I think the culprit was the small change I made in haste. See if this is better. If not then please create one of your videos and show me what you do.
29165932a.xlsm

Author

Commented:
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 today....it 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?

Author

Commented:
Wait nope///// with one column deleted it doesn't update all column to today. Just today
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It doesn't do this?
2019-12-04_04-58-53.mp4

Author

Commented:
Morning Just got in to work.
Let me try, not that I am on a PC

Author

Commented:
So far so good.

Author

Commented:
Wow Amazing.
This works great.....Posting another question Another version.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Use this version instead if you ever plan on changing Sheet1's name.
29165932b.xlsm

Author

Commented:
Awesome …. So I can change the name of the first sheet?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes.

Author

Commented:
Is it supposed to take  along time to update? It took 15 seconds when I created just 4 days

Author

Commented:
I did add 71 names mind you
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Could you make those comments in the new thread please, because that is the current code.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial