range not carrying over

[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

 

[Dave Peterson]

Are those public variable declarations in a General module?

If no, then put them in a General module.

ps. This procedure:

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]

Are those public variable declarations in a General module?

If no, then put them in a General module.

ps. This procedure:


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

























--

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!

 

[Dave Peterson]

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

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

 

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

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.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Didn't know that, so that's probably exactly what I did. Thanks again.

 

[davegb]

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.

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.

 

[Dave Peterson]

There was no following code.

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.


Found the problem and fixed it.

 

[davegb]

There was no following code.

By following code, I meant the code that runs after the form is
closed. When it tried to run, since none of the variables that were
defined by the user filling in the userform, I was geting an undefined
variable error. So the Cancel button has to not just close the
userform, but stop running the other code.

Thanks again for all your help.

 

[Dave Peterson]

If your other code unloads the userform, then maybe just hiding the userform
would be enough.

But it's just a guess.

 

[davegb]

If your other code unloads the userform, then maybe just hiding the userform
would be enough.


But it's just a guess.

I'll give that a try next week, when I'm back at the office. Can you
tell me what's wrong with using "End" as I've done? Obviously,
there's some reason why you're steering me away from that. But it
seems to me that if I hide the form, but the user hasn't filled it in,
that I'll have the same problem with undefined variables if I just
hide the form, when the remaining code runs. Since "End" is working ok
in this instance, there's got to be a particular reason why you feel
it's not good practice. You mentioned earlier that it would "reset the
variables". I'm not sure what that means, but since the routine is
ending, does it matter? Or is this a "bad practice" that will cause me
problems in some other situation? Can you elaborate?