Userform Load Questions

**RobertCo** · 12-19-2006, 11:42 AM

Hello again,

I am in the process of creating a userform which I would like to have populated once a User makes their selection.

I have the form created and have some of the functionality working but I am having a problem understanding how I can populate the remaining Fields on the form using a lookup.

The basic flow of the form should be as follows.

User opens Excel file (form opens with no options)
....(currently I am autopopulating the form with the IP Column)

User selects one of the 4 OptionButtons to determine what list will populate
ListBox1
Once ListBox1 is populated User will then select from that listbox and the rest of the form will be popluated with the corresponding data elements.

My thoughts on how to do this were to initialize form
Once the option has been chosen and the user clicks on the selection,
a Macro to do a vlookup would fill the remaining fields.
1. Is vlookup the correct choice for this process or is there a better method ?
2. How would Vlookup pass the variables to the Form textbox when the Textbox does not know the position in the datasheet where the original variable was loaded from ? This is what is confusing me.

TextBox1.Text = (vlookup(ListBox1.Value,A:I,2)) ???

I have attached a sample of what I am trying to accomplish
Thanks again for all your help, this is really a great learning process for me.

**leonnaley** · 12-19-2006, 03:06 PM

Hey, I can't really understand what you want, but this spreadsheet might do the trick

. I've used =Vlookup to find the data you wanted to look up.

**RobertCo** · 12-19-2006, 04:58 PM

Hello Leonnaley,
Thanks for the response, I am sorry but my attachement did not have the Userform autoloading at the time I originally posted my question.
I am aware of how to make vlookup perform the tasks of populating cells within a spreadsheet.
I am not sure how to go about using vlookup Or some other method of populating the fields in the UserForm

I have modified the Sample spreadsheet so you can see the userform and fields.
any help would be great.

Thanks again.

**leonnaley** · 12-20-2006, 11:30 AM

Oh, well I seem to have misunderstood and underestimated you greatly.. I doubt I can help you at all.. I don't even know how to make the thingy you have made :P just started with the macro part of excel.

**Alfaholic** · 12-20-2006, 01:02 PM

Hi,

You could use either find or a For...Next loop to find the selected data. Let's say you selected IP Address, then the listbox was populated with all the IP addresses.

You could use a Find statement in the macro which finds that IP in column A, then makes that cell active. Then, all you need to to do populate the textboxes is say something like this:

textbox1.text = ActiveCell.Offset(0, 1).Value
textbox2.text = ActiveCell.Offset(0, 2).Value
etc

This assumes of course that your find procedure has found and selected a cell in column A which has the selected IP address. You could also do it in a for... next loop.

Please note for this routine that countcells is the number of rows containing records. You would add a routine before the for... next loop to determine the value of CountCells. Basically, if you have 640 rows with data in, you want CountCells to be 640.

Range("A1").select

For n = 1 To CountCells
If ActiveCell.Offset(n, 0).Value = listbox1.text Then
textbox1.text = ActiveCell.Offset(n, 1).Value
textbox2.text = ActiveCell.Offset(n, 2).Value
textbox3.text = .... etc
End If
Next n

This looks at the cell in A2. If the IP in A2 matches the IP selected in Listbox1, then textbox1 gets the value from column B of the same row (the Type value), textbox2 gets the value from column C of the same row (the Name column) etc. Then it looks at the IP in cell A3, then in A4 and so on until it gets to the end of the CountCells number.

Hope something in this does the trick and that I have not misunderstood the question.

'holic

**peejay** · 12-21-2006, 04:20 PM

hi Robert
see file attached, is this what your looking for?
i have not deleted any of your code, just commented it out and made some alterations as noted.
the listbox is now filled based on the option select (and emptied of the old list items if the optionis changed)
i have added a macro, just to enable the form to be loaded using ctrlZ to save time whilst de-bugging (delete that macro when your happy)

HTH