Solved: Using Vlookup On UserForm

[sooty8]

Hi All

I have created a mock up UserForm which is attached - the problem I have is that the two text boxes that display the Site Info are restricted to Site 1 any suggestions on how I would be able to select any of the other sites 2 & 3 when necessary would be much appreciated.

Regards

Sooty8

[Bob Phillips]

Not sure I understand exactly what you mean/want.

[mancubus]

with the uf's current design, afaik, you can't.
maybe you should add option buttons. or check boxes for multiple site values.

column references are to 5th and 6th columns in B2:K20.
[VBA]Tb5.Value = Application.VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 5, False)
Tb6.Value = Application.VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 6, False)[/VBA]

[sooty8]

Hi Guys

Have done a change on the userform and suggested a drop down to select which site to use - if you open the new form its all explained hopefully on there.

Regards

Sooty8

[Tinbendr]

You could add a spin button. (See attachment)

If the data was recognizable, you could put three option buttons in a frame and choose the one you wanted.

Also, make your Rowsource range a dynamic named range. This way you can keep adding data without having to adjust the rowsource in the userform.

Oh, and I changed the name of your userform to ufStart as Start is a Reserve word in VBA. (Possible conflicts) You should develop some naming convention standards for your code. I use uf for Userforms, cbox for Combobox, lbox for Listbox, txt for Textboxes, etc, plus the name of the field. For example, if you name your field txtTitle, then 6 months from now when you look at it, you will instantly know what that refers to, but you wouldn't remember what TB1 refers to. It's a little extra work on the front end, but it'll pay off down the road.

[Tinbendr]

Here's a option button version.

Also, something else to consider. If you are only using this userform to view records for copy to another sheet, use labels instead of textboxes to prevent inadvertently adding/deleting to the fields of the record.

David

[sooty8]

Hi David

I prefer the spin button - can I ask a couple of questions is it possible to have a text box next to the spin button that on using the spin button and selecting a site the text box would display the site number as per the heading on sheet3 and also a check box that would lock it until I had finished with that site. The reason for this is there are 22 sites and each one has a minimum of 400 rows.

Many Thanks for your help

Regards

Sooty8

[Tinbendr]

This will error if you go past Site 3 since the vlookup range isn't defined past that.

David

[sooty8]

Hi David

Been in & out of meetings - sort of twigged the ranges and had a go at reassigning them - will have to be tomorrow early before I can do anything and after 10:00am I'm out most of the day will get back to you late afternoon if I miss the morning call.

Thanks for the help

Regards

Sooty8

[sooty8]

Hi David

I have reset the column and row ranges and it is working OK - a problem at the moment is that even when locking the Site Info at Site3 and then changing Com3 to a new name it defaults each time to Site 1 is there a way to solve this.

Regards

Sooty 8

[Tinbendr]

I'm not exactly sure what 'lock Site 3' means; for the current record... see below.
[vba]Private Sub Com1_Change()
With Application.WorksheetFunction
Tb1.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 2, False)
Tb2.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 1, False)
Tb3.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 3, False)
Tb4.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 4, False)
If Not Me.CheckBox1 Then
Tb5.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 5, False)
Tb6.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 6, False)
ufStart.lblSiteInfo.Caption = "Site info " & SpinButton1.Value
End If
End With
End Sub[/vba]

For another record (row), and use that record site 3 information? So if I had selected Bloggs, then Site 3, and I changed to Smith, you want the info for Smith-Site 3?
[VBA]Private Sub Com1_Change()
With Application.WorksheetFunction
Tb1.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 2, False)
Tb2.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 1, False)
Tb3.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 3, False)
Tb4.Value = .VLookup(Com1.Value, Worksheets("Sites").Range("B2:K10"), 4, False)
SiteInfo SpinButton1.Value
ufStart.lblSiteInfo.Caption = "Site info " & SpinButton1.Value
End With
End Sub[/VBA]

[sooty8]

Hi David

Thanks for your help - now does exactly what I wanted

Regards

Sooty8