I have a database that uses a userform (ufRepInfo)that finds and displays
specific data on the userform based on what zip code and market a user
chooses. The userform (ufRepInfo) has a find button (cbFindButton) that when
clicked uses the entery in the textbox (tbZipCode) and the entry in the
combobox (cbMarket) to fill out the rest of the textboxes on the userform.
My code works perfectly except when a user doesn't enter anything into
(tbZipCode) or (cbMarket) it creates a run-time error '1004'. What I would
like is that when nothing is entered in one or both of (tbZipCode and
cbMarket) that it brings up a new user form (ufErrorHander), which is already
created. Here is my code, thanks for any help.

Private Sub cbFindButton_Click()
'Find Rep Info

Dim ws As Worksheet

If tbZipCode.Value < 20000 Then
Set ws = Sheets("Zip Codes (00000-19999)")
ElseIf tbZipCode.Value < 40000 Then
Set ws = Sheets("Zip Codes (20000-39999)")
ElseIf tbZipCode.Value < 60000 Then
Set ws = Sheets("Zip Codes (40000-59999)")
ElseIf tbZipCode.Value < 80000 Then
Set ws = Sheets("Zip Codes (60000-79999)")
ElseIf tbZipCode.Value = 80000 Then
Set ws = Sheets("Zip Codes (80000-99999)")
End If
With ws

Select Case cbMarket
Case "Industrial Drives"
cbMarketCol = 13
Case "Municipal Drives (W&E)"
cbMarketCol = 14
Case "Electric Utility"
cbMarketCol = 15
Case "Oil and Gas"
cbMarketCol = 16
End Select
RowCount = 1
Do While .Range("A" & RowCount) < ""
If .Range("A" & RowCount) = Val(tbZipCode.Value) And _
.Cells(RowCount, cbMarketCol) < "" Then

Set Rep = .Range("A" & RowCount)
tbRepNumber.Value = Rep.Offset(0, 1).Value
tbSAPNumber.Value = Rep.Offset(0, 2).Value
tbRepName.Value = Rep.Offset(0, 3).Value
tbRepAddress.Value = Rep.Offset(0, 4).Value
tbRepCity.Value = Rep.Offset(0, 5).Value
tbRepState.Value = Rep.Offset(0, 6).Value
tbRepZipCode.Value = Rep.Offset(0, 7).Value
tbRepBusPhone.Value = Rep.Offset(0, 8).Value
tbRepFax.Value = Rep.Offset(0, 9).Value
tbRepEmail.Value = Rep.Offset(0, 10).Value
tbRegion.Value = Rep.Offset(0, 11).Value
If Rep.Offset(0, 12).Value = "x" Then cbIndustrialDrives = True
If Rep.Offset(0, 13).Value = "x" Then cbMunicipalDrives = True
If Rep.Offset(0, 14).Value = "x" Then cbElectricUtility = True
If Rep.Offset(0, 15).Value = "x" Then cbOilGas = True
If Rep.Offset(0, 16).Value = "x" Then cbMediumVoltage = True
If Rep.Offset(0, 17).Value = "x" Then cbLowVoltage = True
If Rep.Offset(0, 18).Value = "x" Then cbAfterMarket = True
tbInclusions.Value = Rep.Offset(0, 19).Value
tbExclusions.Value = Rep.Offset(0, 20).Value
End If
RowCount = RowCount + 1
Loop
End With
End Sub

Try these changes

Private Sub cbFindButton_Click()
'Find Rep Info

Dim ws As Worksheet

if Val(tbZipCode.Value) < 10000 or _
Val(tbZipCode.Value) 99999 then

msgbox("Bad Zip code - Exiting Macro")
exit sub
end if
If tbZipCode.Value < 20000 Then
Set ws = Sheets("Zip Codes (00000-19999)")
ElseIf tbZipCode.Value < 40000 Then
Set ws = Sheets("Zip Codes (20000-39999)")
ElseIf tbZipCode.Value < 60000 Then
Set ws = Sheets("Zip Codes (40000-59999)")
ElseIf tbZipCode.Value < 80000 Then
Set ws = Sheets("Zip Codes (60000-79999)")
ElseIf tbZipCode.Value = 80000 Then
Set ws = Sheets("Zip Codes (80000-99999)")
End If
With ws

Select Case cbMarket
Case "Industrial Drives"
cbMarketCol = 13
Case "Municipal Drives (W&E)"
cbMarketCol = 14
Case "Electric Utility"
cbMarketCol = 15
Case "Oil and Gas"
cbMarketCol = 16
case else msgbox("No Market - Exiting Macro")
exit sub
End Select
RowCount = 1
Do While .Range("A" & RowCount) < ""
If .Range("A" & RowCount) = Val(tbZipCode.Value) And _
.Cells(RowCount, cbMarketCol) < "" Then

