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.

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

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)

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:

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

GrahamSA

ASKER

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