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
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