Avatar of chris pike
chris pike
Flag 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.
Prompt user to choose date FROM to Last date Cleardate-prompt.mp4
Microsoft ExcelMicrosoft Office

Avatar of undefined
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
chris pike

ASKER
Here is a Mock Up of button set Up
date from to currentCopy-of-29165656c_1.xlsm
Martin Liss

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.
Get an unlimited membership to EE for less than $4 a week.
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,
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 Liss

Hang on, making a small change.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
chris pike

ASKER
I am online.... Watching Stephen King's It Chapter 2
Martin Liss

I've replaced the workbook.
chris pike

ASKER
OK.  I see you said hang on making a change so I didn't download the above
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Martin Liss

If you haven't done so already you can download it now.
chris pike

ASKER
Might be a MAC thing but unless I delete a range of dates it will now update.
Martin Liss

Please give me an example of what you mean.
Get an unlimited membership to EE for less than $4 a week.
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...
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
chris pike

ASKER
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
chris pike

ASKER
Wait nope///// with one column deleted it doesn't update all column to today. Just today
Martin Liss

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

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
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.