View Full Version : Solved: Using Vlookup On UserForm
sooty8
01-25-2011, 01:35 AM
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
01-25-2011, 02:32 AM
Not sure I understand exactly what you mean/want.
mancubus
01-25-2011, 02:58 AM
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.
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)
sooty8
01-25-2011, 03:07 AM
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
01-25-2011, 03:19 AM
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 (http://www.contextures.com/xlnames01.html). 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
01-25-2011, 03:49 AM
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
01-25-2011, 05:23 AM
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
01-25-2011, 08:12 AM
This will error if you go past Site 3 since the vlookup range isn't defined past that.
David
sooty8
01-25-2011, 12:16 PM
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
01-26-2011, 02:06 AM
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
01-26-2011, 06:41 AM
I'm not exactly sure what 'lock Site 3' means; for the current record... see below.
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
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?
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
sooty8
01-26-2011, 10:13 AM
Hi David
Thanks for your help - now does exactly what I wanted
Regards
Sooty8
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.