Using a Vlookup when opening a new Form

kurtc

New Member
Joined
Sep 1, 2011
Messages
30
Morning all, I'm having some trouble with a Vlookup task i'm trying to complete.

i have a userform (form1) that has a combobox populated with a list of names, when one of those names is selected and the "Search" button is pressed, a new userform (form2) opens up with a number of different fields containing data related to that name (this is all kept in a hidden worksheet).

When doing this the first time it works perfectly, however there is a "Search again" button on form2 that takes you back to form1 to conduct another search. This also works fine, however if you select another name from the list and his "Search" again, it still displays the old information from the first search.

But i've also noticed if i hit "search again" on form2 again, dont change the name and click "search" in form1, it then does display the correct info for the new search in form2.

Any idea what may be causing the trouble? he is the code i am using in both forms

FORM1

Private Sub cmdSearch_Click()

If cmbDMC3.Value = "" Then
MsgBox "Unable to locate"

Else

dmclocator.Hide
DMClocatorresults.Show

End If

End Sub

Private Sub Cbclose_Click()

Me.cmbDMC3.Value = ""

dmclocator.Hide
Frontpage.Show

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then
Cancel = True
MsgBox "Please use the Back button", vbCritical
End If

End Sub

FORM2

Private Sub CBsearchagain_Click()

Me.txtDMCname.Value = ""
Me.txtCliadd.Value = ""
Me.txtCredadd.Value = ""
Me.txtClinum.Value = ""
Me.txtCrednum.Value = ""
Me.txtOpen.Value = ""
Me.txtDPA.Value = ""
Me.txtWebsite.Value = ""
Me.txtReg2.Value = ""
Me.txtCCL2.Value = ""
Me.txtGroup.Value = ""
Me.txtTrading.Value = ""
Me.txtComments3.Value = ""

Unload Me

DMClocatorresults.Hide
dmclocator.Show

End Sub

Private Sub UserForm_Initialize()

On Error Resume Next

txtDMCname = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 1, False)
txtCliadd = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 2, False)
txtCredadd = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 3, False)
txtClinum = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 4, False)
txtCrednum = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 5, False)
txtOpen = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 6, False)
txtDPA = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 7, False)
txtWebsite = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 8, False)
txtReg2 = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 9, False)
txtCCL2 = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 10, False)
txtGroup = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 11, False)
txtTrading = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 12, False)
txtComments3 = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 13, False)

Exit Sub


End Sub


Private Sub Cbclose_Click()

Me.txtDMCname.Value = ""
Me.txtCliadd.Value = ""
Me.txtCredadd.Value = ""
Me.txtClinum.Value = ""
Me.txtCrednum.Value = ""
Me.txtOpen.Value = ""
Me.txtDPA.Value = ""
Me.txtWebsite.Value = ""
Me.txtReg2.Value = ""
Me.txtCCL2.Value = ""
Me.txtGroup.Value = ""
Me.txtTrading.Value = ""
Me.txtComments3.Value = ""

DMClocatorresults.Hide
Frontpage.Show

End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top