D
davegb
I've written this code to run a userform and capture a range for the
user to identify before the code turns over to some module code. But
the variable "rExtrFromStrt" doesn't carry over to the other module.
I"m getting a "variable not defined" message in the next module. Any
suggestions?
Option Explicit
Public rColStart As Range
Public rExtrFromStrt As Range
Public rExtrFromEnd As Range
Public lLastCol As Long
Public lLastRow As Long
Public lUsrSelectRow As Long
Public s1stCtyName As String
'Public bHdr As Boolean
Private Sub btnCancel_Click()
End
End Sub
Private Sub btnTop21BOS_Click()
End Sub
Private Sub CheckBox1_Click()
bHdr = True
End Sub
Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long
If btnTop10BOS Then lTop = 10
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3
If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If
If reDataStrt = "" Then
MsgBox "Please select the range where the first county, " _
& "Adams, data is located."
Exit Sub
End If
Set uf1021Mid.rColStart = Range(reDataStrt.Text)
Set rFndCell = uf1021Mid.rColStart.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If
s1stCtyName = rFndCell.Value
'If UCase(s1stCtyName) <> "ADAMS" Then
If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True
End If
End If
With uf1021Mid.rColStart
lLastCol = .Columns(.Columns.Count).Column
End With
Set rExtrFromStrt = uf1021Mid.rColStart
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If
rExtrFromStrt.Select
If cbHdr = True Then
'MsgBox "true"
bHdr = True
End If
uf1021Mid.Hide
user to identify before the code turns over to some module code. But
the variable "rExtrFromStrt" doesn't carry over to the other module.
I"m getting a "variable not defined" message in the next module. Any
suggestions?
Option Explicit
Public rColStart As Range
Public rExtrFromStrt As Range
Public rExtrFromEnd As Range
Public lLastCol As Long
Public lLastRow As Long
Public lUsrSelectRow As Long
Public s1stCtyName As String
'Public bHdr As Boolean
Private Sub btnCancel_Click()
End
End Sub
Private Sub btnTop21BOS_Click()
End Sub
Private Sub CheckBox1_Click()
bHdr = True
End Sub
Sub OKButton_Click()
Dim rFndCell As Range
Dim lStrDif As Long
If btnTop10BOS Then lTop = 10
If btnTop21BOS Then lTop = 21
If btnTop10MidBOS Then lTop = 3
If lTop = 0 Then
MsgBox "Please select the type of extraction (i.e., Top 10, BOS)
you want."
Exit Sub
End If
If reDataStrt = "" Then
MsgBox "Please select the range where the first county, " _
& "Adams, data is located."
Exit Sub
End If
Set uf1021Mid.rColStart = Range(reDataStrt.Text)
Set rFndCell = uf1021Mid.rColStart.Rows(1).Find(What:="Adams", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If rFndCell Is Nothing Then
MsgBox "The first row of data should include Adams County. " _
& "Please select the correct row."
Exit Sub
End If
s1stCtyName = rFndCell.Value
'If UCase(s1stCtyName) <> "ADAMS" Then
If UCase(s1stCtyName) Like "*ADAMS" Then
lStrDif = Len(s1stCtyName) - 5
s1stCtyName = Right(s1stCtyName, Len(s1stCtyName) - lStrDif)
Else
If MsgBox("No ADAMS county found in county list!",
vbRetryCancel) _
= vbCancel Then
Exit Sub
Else
Application.ScreenUpdating = True
End If
End If
With uf1021Mid.rColStart
lLastCol = .Columns(.Columns.Count).Column
End With
Set rExtrFromStrt = uf1021Mid.rColStart
If rExtrFromStrt Is Nothing Then
Exit Sub 'user hit cancel
End If
rExtrFromStrt.Select
If cbHdr = True Then
'MsgBox "true"
bHdr = True
End If
uf1021Mid.Hide