GrahamSA
asked on
InputBox to populate a list (Simple)
Hi Guys!
OK, I have gotten this far...
Super happy that it opens when the workbook opens and asks the right question.
Now for the fun part. on Sheet3 I have a two field list.
The list is named WeightList and has two columns (Date and Weight)
Now. The input box must get the date and populate the next line in the WeightList then it must ask for the weight and put that in the second field.
Then close for now.
Thanks for any help.
OK, I have gotten this far...
Option Explicit
Private Sub Workbook_Open()
Dim cellvalue As Variant
ReShowInputBox:
cellvalue = Application.InputBox _
("Enter the Todays Date (dd/mm/yyyy)")
End Sub
Super happy that it opens when the workbook opens and asks the right question.
Now for the fun part. on Sheet3 I have a two field list.
The list is named WeightList and has two columns (Date and Weight)
Now. The input box must get the date and populate the next line in the WeightList then it must ask for the weight and put that in the second field.
Then close for now.
Thanks for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Whoops.
I had to format the date to ensure the output was formatted as DD/MM/YYYY as well as Column A.
line 27:
See attached.
Dave
populateList-r2.xls
I had to format the date to ensure the output was formatted as DD/MM/YYYY as well as Column A.
line 27:
r.Value = Format(getDate,"DD/MM/YYYY")
See attached.
Dave
populateList-r2.xls
ASKER
Still getting the same result
my cells are formatted *14/03/2001
my cells are formatted *14/03/2001
Did you try entering a date of 08/08/2012? Did that populate correctly?
What do you mean "my cells are formatted *14/03/2001"? Did you change the formatting?
I entered 14/03/2001 by the way and it populated exactly thus. Are you running the file I uploaded?
Dave
What do you mean "my cells are formatted *14/03/2001"? Did you change the formatting?
I entered 14/03/2001 by the way and it populated exactly thus. Are you running the file I uploaded?
Dave
ASKER
08/08 works fine and 14/03 works fine but 08/07 and 01/07 switch in the cell.
The cell formatting in excel. (right click format cells)
Yes your file
The cell formatting in excel. (right click format cells)
Yes your file
They don't switch for me. This may mean we're working with different regional settings between my Windows setup and yours.
Now, I've forced the formatting to be DD/MM/YYYY, and the formatting of the cells in column A are formatted to DD/MM/YYYY. Can you look at cell A3's formatting and let me know what the formatting is for you?
Right-Click cell A3->Format cells. It should show up as custom formatting with the TYPE: dd/mm/yyyy.
What do you see?
Dave
Now, I've forced the formatting to be DD/MM/YYYY, and the formatting of the cells in column A are formatted to DD/MM/YYYY. Can you look at cell A3's formatting and let me know what the formatting is for you?
Right-Click cell A3->Format cells. It should show up as custom formatting with the TYPE: dd/mm/yyyy.
What do you see?
Dave
What version of Windows are you running?
If Vista/Windows 7/8 you should be able to go to the Control Panel, then click on something like "Click, Language, and Region", then "Regional and Language Options"
Under Regional and Language Optins - what is your current format? Mine is English(United States).
Dave
If Vista/Windows 7/8 you should be able to go to the Control Panel, then click on something like "Click, Language, and Region", then "Regional and Language Options"
Under Regional and Language Optins - what is your current format? Mine is English(United States).
Dave
Independent of regional settings, I've modified the code to take whatever you've typed and place it in the cell, with no formatting. That way, what you see is what you get, independent of how it is typed in or regional settings of the user.
Without knowing what your settings are and matching mine to yours, I'm only guessing, so this approach what you see is what you get, with no guessing. Let's see if this serves.
Please try this.
Dave
populateList-r3.xls
Without knowing what your settings are and matching mine to yours, I'm only guessing, so this approach what you see is what you get, with no guessing. Let's see if this serves.
Please try this.
Dave
populateList-r3.xls
ASKER
WOW
The r3 file is exactly the same
Setting are English US format dd/MM/yyyy
Still mixing up the 08 and 07
Wow I am going to play around with my settings (Hope that I don't kill anything)
The r3 file is exactly the same
Setting are English US format dd/MM/yyyy
Still mixing up the 08 and 07
Wow I am going to play around with my settings (Hope that I don't kill anything)
Not sure why that is. I changed the column A format to general and am not formatting the output.... Hmmmm....
My settings are English US format and M/D/YYYY
Are all your users going to have the same settings as yours? If so, I can change my settings to yours to make it work.
Dave
My settings are English US format and M/D/YYYY
Are all your users going to have the same settings as yours? If so, I can change my settings to yours to make it work.
Dave
ASKER
This app no, its just me trying to learn, but there are 6 users in my company that have the exact same settings as me and we have a whole bunch of Access, SQL and Excel stuff going on.
I tried this DD/MMM/YYYY in your code and its perfect for this app.
I looks like our settings are the same, but before tonight my were on South Africa English.
I remember having issues with dates in the past until one day, everything worked perfectly without a tangible solution.
My settings look like this:
I have tied formatting the cells to general and get a number that does not really look like a date.
Thanks so much for your help (I am Happy to use the MMM format for this one)
I tried this DD/MMM/YYYY in your code and its perfect for this app.
I looks like our settings are the same, but before tonight my were on South Africa English.
I remember having issues with dates in the past until one day, everything worked perfectly without a tangible solution.
My settings look like this:
I have tied formatting the cells to general and get a number that does not really look like a date.
Thanks so much for your help (I am Happy to use the MMM format for this one)
So, is it now working?
PS - You can comment out the messagebox, then after inputting the data, the workbook automatically closes.
Cheers,
Dave
PS - You can comment out the messagebox, then after inputting the data, the workbook automatically closes.
Cheers,
Dave
ASKER
Thanks Friend for being a great teacher and going the extra mile...
ASKER
Little thing that popped up when testing on my file and then yours.
When Changed the date from 07/07/2102 to 08/07/2012 the cell gets populated with 07/08/2012
Somehow DD/MM/YYYY is being converted to MM/DD/YYYY ?