Avatar of GrahamSA
GrahamSA
Flag for South Africa asked on

InputBox to populate a list (Simple)

Hi Guys!

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

Open in new window


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.
Microsoft Excel

Avatar of undefined
Last Comment
GrahamSA

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
dlmille

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
GrahamSA

ASKER
Wow what a Legend!

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 ?
dlmille

Whoops.

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")

Open in new window


See attached.

Dave
populateList-r2.xls
GrahamSA

ASKER
Still getting the same result
my cells are formatted *14/03/2001
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
dlmille

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
GrahamSA

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
dlmille

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

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
dlmille

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
GrahamSA

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)
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
dlmille

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
GrahamSA

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:
Settings
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)
dlmille

So, is it now working?

PS - You can comment out the messagebox, then after inputting the data, the workbook automatically closes.

Cheers,

Dave
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GrahamSA

ASKER
Thanks Friend for being a great teacher and going the extra mile...