Remove start up prompt (VBA) and add double click name to drop name in second sheet.

chris pike
chris pike used Ask the Experts™
on
I have a great VBA sheet that create days and fills data from Column "E"
I had a new button created that enables me to input data from any date.

1. I need a version of this sheet without the start up prompt to offer as an option to the final user of the program.

One addition thought. Whenever a change is made to the rate or a bonus rate is given, is there a better way than manually entering a comment into the cell?
Currently the user is right clicking on a cell and adding a comment. What other options are there.

I have an Idea. Can we double click on any name in the list and it will jump to "Change History" sheet and drop name in next available row(ignore blanks).? Remember the name list will be quite large.
That might be a good idea.
ChangeHistory_01.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Which cell or cells are you adding the comment to and what does the comment say?
chris pikeOffice Admin

Author

Commented:
OH boy my excel keeps crashing
My have to reboot.

At any time. the user can double click on a name from the list on the left. There could be 75 or more names on this list.
When they double click on the name, the workbook will switch to the "Change History" tracking tab, and Add that name to the first cell available in the table under "Name"////// Tha'ts all.

The user will then just enter all the details of what changes were made. Nothing will happen, only a name drop in the first column

So just a simple double click and open other sheet and drop name as main list.
chris pikeOffice Admin

Author

Commented:
I think there is something wrong with the workbook I attached above.... Just delete it.
It keeps crashing.
CHANGE-HISTORY-CLIP.PNGI just need a simple table in the second sheet Called "Change History"
CHANGE-HISTORY-TAB-ONLY.xlsx
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What should the comment say?

Do you want the year in the table to change to 2020 next year, or perhaps 2019 - 2020?
chris pikeOffice Admin

Author

Commented:
NO nothing.... All will be manual thanks. The user will type in hi comment as needed and the date as well.
So I think that it is for this one.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In this workbook I
  • Named the table “tblHistory” in case you need to reference it
  • Eliminated the opening prompt
  • Added both name and date to the table on double-click in column 'B' of Sheet1
29166069.xlsm
chris pikeOffice Admin

Author

Commented:
Opps was I in the wrong thread sorry....My Bad
chris pikeOffice Admin

Author

Commented:
It take 15 seconds to fill in the code for 5 days
Let me try a lot of days to update
chris pikeOffice Admin

Author

Commented:
I created dates from 2016... its hanging...LOL
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I deleted the last 4 columns, closed and reopened the workbook and it updated almost instantaneously.
chris pikeOffice Admin

Author

Commented:
ok I will force close it , and re-open it again tomorrow, I am off work now heading home..... Talk soon.
Thanks for your help... We are almost there.      :)
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I deleted all the columns starting with columns 'I', with 'H' being 09-01-16. I then closed and reopened the workbook. It took 55 seconds to update.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's an update that does the same large number of columns in less than a second.
29166069a.xlsm
chris pikeOffice Admin

Author

Commented:
Something keeps happening with my settings after I open your sheet.
Simple      =sum(a1+b1)   does not update …….  no formulas are updating.
I went to Excel options >>>>  Proofing >>>> Autocorrect >>> then   Auto Format as You Type (all ticked)
But still not working.
Grrrrrrr
chris pikeOffice Admin

Author

Commented:
Is there a setting somewhere else that I am missing
chris pikeOffice Admin

Author

Commented:
Found it, by why would it turn off. Formula Tab>>>>> then calculation options>>> automatic
Why would it switch to manual?>\
Yah so weird . Every time I copy paste anything, the formatting switches to  manual.
chris pikeOffice Admin

Author

Commented:
I am here....been doing some ready.... yah a macro thing... yah changes the settings. even when you open a new workbook or other workbooks the setting still stays on manual.
That happened to a workbook I made for someone else, all their excel files stopped working.... I was in the dog house on that one.... lol
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Correcting...
chris pikeOffice Admin

Author

Commented:
silly question....what's the difference between
=SUM(E5+F5)
and
=SUM(E5:F5)
or just
=G5+G6

when adding up just 2 cells
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I apologize. A one-line oversight caused the problem. BTW I added a small macro called 'Clear' that deletes all but the first data column.
29166069b.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
what's the difference between
I don't know what goes on "behind the scenes" but they are all effectively the same. Obviosly however the second one is the most flexible.
chris pikeOffice Admin

Author

Commented:
Did we miss something... The double click on the name for change history??
Thanks Martin
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
No, it works (for me at least). This doesn't show the double-click very well.
2019-12-05_06-16-06.mp4
chris pikeOffice Admin

Author

Commented:
let me try again
chris pikeOffice Admin

Author

Commented:
Maybe when I copied and pasted the actual names into those cells (73 names)?
chris pikeOffice Admin

Author

Commented:
hmm weird. kinda hit and miss
Give me a sec.. I am adding all the names and all the dats into your sheet
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It should not matter how many names there are. I inserted one in the middle and them double-clicked the last name and it worked,
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Do you have the ability to modify your double-click speed?
chris pikeOffice Admin

Author

Commented:
So far so good. got the names in
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
At some point please answer my second question in this post above.
chris pikeOffice Admin

Author

Commented:
OK looks good..... still testing...back soon
Thanks
chris pikeOffice Admin

Author

Commented:
Just a super small one.....when it updates:
The first few rows are bold and then the rest are no bold.
It would like better if all bold...Thoughts?
Thanks so much.
bold.PNG
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Bold. I also change the way the heading on the Change History table is formatted so that it will stay centered if any column width is changed. And, again, please answer my question.
29166069c.xlsm
chris pikeOffice Admin

Author

Commented:
I am sorry, maybe I am a bit slow.... what is your question?
"What should the comment be?"  was that your question?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Do you want the year in the table to change to 2020 next year, or perhaps 2019 - 2020?
chris pikeOffice Admin

Author

Commented:
Another amazing job
Such a Huge help
Thanks Martin
chris pikeOffice Admin

Author

Commented:
No it's ok thank you very much...
It's ok
I can change it,,,,,
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thanks.

I can change it,,,,,
Of course you can but why not automatically? (I won't bug you about this again)

How would you like to be able to do this?
2019-12-05_14-07-13.mp4
chris pikeOffice Admin

Author

Commented:
I guess for the Initials we could have a small table with the manager names. And then do a dropdown of the managers name.

Perhaps we could make a simple range or table like this:
drop down listWe could put this to the right of the Change History table.
Thanks Martin
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Okay, in a new question I can do that, but what I was talking about was column 'E' on the history sheet and that says "Intls" which I assume means "Initials". Should it say "Manager" instead?
chris pikeOffice Admin

Author

Commented:
Either way is fine... I would be happy with whatever you would have done. But if I had to choose, if we are using first names in the drop down then I would say use "Manager" If It is a simple dropdown I can update the names in the future easily.
chris pikeOffice Admin

Author

Commented:
I am having trouble with this sheet. I am trying to start fresh with this sheet.  "F" which is the total available, which adds up every date column, needs to be brought up to date with 2019's numbers.

So what I mean is, There will be one date column... Today.... and the Total available will be what ever totals each person already has banked.

So if this sheet starts today, the people will already start with they have banked already. I need to copy and paste those values and then the new date columns will just add to those values.

New question coming.....
Thanks

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