Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
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! :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
#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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
On Nov 20, 9:54 am, Dave Peterson wrote:
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- Hide quoted text - - Show quoted text - Didn't know that, so that's probably exactly what I did. Thanks again. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
There was no following code.
davegb wrote: 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 - -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
On Nov 21, 12:03 pm, Dave Peterson wrote:
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. davegb wrote: 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 - -- Dave Peterson- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
If your other code unloads the userform, then maybe just hiding the userform
would be enough. Private Sub btnCancel_Click() me.hide End Sub But it's just a guess. davegb wrote: On Nov 21, 12:03 pm, Dave Peterson wrote: 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. davegb wrote: 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 - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
range not carrying over
On Nov 26, 8:25 am, Dave Peterson wrote:
If your other code unloads the userform, then maybe just hiding the userform would be enough. Private Sub btnCancel_Click() me.hide End Sub 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? davegb wrote: On Nov 21, 12:03 pm, Dave Peterson wrote: 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. davegb wrote: 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 - -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Carrying last $$$ amount to another field | Excel Worksheet Functions | |||
Carrying data over in worksheets | Excel Worksheet Functions | |||
Carrying a balance forward | Excel Worksheet Functions | |||
Carrying formatting automatically to new W/S | Excel Worksheet Functions | |||
IF is not carrying out the FALSE part | Excel Worksheet Functions |