Form Closses but written conditions not implemented

Wilfred_E

New Member
Joined
May 9, 2019
Messages
44
Hello:

I have the following two codes in a button: The (first code) aims to submit value of option button into worksheet cell:

For Each FormControl In Me.Controls

'Check only OptionButtons
If TypeName(FormControl) = "OptionButton" Then

'Check the status of the OptionButton
If FormControl.Value = True Then

'Set a variable equal to the Caption of the selected OptionButton
OptionButtonValue = FormControl.Caption

'We found the selected OptionButton so exit the loop.

Exit For
End If
End If
Next

'Store input in the worksheet
Sheets("Answer Sheet").Range("E80").Value = OptionButtonValue


To ensure an option button is selected before proceeding to next form (being, ScoreBoards), i have the following code (second code):

Dim cnt As Integer
For Each ctl In Me.Controls
If TypeName(ctl) = "OptionButton" Then
If ctl.Value = True Then cnt = cnt + 1
End If
Next ctl
If cnt = 0 Then MsgBox "Hello " & CStr(ThisWorkbook.Sheets("AccessReg").Range("D630").Value) & ", you have not selected an answer! Please select an answer to proceed to next question. Thank you.", vbInformation, "Please select an answer!" Else ScoreBoards.Show


MY CHALLENGES

Both codes above exists in my forms i.e. questions 1, 2, 3,...respectively, but can't seem to get the second code (that, which ensures an option button is selected before next form can be opened) to work by adding 'unload me' to the end of it, yet i want the form closed before proceeding to next. Adding 'unload me', pop-up the msgbox (which tells me to select an answer) but when i clicked okay on the msgbox, it closes the form (Question1) instead of returning me to same form to ensure an answer is clicked, then proceed to next form (Question2). However, when i remove the 'unload me', things work fine i.e. the msgbox popup when selection not made, returns to same form when okay on msgbox is clicked, and opens next form when selection made.

What i really want is: i want the second code above (which ensures an option button is selected before next form can be opened) to work as programmed and each form closed before proceeding to the next form.

Thank you in advance

PS:

The concept summary is:

- On a userform (Question1), select an option button and submit the value to worksheet
- Ensure an option button is selected:

- if selected and button clicked, the next form(being Question2) should open
- if not selected and button clicked, the msgbox (which tells me to select an answer), should popup
- clicking okay on the msgbox, should return me to same form (Question1) so that i can select an option and proceed.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Replace this:
If cnt = 0 Then MsgBox "Hello " & CStr(ThisWorkbook.Sheets("AccessReg").Range("D630").Value) & ", you have not selected an answer! Please select an answer to proceed to next question. Thank you.", vbInformation, "Please select an answer!" Else ScoreBoards.Show

by this:
VBA Code:
    If cnt = 0 Then
        MsgBox "Hello " & CStr(ThisWorkbook.Sheets("AccessReg").Range("D630").Value) & ", you have not selected an answer! Please select an answer to proceed to next question. Thank you.", vbInformation, "Please select an answer!"
    Else
        Call Proceed(Me, ScoreBoards)
    End If

and paste in a standard module this
VBA Code:
Public Sub Proceed(ByRef argCurrentUsf As Object, ByRef argNextUsf As Object)
    If Not argCurrentUsf Is Nothing And Not argNextUsf Is Nothing Then
        Unload argCurrentUsf
        argNextUsf.Show
    End If
End Sub
 
Upvote 0
T
Replace this:
If cnt = 0 Then MsgBox "Hello " & CStr(ThisWorkbook.Sheets("AccessReg").Range("D630").Value) & ", you have not selected an answer! Please select an answer to proceed to next question. Thank you.", vbInformation, "Please select an answer!" Else ScoreBoards.Show

by this:
VBA Code:
    If cnt = 0 Then
        MsgBox "Hello " & CStr(ThisWorkbook.Sheets("AccessReg").Range("D630").Value) & ", you have not selected an answer! Please select an answer to proceed to next question. Thank you.", vbInformation, "Please select an answer!"
    Else
        Call Proceed(Me, ScoreBoards)
    End If

and paste in a standard module this
VBA Code:
Public Sub Proceed(ByRef argCurrentUsf As Object, ByRef argNextUsf As Object)
    If Not argCurrentUsf Is Nothing And Not argNextUsf Is Nothing Then
        Unload argCurrentUsf
        argNextUsf.Show
    End If
End Sub
Bravo, GWteB! It works as suggested and as expected. Thank you very much.
 
Upvote 0
You're welcome and thanks for letting me know.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top