Error help
 

I have a userform (ufRepInfo) which finds data and displays it on the
userform in a bunch of textboxes , a user enters itheir desired Zip Code and
Market into the textbox (tbZipCode) and the combobox (cbMarket) and clicks
the find button (cbFindButton). My code worked perfectly till I added a
min/max button to my userform. Now I am getting an error that reads: compile
error - variable not defined (this is at the line "cbMarketCol = 13" in my
code and the line "Private Sub cbFindButton_Click()" is also highlighted in
yellow). What am I doing wrong? Here is my code. I added a module that
looks like this for the min/max button with my code for ufRepInfo after it:

Private Declare Function FindWindowA Lib "USER32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLongA Lib "USER32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLongA Lib "USER32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Option Explicit

Sub FormatUserForm(UserFormCaption As String)

Dim hWnd As Long
Dim exLong As Long

hWnd = FindWindowA(vbNullString, UserFormCaption)
exLong = GetWindowLongA(hWnd, -16)
If (exLong And &H20000) = 0 Then
SetWindowLongA hWnd, -16, exLong Or &H20000
Else
End If
End Sub

Sub ShowForm()

ufRepInfo.Show

End Sub


AND MY CODE FOR ufRepInfo is

Option Explicit

Private Sub ufRepInfo_Initialize()
Call FormatUserForm(Me.Caption)
With cbMarket
.AddItem "Industrial Drives"
.AddItem "Municipal Drives (W&E)"
.AddItem "Electric Utility"
.AddItem "Oil and Gas"
End With
End Sub

Private Sub cbFindButton_Click()
'Find Rep Info

Dim ws As Worksheet

If Val(tbZipCode.Value) < 0 Or _
Val(tbZipCode.Value) 99999 Then

MsgBox ("Please enter a Zip Code")
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 ("Please enter a Market")
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

Error help
 

You have Option Explicit in your code which means every variable must be
delared.
cbMarketCol is a variable so you need this statement

Dim cbMarketCol as Integer

"aintlifegrand79" wrote:

I have a userform (ufRepInfo) which finds data and displays it on the
userform in a bunch of textboxes , a user enters itheir desired Zip Code and
Market into the textbox (tbZipCode) and the combobox (cbMarket) and clicks
the find button (cbFindButton). My code worked perfectly till I added a
min/max button to my userform. Now I am getting an error that reads: compile
error - variable not defined (this is at the line "cbMarketCol = 13" in my
code and the line "Private Sub cbFindButton_Click()" is also highlighted in
yellow). What am I doing wrong? Here is my code. I added a module that
looks like this for the min/max button with my code for ufRepInfo after it:

Private Declare Function FindWindowA Lib "USER32" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLongA Lib "USER32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLongA Lib "USER32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Option Explicit

Sub FormatUserForm(UserFormCaption As String)

Dim hWnd As Long
Dim exLong As Long

hWnd = FindWindowA(vbNullString, UserFormCaption)
exLong = GetWindowLongA(hWnd, -16)
If (exLong And &H20000) = 0 Then
SetWindowLongA hWnd, -16, exLong Or &H20000
Else
End If
End Sub

Sub ShowForm()

ufRepInfo.Show

End Sub


AND MY CODE FOR ufRepInfo is

Option Explicit

Private Sub ufRepInfo_Initialize()
Call FormatUserForm(Me.Caption)
With cbMarket
.AddItem "Industrial Drives"
.AddItem "Municipal Drives (W&E)"
.AddItem "Electric Utility"
.AddItem "Oil and Gas"
End With
End Sub

Private Sub cbFindButton_Click()
'Find Rep Info

Dim ws As Worksheet

If Val(tbZipCode.Value) < 0 Or _
Val(tbZipCode.Value) 99999 Then

MsgBox ("Please enter a Zip Code")
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 ("Please enter a Market")
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