PDA

View Full Version : Solved: How to maintain control as nested subs exit?



RonMcK3
09-01-2007, 11:46 AM
I want to create a small application. It will allow a user to create a worksheet and then process a column of data the user manually enters. The programs for each of the two processes have been written and work.

I have designed UserForm1; it has a ComboBox and Option Buttons for data entry and CommandButtons for selecting the process to run (BuildWS or LinkLessons) or Cancel. I have built some common subroutines because some steps in each process are identical (validate selection of a valid state and a grade in the active range). Even in the subroutine there are steps that are used multiple times so I placed these in their own subroutines.

My challenge is similar to Darren?s issue at ?Update userform after changes have been made on another userform?.

When a subroutine finds that no state code was entered, it executes cbStateCode.SetFocus. I want each of the subroutines above it to abort and exit, returning the program to UserForm1 with focus on the StateCode ComboBox.

How does an abnormal exit made from a sub routine that?s 2 or 3 or more levels down from the UserForm get relayed back up to it? Raise an ?error?? Use a public variable and code my own tests?

What other information would help you help me? I've attached a copy of my worksheet for this work in process.

TIA!

mdmackillop
09-01-2007, 12:29 PM
Hi Ron
Welcome to VBAX
First of all, there is no real benefit in storing the subs in a standard module, unless they are being used by other userforms. By doing so, you need to build in a reference back to the userform as in
Sub GetStateGrade()
With UserForm1
If .cbStateCode.ListIndex = -1 Or .cbStateCode.Text = "" Then
MsgBox "You must select a State Code."
.cbStateCode.SetFocus
Exit Sub
End If


Personally, the only sub I would put in Module 1 is
Sub Shows()
UserForm1.Show
End Sub

Move the rest into the Userform code module and use this to set the combobox values
Private Sub UserForm_Initialize()

' Fill the ComboBox
With cbStateCode
.AddItem "GA"
.AddItem "IL"
End With
End Sub

I've never used GoSub and it doesn't work here. From what I read, it needs a Return statement. As far as I can see, there is no need for it, so I would delete it.

Most importantly, always use Option Explicit at the start of your code and use Debug/Compile to check for errors as you go.

RonMcK3
09-01-2007, 02:33 PM
md,

For the moment, I have moved GetStateGrade and its subs back into UserForm1[code], moved the UserForm1.Show into code for a command button on a worksheet in the WB, and changed the sub name for populating the combo box to UserForm_Initialize().

With no input values selected (state or grade), when I click Build_WS button, the program gets to the appropriate error trap, displays the msg box. When I click 'ok' the mb goes away, the program exits the GetStateGrade sub BUT it continues on into the Build_WS sub instead of returning to userform1 and letting/making me enter good data.

Where have I gone astray?:banghead:

Pertinent code snippets are below; the current iteration of the wb is attached en zip.


Private Sub cbBuild_WS_Click()
cbLessonLinks.Enabled = False
cbCancel.Enabled = False
GetStateGrade '<< problem starts in this sub,
' it does not return program control to UserForm1
' instead it just goes to the next line as if GetStateGrade
' processed successfully.

Build_WS
cbLessonLinks.Enabled = True
cbCancel.Enabled = True
Reset_UserForm1
End Sub


Sub GetStateGrade()
Dim StCode As String, StName As String, strGrade As String
Dim IsEnabled As Boolean
Dim Grade As Long, FirstGrade As Long, LastGrade As Long
With UserForm1
If cbStateCode.ListIndex = -1 Or cbStateCode.Text = "" Then
MsgBox "You must select a State Code."
cbStateCode.SetFocus
Exit Sub
' md: when this sub exits the program contines to the next line
' in the prior sub as if nothing has happened. It is not
' returning to deal with getting a 'good' state code.

End If
StCode = cbStateCode.Text
Select Case StCode
Case "GA"
StName = "Georgia"
IsEnabled = True
FirstGrade = 1
LastGrade = 5

Many thanks,

Aussiebear
09-01-2007, 03:54 PM
Ron, I'm just having a guess here but what would happen if you changed the 'End If" to an "Else" after you exit the sub?

RonMcK3
09-01-2007, 05:21 PM
Thanks, Aussiebear. No joy. I moved the End If down below the Select Case code and inserted an Else in its stead. I'm fairly certain that the code in the GetStateGrade sub is fine.

What isn't happening seems to be that the cbBuild_WS_Click() sub is not detecting that GetStateGrade is unhappy.

I'm going to see what I can accomplish by adding and using a public variable Return2UF (as Boolean).

More news as it develops; film at 11.

Bob Phillips
09-01-2007, 05:57 PM
I'm getting basic syntax errors, impossible to test in such a state.

rbrhodes
09-02-2007, 12:59 AM
Hi RonMcK,

Wow! Did I have some fun with this!

I think I commented all that I did in the code, any questions about what I did, let me know.

Looks like you've been working on this for a while as the variables were all over the map (a sure sign <G>) and there were quite a few (sic) speeling mistakes: Use Option Explicit!

In the VBE, click Tools/Options and on the Editor Tab check 'Require Variable Declaration' (I recommend you check off eveything...)

Well that's enough outta me!

RonMcK3
09-03-2007, 10:19 AM
Wow! Did I have some fun with this!

I think I commented all that I did in the code, any questions about what I did, let me know.

Looks like you've been working on this for a while as the variables were all over the map (a sure sign <G>) and there were quite a few (sic) speeling mistakes: Use Option Explicit!

In the VBE, click Tools/Options and on the Editor Tab check 'Require Variable Declaration' (I recommend you check off eveything...)

Well that's enough outta me!
Thank you, dr, I'll compare your fixes and suggestions with what I've done since XLD's post. Someone earlier in the thread pointed me at Option Explicit; I added it to each module and have killed most of the typos. I am reworking Build_WS and LessonLinks subs, cleaning them up. I will upload my files after I get a wee bit more work done.

:)

RonMcK3
09-03-2007, 05:25 PM
Wow! Did I have some fun with this!

I think I commented all that I did in the code, any questions about what I did, let me know.

dr,

Sorry about all the typos.:(

Thank you very much! Your revisions and comments are a very good tutorial. Without fanfare, you showed me how to drive the State Code selection with a 'hidden' table filled with the state-specific values needed by the program. I used the technique to load the combobox list. You also tightened up subs that were overlooking unlikely but possible exceptions.

The lingering question is why have a command button on the worksheet if the Workbook_Open() sub in ThisWorkbook does the UserForm1.Show, and the Cancel_Click() unloads UserForm1 and closes the file?


:thumb :mbounce::bow:

RonMcK3
09-03-2007, 06:20 PM
As expected, the tool tips appear as I move my mouse over the various controls on UserForm1; what I didn't expect was for the portion of any tool tip that extends past the border of the userform to remain (an 'artifact') and for these to accumulate until I Cancel and close my UserForm and file.

Is this 'normal' (or 'std') behavior for a VBA app? Attached for your viewing pleasure is a screen capture (in a MS Word doc)

:dunno

Many thanks,

RonMcK3
09-05-2007, 07:31 PM
Hi All !

I thought you might like to see the pretty much finished product. The zip file includes the two configuration files that I added, in one case moving sheets specific to one state into its own file so the "program file" does need only be changed if I add more features to the program.

It works both here on my PC (XP-Excel 2002) and on my Mac at work (2004), except that the command button on the worksheet doesn't work on the Mac.

Let me know if you have any questions.

Thanks again, everyone (especially Dusty) for your help!:bow:

Ron:hi:
Orlando, FL