help with error

  • Thread starter aintlifegrand79
  • Start date
A

aintlifegrand79

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
 
J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top