Bypass entry of data from Lookup Table
 

On an Entry form (Userform) I'm trying to allow for the Entry of
new items MANUALLY that are not in a pre-defined lookup table.
My line 3 and 4 below is a poor attempt at doing so <<IS NOT WORKING; Can
someone
suggest the code to allow me to enter all 7 fields manually?
TIA,


Private Sub cbVenCode_AfterUpdate()
Set MyVenTable = Range("VenTable")
If Application.VLookup(Me.cbVencode, MyVenTable, 2, False) = "" Then
Exit Sub
End If
txtVenName.Value = Application.VLookup(Me.cbVencode, MyVenTable, 2,
False)
txtVAdd1.Value = Application.VLookup(Me.cbVencode, MyVenTable, 3, False)
txtVAdd2.Value = Application.VLookup(Me.cbVencode, MyVenTable, 4, False)
txtCity.Value = Application.VLookup(Me.cbVencode, MyVenTable, 5, False)
txtSt.Value = Application.VLookup(Me.cbVencode, MyVenTable, 6, False)
txtZip.Value = Application.VLookup(Me.cbVencode, MyVenTable, 7, False)
Set MyVenTable = Nothing
End Sub

Bypass entry of data from Lookup Table
 

if iserror(application.match(me.cbvencode,myventable. columns(1),0)) then
'allow manual entry
'make the textboxes enabled?
else
'just show the values
'and disable the textboxes so no changes can be made?
end if

Maybe....

Jim May wrote:

On an Entry form (Userform) I'm trying to allow for the Entry of
new items MANUALLY that are not in a pre-defined lookup table.
My line 3 and 4 below is a poor attempt at doing so <<IS NOT WORKING; Can
someone
suggest the code to allow me to enter all 7 fields manually?
TIA,

Private Sub cbVenCode_AfterUpdate()
Set MyVenTable = Range("VenTable")
If Application.VLookup(Me.cbVencode, MyVenTable, 2, False) = "" Then
Exit Sub
End If
txtVenName.Value = Application.VLookup(Me.cbVencode, MyVenTable, 2,
False)
txtVAdd1.Value = Application.VLookup(Me.cbVencode, MyVenTable, 3, False)
txtVAdd2.Value = Application.VLookup(Me.cbVencode, MyVenTable, 4, False)
txtCity.Value = Application.VLookup(Me.cbVencode, MyVenTable, 5, False)
txtSt.Value = Application.VLookup(Me.cbVencode, MyVenTable, 6, False)
txtZip.Value = Application.VLookup(Me.cbVencode, MyVenTable, 7, False)
Set MyVenTable = Nothing
End Sub

--

Dave Peterson

Bypass entry of data from Lookup Table
 

Thanks Dave;
I got it going, as I wanted it.
Jim


"Dave Peterson" wrote in message
...
if iserror(application.match(me.cbvencode,myventable. columns(1),0)) then
'allow manual entry
'make the textboxes enabled?
else
'just show the values
'and disable the textboxes so no changes can be made?
end if

Maybe....

Jim May wrote:

On an Entry form (Userform) I'm trying to allow for the Entry of
new items MANUALLY that are not in a pre-defined lookup table.
My line 3 and 4 below is a poor attempt at doing so <<IS NOT WORKING;
Can
someone
suggest the code to allow me to enter all 7 fields manually?
TIA,

Private Sub cbVenCode_AfterUpdate()
Set MyVenTable = Range("VenTable")
If Application.VLookup(Me.cbVencode, MyVenTable, 2, False) = "" Then
Exit Sub
End If
txtVenName.Value = Application.VLookup(Me.cbVencode, MyVenTable, 2,
False)
txtVAdd1.Value = Application.VLookup(Me.cbVencode, MyVenTable, 3,
False)
txtVAdd2.Value = Application.VLookup(Me.cbVencode, MyVenTable, 4,
False)
txtCity.Value = Application.VLookup(Me.cbVencode, MyVenTable, 5,
False)
txtSt.Value = Application.VLookup(Me.cbVencode, MyVenTable, 6, False)
txtZip.Value = Application.VLookup(Me.cbVencode, MyVenTable, 7,
False)
Set MyVenTable = Nothing
End Sub

--

Dave Peterson