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. date-prompt.mp4
Microsoft ExcelMicrosoft Office
Last Comment
Martin Liss
8/22/2022 - Mon
Martin Liss
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'?
chris pike
ASKER
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 Liss
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
chris pike
ASKER
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
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.
Unlimited question asking, solutions, articles and more.
chris pike
ASKER
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>?
chris pike
ASKER
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 Liss
Okay I'll modify the current button's code. And no big deal but can I change its name?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
chris pike
ASKER
Yes for sure... OK
Martin Liss
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, and 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
Unlimited question asking, solutions, articles and more.
chris pike
ASKER
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 Liss
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.
chris pike
ASKER
I tried that.. I didn't try the x
Hang on
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
chris pike
ASKER
Mac Excel 2019
chris pike
ASKER
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...
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
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?
Morning Just got in to work.
Let me try, not that I am on a PC
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
chris pike
ASKER
So far so good.
chris pike
ASKER
Wow Amazing.
This works great.....Posting another question Another version.
Martin Liss
Use this version instead if you ever plan on changing Sheet1's name. 29165932b.xlsm
Unlimited question asking, solutions, articles and more.
chris pike
ASKER
Awesome …. So I can change the name of the first sheet?
Martin Liss
Yes.
chris pike
ASKER
Is it supposed to take along time to update? It took 15 seconds when I created just 4 days
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
chris pike
ASKER
I did add 71 names mind you
Martin Liss
Could you make those comments in the new thread please, because that is the current code.
BTW, I've never liked the name 'Update From Today'. How about something like just 'Update' or 'Update Points'?