Set Rep = .Range("A" & RowCount)
tbRepNumber.Value = Rep.Offset(0, 1).Value
tbSAPNumber.Value = Rep.Offset(0, 2).Value
tbRepName.Value = Rep.Offset(0, 3).Value
tbRepAddress.Value = Rep.Offset(0, 4).Value
tbRepCity.Value = Rep.Offset(0, 5).Value
tbRepState.Value = Rep.Offset(0, 6).Value
tbRepZipCode.Value = Rep.Offset(0, 7).Value
tbRepBusPhone.Value = Rep.Offset(0, 8).Value
tbRepFax.Value = Rep.Offset(0, 9).Value
tbRepEmail.Value = Rep.Offset(0, 10).Value
tbRegion.Value = Rep.Offset(0, 11).Value
If Rep.Offset(0, 12).Value = "x" Then cbIndustrialDrives = True
If Rep.Offset(0, 13).Value = "x" Then cbMunicipalDrives = True
If Rep.Offset(0, 14).Value = "x" Then cbElectricUtility = True
If Rep.Offset(0, 15).Value = "x" Then cbOilGas = True
If Rep.Offset(0, 16).Value = "x" Then cbMediumVoltage = True
If Rep.Offset(0, 17).Value = "x" Then cbLowVoltage = True
If Rep.Offset(0, 18).Value = "x" Then cbAfterMarket = True
tbInclusions.Value = Rep.Offset(0, 19).Value
tbExclusions.Value = Rep.Offset(0, 20).Value
End If
RowCount = RowCount + 1
Loop
End With
End Sub

"aintlifegrand79" wrote:

I have a database that uses a userform (ufRepInfo)that finds and displays
specific data on the userform based on what zip code and market a user
chooses. The userform (ufRepInfo) has a find button (cbFindButton) that when
clicked uses the entery in the textbox (tbZipCode) and the entry in the
combobox (cbMarket) to fill out the rest of the textboxes on the userform.
My code works perfectly except when a user doesn't enter anything into
(tbZipCode) or (cbMarket) it creates a run-time error '1004'. What I would
like is that when nothing is entered in one or both of (tbZipCode and
cbMarket) that it brings up a new user form (ufErrorHander), which is already
created. Here is my code, thanks for any help.

Private Sub cbFindButton_Click()
'Find Rep Info

Dim ws As Worksheet

If tbZipCode.Value < 20000 Then
Set ws = Sheets("Zip Codes (00000-19999)")
ElseIf tbZipCode.Value < 40000 Then
Set ws = Sheets("Zip Codes (20000-39999)")
ElseIf tbZipCode.Value < 60000 Then
Set ws = Sheets("Zip Codes (40000-59999)")
ElseIf tbZipCode.Value < 80000 Then
Set ws = Sheets("Zip Codes (60000-79999)")
ElseIf tbZipCode.Value = 80000 Then
Set ws = Sheets("Zip Codes (80000-99999)")
End If
With ws

Select Case cbMarket
Case "Industrial Drives"
cbMarketCol = 13
Case "Municipal Drives (W&E)"
cbMarketCol = 14
Case "Electric Utility"
cbMarketCol = 15
Case "Oil and Gas"
cbMarketCol = 16
End Select
RowCount = 1
Do While .Range("A" & RowCount) < ""
If .Range("A" & RowCount) = Val(tbZipCode.Value) And _
.Cells(RowCount, cbMarketCol) < "" Then

Set Rep = .Range("A" & RowCount)
tbRepNumber.Value = Rep.Offset(0, 1).Value
tbSAPNumber.Value = Rep.Offset(0, 2).Value
tbRepName.Value = Rep.Offset(0, 3).Value
tbRepAddress.Value = Rep.Offset(0, 4).Value
tbRepCity.Value = Rep.Offset(0, 5).Value
tbRepState.Value = Rep.Offset(0, 6).Value
tbRepZipCode.Value = Rep.Offset(0, 7).Value
tbRepBusPhone.Value = Rep.Offset(0, 8).Value
tbRepFax.Value = Rep.Offset(0, 9).Value
tbRepEmail.Value = Rep.Offset(0, 10).Value
tbRegion.Value = Rep.Offset(0, 11).Value
If Rep.Offset(0, 12).Value = "x" Then cbIndustrialDrives = True
If Rep.Offset(0, 13).Value = "x" Then cbMunicipalDrives = True
If Rep.Offset(0, 14).Value = "x" Then cbElectricUtility = True
If Rep.Offset(0, 15).Value = "x" Then cbOilGas = True
If Rep.Offset(0, 16).Value = "x" Then cbMediumVoltage = True
If Rep.Offset(0, 17).Value = "x" Then cbLowVoltage = True
If Rep.Offset(0, 18).Value = "x" Then cbAfterMarket = True
tbInclusions.Value = Rep.Offset(0, 19).Value
tbExclusions.Value = Rep.Offset(0, 20).Value
End If
RowCount = RowCount + 1
Loop
End With
End Sub