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

asked on

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

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Which cell or cells are you adding the comment to and what does the comment say?
Avatar of chris pike

ASKER

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.
I think there is something wrong with the workbook I attached above.... Just delete it.
It keeps crashing.
User generated imageI just need a simple table in the second sheet Called "Change History"
CHANGE-HISTORY-TAB-ONLY.xlsx
What should the comment say?

Do you want the year in the table to change to 2020 next year, or perhaps 2019 - 2020?
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.
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
Opps was I in the wrong thread sorry....My Bad
It take 15 seconds to fill in the code for 5 days
Let me try a lot of days to update
I created dates from 2016... its hanging...LOL
I deleted the last 4 columns, closed and reopened the workbook and it updated almost instantaneously.
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.      :)
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.
Here's an update that does the same large number of columns in less than a second.
29166069a.xlsm
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
Is there a setting somewhere else that I am missing
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.
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
Correcting...
silly question....what's the difference between
=SUM(E5+F5)
and
=SUM(E5:F5)
or just
=G5+G6

when adding up just 2 cells
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
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.
Did we miss something... The double click on the name for change history??
Thanks Martin
No, it works (for me at least). This doesn't show the double-click very well.
2019-12-05_06-16-06.mp4
let me try again
Maybe when I copied and pasted the actual names into those cells (73 names)?
hmm weird. kinda hit and miss
Give me a sec.. I am adding all the names and all the dats into your sheet
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,
Do you have the ability to modify your double-click speed?
So far so good. got the names in
At some point please answer my second question in this post above.
OK looks good..... still testing...back soon
Thanks
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.
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am sorry, maybe I am a bit slow.... what is your question?
"What should the comment be?"  was that your question?
Do you want the year in the table to change to 2020 next year, or perhaps 2019 - 2020?
Another amazing job
Such a Huge help
Thanks Martin
No it's ok thank you very much...
It's ok
I can change it,,,,,
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
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:
User generated imageWe could put this to the right of the Change History table.
Thanks Martin
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?
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.
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