Excel Vba Userform Shows but Code Still Continues
Force Code To Wait Until Userform Is Closed
- Andy Hennig
-
- #1
Situation:
At two places in the middle of my code I need to specify a particular variable; usually this is done by some autodection-method, but whenever this fails a userform gets loaded and the user needs to do some clicks which finally specifies that variable.Problem:
So far my code does NOT wait for the userform (and the user-input), but continues running until it fails, cause that particular variable (see above) had not been initialised with the correct value for this run.My Question:
How can I force the code to wait until the userform has been closed? Or what other way do you usually solve that problem when wanting to use a userform in mid-code? Below you find the place where I load the User form 'UsrFormSpecifyFormat'.Have a pleasant sunday,
Andy'public slotNumber as Integer 'globally visible '[...] Select Case intFormatMatches Case 0 UsrFormSpecifyFormat.Show vbModeless 'this form set the globally visible variable 'slotNumber' to a certain value Case 1 slotNumber = arrDecideSlot(indexPos) Case Else slotNumber = pinDownFormat(wksB, arrDecideSlot, hasheader) End Select '[...]Display More
-
- #2
Re: Force Code To Wait Until Userform Is Closed
Hi Andy,
You would have to set the output of the form to the variable and test if anything was entered...similar to the example below where you set a variable to be an inputbox. If the user clicks "cancel" then the variable returned = false. You can then check its condition and run code accordingly.
Dim x As String x = Application.InputBox("Enter a number between 1 and 10") If x = False Then Exit Sub End If 'Run code if x had a number entered.Display More
Does that help?
Brad
-
- #3
Re: Force Code To Wait Until Userform Is Closed
Hi Brad,
thank you for your input! Excuse me for asking, but how do I set the output of the form to the variable, then? As far as I have tried it is not possible to do something like 'x=userform.show', as an userform does not have an output.
I have tried your code ... and with that input-box it works perfectly fine with me :), it's just that I need it to work with an userform instead of an inputbox...
Andy
-
- #4
Re: Force Code To Wait Until Userform Is Closed
Hi Andy,
I'd need a little more detail as to what's happening in your code. In most situations I would validate the userforms data before letting them close the form. Eg, If the information isn't right, print an error message and don't hide the form.
Can you post a little more of the code and what is happening with it?
Brad.
-
- #5
Re: Force Code To Wait Until Userform Is Closed
hey, sure! And thanks for the quick answer.
How much ... or what code do you need? I'll insert here my userform-code (see below)
Andy
Purpose of UserForm:
The purpose of the userform is to let the user chose between up to 7 different input-data-formats. (This selection is supported with some graphical feature like highlighting certain cells according to the chosen format as well as with some information about the availability of certain formats.) Finally, the user's choice will be stored as an integer [1..7] in a globally visible variable called "slotNumber" of type integer.Structure of UserForm:
The UserFrom contains 7 option buttons [Thanks to Tony Ward;)] and two command buttons: one for accepting the chosen format, the other one for cancelling and returning. The former one also includes a data-validation-function, returning 'true' if user-input can be processed.Code of the UserForm:
'*************************************************************************** '* '* FRAME NAME: UsrFormUserFormat '* '* DESCRIPTION: UserInterface that allows user to specify sample-format '* '* UPDATES: '* DATE COMMENTS '* 18 Mar 2007 form established: needs methods from usrFormCustomizeFormat '* '*************************************************************************** Dim wksSample As Worksheet Dim wksSettings As Worksheet Public Const slotBegin = 9 'first slot available Public Const boolActiveCol = 2 'column number of boolean variables regarding activation of slot Public Const boolTimeCol = 5 'column number of boolean variables regarding existance of time vector Public Const boolIntensityCol = 7 'column number of boolean variables regarding existance of intensity vector Public Const boolAreaCol = 12 'column number of boolean variables regarding existance of area values ' Public Const totalColsCol = 21 ' Public Const minRowsCol = 22 ' Public Const contentHeaderCol = 23 'column number with first header line values (if existant) ' Public Const contentHeaderLength = 5 'amount of cells which possibly hold header content ' Public Const slotEnd = 15 'last slot availabe '************************************************* '* Initialize '************************************************* Private Sub UserForm_Initialize() Set wksSample = ActiveSheet Set wksSettings = Sheets(wksSettingsName) 'ensuring the hidden SETTINGS-Worksheet exists On Error Resume Next If wksSettings Is Nothing Then 'Doesn't exist 'Call UsrFormCustomizeFormat.createSettingsSheet '<-- temporally canceled for www.ozgrid.com On Error GoTo 0 '???? Else wksSettings.Visible = True End If 'load Step2 status of format-types Call loadSettingsStatus 'set variable, which user is about to specify to negative value intUsrSpecified = -9999 End Sub '************************************************* '* Controls '************************************************* Private Sub obTimeOnly_Click() Call sheetColorUpdate(wksSample) End Sub Private Sub obIntensityOnly_Click() Call sheetColorUpdate(wksSample) End Sub Private Sub obAreaOnly_Click() Call sheetColorUpdate(wksSample) End Sub Private Sub obTimeIntensity_Click() Call sheetColorUpdate(wksSample) End Sub Private Sub obTimeArea_Click() Call sheetColorUpdate(wksSample) End Sub Private Sub obIntensityArea_Click() Call sheetColorUpdate(wksSample) End Sub Private Sub obTimeIntensityArea_Click() Call sheetColorUpdate(wksSample) End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Unload UsrFormSpecifyFormat End Sub Private Sub UserForm_Terminate() Unload UsrFormSpecifyFormat End Sub '************************************************* '* Buttons '************************************************* Private Sub cbAcceptAndContinue_Click() If Not checkValidSelection Then MsgBox "This format has not yet been customized for usage. Return to main-menu and click on 'Customize Format' if you would like to use this format.", vbInformation, "Invalid Format" Exit Sub End If Call clearAllMarks 'clears up all color, comments and bold Call readOutSlotNumber 'this sets the globally visible slotNumber 'hide settings-sheet wksSettings.Visible = xlVeryHidden 'wksSettings.Visible = True 'unload usrFormCustomizeFormat Unload UsrFormSpecifyFormat End Sub Private Sub cbCancelPasting_Click() Call clearAllMarks 'clears up all color, comments and bold 'hide settings-sheet wksSettings.Visible = xlVeryHidden 'wksSettings.Visible = True 'unload usrFormCustomizeFormat Unload UsrFormSpecifyFormat End Sub '************************************************* '* Methods, Subs and Functions '************************************************* Private Sub clearAllMarks() With ActiveSheet 'clear all former marks .Cells.Interior.ColorIndex = xlNone 'clear all field color marks .Cells.Font.ColorIndex = vbBlack 'clear all letter color marks .Cells.ClearComments 'clear all comments .Cells.Font.Bold = False End With End Sub Private Sub loadSettingsStatus() With wksSettings 'time vector If .Cells(slotBegin, boolActiveCol).Value = True Then obTimeOnly.Caption = "Time only (activated)" End If 'only Intensity vector If .Cells(slotBegin + 1, boolActiveCol).Value = True Then obIntensityOnly.Caption = "Intensity only (activated)" End If 'only Area If .Cells(slotBegin + 2, boolActiveCol).Value = True Then obAreaOnly.Caption = "Area only (activated)" End If 'Time and Intensity If .Cells(slotBegin + 3, boolActiveCol).Value = True Then obTimeIntensity.Caption = "Time and Intensity (activated)" End If 'Time and Area If .Cells(slotBegin + 4, boolActiveCol).Value = True Then obTimeArea.Caption = "Time and Area (activated)" End If 'Intensity And Area If .Cells(slotBegin + 5, boolActiveCol).Value = True Then obIntensityArea.Caption = "Intensity and Area (activated)" End If 'time, Intensity and Area If .Cells(slotBegin + 6, boolActiveCol).Value = True Then obTimeIntensityArea.Caption = "Time, Intensity and Area (activated)" End If End With End Sub Private Function checkValidSelection() As Boolean Dim strPosTag As String 'String which is used to signal an ACTIVE format Dim strNegTag As String 'String which is used to signal an INACTIVE format strPosTag = "*(activated)" strNegTag = "*(deactivated)" 'starting with a negative assumption checkValidSelection = False 'figure out, which option button is checked: If obTimeOnly.Value = True Then If obTimeOnly.Caption Like strPosTag Then checkValidSelection = True Exit Function End If If obIntensityOnly.Value = True Then If obIntensityOnly.Caption Like strPosTag Then checkValidSelection = True Exit Function End If If obAreaOnly.Value = True Then If obAreaOnly.Caption Like strPosTag Then checkValidSelection = True Exit Function End If If obTimeIntensity.Value = True Then If obTimeIntensity.Caption Like strPosTag Then checkValidSelection = True Exit Function End If If obTimeArea.Value = True Then If obTimeArea.Caption Like strPosTag Then checkValidSelection = True Exit Function End If If obIntensityArea = True Then If obIntensityArea.Caption Like strPosTag Then checkValidSelection = True Exit Function End If If obTimeIntensityArea = True Then If obTimeIntensityArea.Caption Like strPosTag Then checkValidSelection = True Exit Function End If End Function Private Sub sheetColorUpdate(wksTarget As Worksheet) Dim intTemp As Integer Dim rng As Range Dim tempRow As Variant, tempCol As Variant Dim somethingMarked As Boolean 'specify which button is selected: slotNumber Call readOutSlotNumber With wksTarget Call clearAllMarks 'clears up all color, comments and bold 'if sheet has header, mark header If wksSettings.Cells(slotBegin + slotNumber - 1, boolActiveCol) Then somethingMarked = True .Rows(1).Font.Bold = True End If 'mark time vector If wksSettings.Cells(slotBegin + slotNumber - 1, boolTimeCol) Then somethingMarked = True intTemp = CInt(wksSettings.Cells(slotBegin + slotNumber - 1, boolTimeCol + 1).Value) If intTemp > 0 Then .Columns(intTemp).Interior.ColorIndex = 43 With .Cells(2, intTemp) .AddComment .Comment.text text:="Element of the currently chosen Time Vector" End With End If End If 'mark intensity vector If wksSettings.Cells(slotBegin + slotNumber - 1, boolIntensityCol) Then somethingMarked = True 'intensity vector for total area intTemp = CInt(wksSettings.Cells(slotBegin + slotNumber - 1, boolIntensityCol + 1).Value) If intTemp > 0 Then .Columns(intTemp).Interior.ColorIndex = 44 With .Cells(2, intTemp) .AddComment .Comment.text text:="Element of the currently chosen Total Intensity vector" End With End If 'intensity vector for bleached area intTemp = CInt(wksSettings.Cells(slotBegin + slotNumber - 1, boolIntensityCol + 2).Value) If intTemp > 0 Then .Columns(intTemp).Interior.ColorIndex = 45 With .Cells(2, intTemp) .AddComment .Comment.text text:="Element of the currently chosen Bleached Intensity vector" End With End If 'intensity vector for unbleached area intTemp = CInt(wksSettings.Cells(slotBegin + slotNumber - 1, boolIntensityCol + 3).Value) If intTemp > 0 Then .Columns(intTemp).Interior.ColorIndex = 46 With .Cells(2, intTemp) .AddComment .Comment.text text:="Element of the currently chosen Unbleached Intensity vector" End With End If 'intensity vector for background area intTemp = CInt(wksSettings.Cells(slotBegin + slotNumber - 1, boolIntensityCol + 4).Value) If intTemp > 0 Then .Columns(intTemp).Interior.ColorIndex = 53 With .Cells(2, intTemp) .AddComment .Comment.text text:="Element of the currently chosen Background Intensity vector" End With End If End If 'mark area cells If wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol) Then somethingMarked = True 'area cell for total area tempRow = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 1).Value tempCol = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 2).Value If tempRow > 0 And tempCol > 0 Then With .Cells(CInt(tempRow), CInt(tempCol)) .Interior.ColorIndex = 37 .AddComment .Comment.text text:="Currently chosen Total Area value" End With End If 'area cell for bleached area tempRow = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 3).Value tempCol = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 4).Value If tempRow > 0 And tempCol > 0 Then With .Cells(CInt(tempRow), CInt(tempCol)) .Interior.ColorIndex = 42 .AddComment .Comment.text text:="Currently chosen Bleached Area value" End With End If 'area cell for unbleached area tempRow = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 5).Value tempCol = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 6).Value If tempRow > 0 And tempCol > 0 Then With .Cells(CInt(tempRow), CInt(tempCol)) .Interior.ColorIndex = 5 .AddComment .Comment.text text:="Currently chosen Unbleached Area value" End With End If 'area cell for background area tempRow = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 7).Value tempCol = wksSettings.Cells(slotBegin + slotNumber - 1, boolAreaCol + 8).Value If tempRow > 0 And tempCol > 0 Then With .Cells(CInt(tempRow), CInt(tempCol)) .Interior.ColorIndex = 55 .AddComment .Comment.text text:="Currently chosen Background Area value" End With End If End If 'if nothing has been selected, mark entire area grey If somethingMarked = False Then .Range(.Cells(1, 1), .Cells(.Cells(65536, 1).End(xlUp).row, .Cells(1, 255).End(xlToLeft).Column)).Font.ColorIndex = 15 ' .Range(.Cells(1, 1), .Cells(.Cells(65536, 1).End(xlUp).row, .Cells(1, 255).End(xlToLeft).Column)).Interior.ColorIndex = 15 End If 'select a cell for user-focus .Range("A1").Select End With End Sub Private Sub readOutSlotNumber() 'figure out, which checkbox is checked at frame 'Step2' If obTimeOnly.Value = True Then slotNumber = 1 If obIntensityOnly.Value = True Then slotNumber = 2 If obAreaOnly.Value = True Then slotNumber = 3 If obTimeIntensity.Value = True Then slotNumber = 4 If obTimeArea.Value = True Then slotNumber = 5 If obIntensityArea = True Then slotNumber = 6 If obTimeIntensityArea = True Then slotNumber = 7 End SubDisplay More
-
- #6
Re: Force Code To Wait Until Userform Is Closed
Do you have a main procedure that calls (or shows) the form?
Brad.
-
- #7
Re: Force Code To Wait Until Userform Is Closed
OK, then this is the sub from where the userform gets called. This userinput is needed in the second but last 'Select Case'-loop for the last 'Select Case'-loop to execute correctly.
Most of the constants and variables are not needed in this case here, I guess, but the main-program with all its subs requires several.
'--------------------------------------------------------------------------------------- '--------------------------Some Constants------------------------------------------- '--------------------------------------------------------------------------------------- Const alwaysAskUserForSampleFormat =false Const screenUpdatesBegin=false Const wksSetttingsName="Settings" '--------------------------------------------------------------------------------------- '--------------------------Position of Data in the hidden 'settings'-sheet-------------- '--------------------------------------------------------------------------------------- Public Const boolActiveCol = 2 'column number of boolean variables regarding activation of slot Public Const boolTimeCol = 5 'column number of boolean variables regarding existance of time vector Public Const boolIntensityCol = 7 'column number of boolean variables regarding existance of intensity vector Public Const boolAreaCol = 12 'column number of boolean variables regarding existance of area values Public Const totalColsCol = 21 Public Const minRowsCol = 22 Public Const contentHeaderCol = 23 'column number with first header line values (if existant) Public Const contentHeaderLength = 5 'amount of cells which possibly hold header content Public Const slotBegin = 9 'first slot available Public Const slotEnd = 15 'last slot availabe Sub insertData2() ' formerly know as Sub FRAP_eval_9() Dim wksA As Worksheet, wksB As Worksheet Dim aFmts As Variant 'wichtig für das "aus dem clipboard"-Kopieren Dim fmt As Variant 'wichtig für das "aus dem clipboard"-Kopieren Dim clipCheck As Boolean Dim intMaxCols As Integer, intMaxRows As Integer, intMaxRowsEff As Integer Dim hasheader As Boolean Dim c As Range Dim arrDecideSlot() Dim counter As Integer, intFormatMatches As Integer Dim indexPos As Integer 'temp variable that holds the number of the settingsslot to be applied for data-extraction from the sample ' Dim slotNumber As Integer 'final variable that holds the number of the settingsslot to be applied for data-extraction from the sample 'no screen repainting: makes macro run faster Application.ScreenUpdating = screenUpdatesBegin ' ensuring that cells get updated automatically Application.Calculation = xlCalculationAutomatic 'remember the name of the active worksheet in which all data will Set wksA = ActiveSheet 'IF there is anything in the cache, then insert a new Worksheet and paste everything what is in the cache aFmts = Application.ClipboardFormats For Each fmt In aFmts If (fmt = xlClipboardFormatText) Or (fmt = xlClipboardFormatTable) Then Set wksB = Sheets.Add ActiveSheet.Paste Selection.Interior.ColorIndex = 0 clipCheck = True Exit For 'attention: new End If Next 'Possible interrupt because of missing data-sample If clipCheck = False Then MsgBox "Please make sure you have text-data copied into the clipboard [Ctrl-C] before running this makro (e.g. ImageJ Multi Measure data of four areas)." Exit Sub End If 'possible interrupt because user wants to be asked for provided format-type If alwaysAskUserForSampleFormat Then 'slotNumber GoTo launch_insert_sub End If 'possible interrupt because of missing (hidden) 'setting'-sheet If Sheets(wksSettingsName) Is Nothing Then MsgBox "Please customize your data-formats before inserting data. Click 'Customize Format' on the main menu.", vbInformation, "Missing format set-up" Exit Sub End If '------------------------------------------------------------------------' 'Analyse Data-Sample' '------------------------------------------------------------------------' With wksB 'Calculation of pasted data-size intMaxCols = .Cells(1, 255).End(xlToLeft).Column If .Cells(65536, 2).End(xlUp).row > .Cells(65536, 1).End(xlUp).row Then intMaxRows = .Cells(65536, 2).End(xlUp).row Else intMaxRows = .Cells(65536, 1).End(xlUp).row End If If intMaxRows < 5 Then intMaxRowsEff = intMaxRows Else intMaxRowsEff = 5 End If 'Header Analysis hasheader = False For Each c In .Range(.Cells(1, 1), .Cells(1, intMaxCols)) If c.Value <> vbNullString Then If Not IsNumeric(c.Value) Then hasheader = True End If Else hasheader = True Exit For End If Next c End With 'check if how many slots in (hidden) 'settings'-sheet fullfill given criteria ReDim arrDecideSlot(1 To slotEnd - slotBegin + 1) With Sheets(wksSettingsName) 'previously had been tested for existance counter = 0 For Each c In .Range(.Cells(slotBegin, boolActiveCol), .Cells(slotEnd, boolActiveCol)) counter = counter + 1 If Not c.Value = "" Then If c.Value = True And _ .Cells(c.row, totalColsCol).Value = intMaxCols And _ .Cells(c.row, minRowsCol).Value = intMaxRowsEff Then arrDecideSlot(counter) = 1 indexPos = counter End If End If Next c End With 'come up with the right slotNumber intFormatMatches = CInt(arraySum(arrDecideSlot)) Select Case intFormatMatches Case 0 Dim x slotNumber = -9999 UsrFormSpecifyFormat.Show vbModeless UsrFormSpecifyFormat.Label1.Caption = "ATTENTION: Your data does not match none of those saved in your settings. Nevertheless you can force your sample to match a format (not-recommended) or Cancel (and customize your active formats)." 'MsgBox "Your data does not match none of those saved in your settings. ", vbInformation, "Error: Na matching format found" 'myArg=InputBox "Your data does not match none of those saved in your settings. ", vbOKCancel, "Error: Na matching format found" Case 1 slotNumber = arrDecideSlot(indexPos) Case Else 'slotNumber = pinDownFormat(wksB, arrDecideSlot, hasheader, intMaxCols, intMaxRows) 'Call pinDownFormat(wksB, arrDecideSlot, hasheader, intMaxCols, intMaxRows) '<-- temporarily canceled End Select '-----------------------------------------------------------------' '-THIS IS WHERE THE CODE SHOULD PAUSE, WAITING FOR USERFORM--------' '-----------------------------------------------------------------' launch_insert_sub: 'launch the correct insert-sub, according to the data type in sample Select Case slotNumber Case 1 ' Call insertTimeOnly(wksA, wksB, 1, hasheader, intMaxRows, intMaxCols) Case 2 'Call insertIntensityOnly(wksA, wksB, 2, hasheader, intMaxRows, intMaxCols) Case 3 'Call insertAreaOnly(wksA, wksB, 3, hasheader, intMaxRows, intMaxCols) Case 4 'Call insertTimeIntensity(wksA, wksB, 4, hasheader, intMaxRows, intMaxCols) Case 5 'Call insertTimeArea(wksA, wksB, 5, hasheader, intMaxRows, intMaxCols) Case 6 'Call insertIntensityArea(wksA, wksB, 6, hasheader, intMaxRows, intMaxCols) Case 7 'Call insertTimeIntensityArea(wksA, wksB, 7, hasheader, intMaxRows, intMaxCols) Case Else 'welllllllll? End Select End SubDisplay More
-
- #8
Re: Force Code To Wait Until Userform Is Closed
Hi Andy,
Are you able to attach the workbook or a sample of it. I'm having a little trouble wrapping my head around what this code is doing.
Generally speaking, I would have the form wrapping around the select case rather than having the form inside the select case statement. The form would then trigger another procedure which could pass information to the main procedure or the public variables.
Brad.
-
- #9
Re: Force Code To Wait Until Userform Is Closed
ai, so you mean, at the end of my sub I should launch/show the user form in any case, but have that 'Select Case'-loop WITHIN the user form? Wow, that's great, I shall give it a try! Thank you!
Due to some extensions, currently, the workbook is one single contruction site with about 50 subs, and only half of them is tuned into a general concept.
I will see if I get your idea working, if not ... well, then I will paste here some running mini-version of the code. -
- #10
Re: Force Code To Wait Until Userform Is Closed
Quote from Andy Hennig
Due to some extensions, currently, the workbook is one single contruction site with about 50 subs, and only half of them is tuned into a general concept.
I will see if I get your idea working, if not ... well, then I will paste here some running mini-version of the code.
I understand Andy. Let me know how you go. It just seems a bit odd that the form is within the code and not the other way around. Generally, forms are the starting point for running a procedure.Anyway, I'd be interested to know how you go. There are so many ways to address one single problem within excel, we'll get a workable solution eventually.
Brad
-
- #11
Re: Force Code To Wait Until Userform Is Closed
You've opened your form "modeless".
[vba]
UsrFormSpecifyFormat.Show vbModeless
[/vba]Here's an excerpt from http://www.thecodenet.com/articles.php?id=7
Quote
Display More
Modal vs modelessForms can be shown in one of two "modes" - modal or modeless. Modal forms do not allow the user to do anything else in Excel while the form is visible - like a MsgBox. Modeless forms allow the user to move around in Excel, do other things, and then return to the form.
**Code execution will also continue in the background while a modeless form is shown.**
You can make your code wait until the form has been closed using a loop that checks whether the form has been closed:
[vba]
Do Until UsrFormSpecifyFormat.Visible = False
DoEvents
Loop
[/vba]HTH
-
- #12
Re: Force Code To Wait Until Userform Is Closed
Oh, great, it worked out both ways! First I tried to solve the problem the way bradles suggested, then the way you suggested it. The latter seems to be easier to implement, this is why I'll go that way.For all those experiencing a simillar problem, wanting to go the other way, this is the solution that bradles recommended and worked with me, too:
a) right at the end of my code I place the load command for the user form. Note: I load it but I do not show it.
b) Into the Userform Sub 'UserForm_Initialize()' I placed the 'decision-making' code (with me it was a 'Select Case'-statement), that either calls another sub OR shows the userform so the user can interact with the userform itself.
Dim intVariable Select case intVariable Case 0 Call someSub Case 1 Call anotherSub Case Else UserForm1.Show End SelectJust make sure you don't have no code like stated below BEFORE the proper and full execution of 'UserForm_Initialize()' has come to an end.
schneidertheried75.blogspot.com
Source: https://forum.ozgrid.com/forum/index.php?thread/57244-force-code-to-wait-until-userform-is-closed/
0 Response to "Excel Vba Userform Shows but Code Still Continues"
Post a Comment