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

Are those public variable declarations in a General module?

If no, then put them in a General module.

ps. This procedu

Private Sub btnCancel_Click()
End
End Sub

Will reset all your variables. Not an elegant way to do this if that's what you
intended.

davegb wrote:

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

--

Dave Peterson

On Nov 16, 4:31 pm, Dave Peterson wrote:
Are those public variable declarations in a General module?

If no, then put them in a General module.

ps. This procedu

Private Sub btnCancel_Click()
End
End Sub

Will reset all your variables. Not an elegant way to do this if that's what you
intended.





davegb wrote:

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

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, thanks for your reply.
I moved the public declarations from the userform module to the
general module. Now I'm getting a compile error on one of the
variables that I wasn't getting before. Not sure why, but I'm trying
to track it down.

As for the Button Cancel routine resetting my variables, I didn't know
it did that, but I don't think it matters. I just wanted to end the
whole routine if the user chooses "Cancel". If there's a better way,
I'm all ears, like Prince Charles! :)

#2. Maybe you could just unload the form.

Private Sub btnCancel_Click()
unload me
End Sub

#1. Difficult to guess without seeing what changes you made.

davegb wrote:

On Nov 16, 4:31 pm, Dave Peterson wrote:
<<snipped

Dave, thanks for your reply.
I moved the public declarations from the userform module to the
general module. Now I'm getting a compile error on one of the
variables that I wasn't getting before. Not sure why, but I'm trying
to track it down.

As for the Button Cancel routine resetting my variables, I didn't know
it did that, but I don't think it matters. I just wanted to end the
whole routine if the user chooses "Cancel". If there's a better way,
I'm all ears, like Prince Charles! :)

--

Dave Peterson

On Nov 19, 12:26 pm, Dave Peterson wrote:
#2. Maybe you could just unload the form.

Private Sub btnCancel_Click()
unload me
End Sub


I modified the code to try this. I remember I found this in this NG
some time ago and tried it, but it didn't recognize "Me" and I had to
replace it with the userform name to get it to work. Never figured out
why. Can't test it until I figure out why I'm getting the compile
error in the General module.

#1. Difficult to guess without seeing what changes you made.


Of course. I prefer to work on a problem for a while before I post
here. I learn more that way and actually get to write some code. I
appreciate you experts taking the time to help me out here, but I
prefer not to bother you until I'm stumped. Thanks again for all your
help.



davegb wrote:

On Nov 16, 4:31 pm, Dave Peterson wrote:
<<snipped

Dave, thanks for your reply.
I moved the public declarations from the userform module to the
general module. Now I'm getting a compile error on one of the
variables that I wasn't getting before. Not sure why, but I'm trying
to track it down.

As for the Button Cancel routine resetting my variables, I didn't know
it did that, but I don't think it matters. I just wanted to end the
whole routine if the user chooses "Cancel". If there's a better way,
I'm all ears, like Prince Charles! :)

--

Dave Peterson

The Me keyword refers to the object that owns the code.

If the code is behind a worksheet, then Me refers to that worksheet.

If the code is in the ThisWorkbook module, then Me refers to the workbook that
owns the code.

If the code is behind the userform, then Me refers to that userform.

If your code is in a general module, then the Me keyword won't work. That's my
guess what happened in your earlier attempt.

davegb wrote:

On Nov 19, 12:26 pm, Dave Peterson wrote:
#2. Maybe you could just unload the form.

Private Sub btnCancel_Click()
unload me
End Sub


I modified the code to try this. I remember I found this in this NG
some time ago and tried it, but it didn't recognize "Me" and I had to
replace it with the userform name to get it to work. Never figured out
why. Can't test it until I figure out why I'm getting the compile
error in the General module.

#1. Difficult to guess without seeing what changes you made.


Of course. I prefer to work on a problem for a while before I post
here. I learn more that way and actually get to write some code. I
appreciate you experts taking the time to help me out here, but I
prefer not to bother you until I'm stumped. Thanks again for all your
help.



davegb wrote:

On Nov 16, 4:31 pm, Dave Peterson wrote:
<<snipped

Dave, thanks for your reply.
I moved the public declarations from the userform module to the
general module. Now I'm getting a compile error on one of the
variables that I wasn't getting before. Not sure why, but I'm trying
to track it down.

As for the Button Cancel routine resetting my variables, I didn't know
it did that, but I don't think it matters. I just wanted to end the
whole routine if the user chooses "Cancel". If there's a better way,
I'm all ears, like Prince Charles! :)

--

Dave Peterson

--

Dave Peterson

On Nov 20, 8:14 am, davegb wrote:
On Nov 19, 12:26 pm, Dave Peterson wrote:

#2. Maybe you could just unload the form.

Private Sub btnCancel_Click()
unload me
End Sub

I modified the code to try this. I remember I found this in this NG
some time ago and tried it, but it didn't recognize "Me" and I had to
replace it with the userform name to get it to work. Never figured out
why. Can't test it until I figure out why I'm getting the compile
error in the General module.


I made this change but it caused problems. When I just close the form,
the following code tries to execute but since the userform data is not
there, I get undefined variable errors. Is there a better way of
handling all this? For now, to work on the program, I just put back
the End statement.

#1. Difficult to guess without seeing what changes you made.

Of course. I prefer to work on a problem for a while before I post
here. I learn more that way and actually get to write some code. I
appreciate you experts taking the time to help me out here, but I
prefer not to bother you until I'm stumped. Thanks again for all your
help.


Found the problem and fixed it.





davegb wrote:

On Nov 16, 4:31 pm, Dave Peterson wrote:
<<snipped

Dave, thanks for your reply.
I moved the public declarations from the userform module to the
general module. Now I'm getting a compile error on one of the
variables that I wasn't getting before. Not sure why, but I'm trying
to track it down.

As for the Button Cancel routine resetting my variables, I didn't know
it did that, but I don't think it matters. I just wanted to end the
whole routine if the user chooses "Cancel". If there's a better way,
I'm all ears, like Prince Charles! :)

--

Dave Peterson- Hide quoted text -

- Show quoted text -