Open a excel userform with values previously entered by user using VBA

**anuraag1** · 01-17-2013, 02:33 AM

I have an excel userform.On filling the values and clicking save , the textbox values are saved to corresponding columns in a excel sheet.On clciking the Open button i want the userform to open with the previously filled values without accessing the excel sheet.I'am a newbie and have no clue about this..Kindly help.Thanks in advance

**:) Sixthsense :)** · 01-17-2013, 02:50 AM

It seems that you require some code which should run on Initialization of the Userform and the controls needs to load the last entry from the worksheet.

Please attach the workbook for providing the code.

**anuraag1** · 01-17-2013, 03:03 AM

this is the form id like to work on...refer sheet 'dd' please[also once the save is clicked,the excel sheet is read only]

**:) Sixthsense :)** · 01-17-2013, 05:40 AM

I seen your file and it seems to be lot of conditions needs to be reversed.

Better on initialize event do something like this...

Please Login or Register  to view this content.

**anuraag1** · 01-17-2013, 06:38 AM

works fine...thank you..but here's the problem .i'm storing the list index of the country combo box in the excel sheet,however i need the name of the country on reopening the form in the userform..Then i need the state combobox to populate based on the country chosen.. i do not want the
If country = "United States" Then
cmbState1.List() = Sheet2.Range("K1:K92").Value
End If
sisnce there are over 200 countries i need an effect and not so lengthy code
...Kindly help sixth sense..

**:) Sixthsense :)** · 01-17-2013, 06:44 AM

ha ha I am aware of this issue

that's why I mentioned

lot of conditions needs to be reversed

in my earlier post

you have to use your select case statement in reverse to identify the Country Name using the code no.

or

Keep a base data in a separate sheet (both # and Country Name) and use Vlookup function in your code and get the Value.

**anuraag1** · 01-17-2013, 06:49 AM

but everything involves access to sheet..is there absolutely no way to lock the fields in the form once the OK is pressed and reopen the form with them..

**:) Sixthsense :)** · 01-17-2013, 07:02 AM

1) On Ok Click after Data Transfer you SHOULD NOT CLEAR the fields
2) You should not Exit the UserForm instead just HIDE it.
3) Even you have done the above the userform will not keep the last entry data when the file is closed and opened. So it requires the Last entry to be feeded into each control on FORM INITIALISATION