Solved: OptionButton Error Handling

[neilholmes]

Hi everyone,

Hoping someone will be able to help me.

I have built a word document that runs a userform on launch. In the userform the user has the option (using option buttons) to select the content that needs to be added to document. This works fine.

On some userforms I have built more than one selection into frames, so for example the user will see:

Question 1: Answer, A, B or C.
Question 2: Answer, A, B or C.

I have found that with this coding should the user forget to make a selection, the code will error.

Can anyone help? Basically what I would like to do is add error handling so that if the user mistakenly forgets to make a selection the code will recognise this and a message will come up saying sometime like, "You have made no selection".

The code then returns to the userform.

The selection code I am using is this :

Dim Doc1 As Document, StrVar1 As String
StrVar1 = ""

If OptionButton1 = True Then
StrVar1 = "1"
ElseIf OptionButton2 = True Then
StrVar1 = "2"
ElseIf OptionButton3 = True Then
StrVar1 = "3"
End If

Set Doc1 = Documents.Open("C:\My Documents\Report\VARIABLE" & StrVar1 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("BOOKMARK1").Range.Paste


If I have more than one selection option in the same userform I simply change the Doc and StrVar. For example:


Dim Doc2 As Document, StrVar2 As String
StrVar2 = ""


Hope you can help. Thanks in advance everyone.

http://www.vbaexpress.com/forum/images/icons/icon7.gif

- Neil

[gmaxey]

How are you executing the code? With a command button on the fomr? I suggest that you neve let the error occur in th first place:

Private Sub CommandButton1_Click()
Dim Doc1 As Document, StrVar1 As String
StrVar1 = ""

If OptionButton1 = True Then
StrVar1 = "1"
ElseIf OptionButton2 = True Then
StrVar1 = "2"
ElseIf OptionButton3 = True Then
StrVar1 = "3"
End If
If Not StrVar1 = "" Then
Set Doc1 = Documents.Open("C:\My Documents\Report\VARIABLE" & StrVar1 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("BOOKMARK1").Range.Paste
Else
MsgBox "You did not select an option"
End If
End Sub

[neilholmes]

Thanks Greg,

Sorry, yes the code is executed by a commandbutton. The code looks like what I need. Many thanks for your help with this.

- Neil

[neilholmes]

I have come up against another problem and was hoping you would be able to help once again. Your amendments to my code work perfectly, however when I have multiple frames including multiple optionbuttons I run into problems.

For example, my code now looks something like this:

' OPTION 1
Dim Doc1 As Document, StrVar1 As String
StrVar1 = ""

If OptionButton1 = True Then
StrVar1 = "1"
ElseIf OptionButton2 = True Then
StrVar1 = "2"
ElseIf OptionButton3 = True Then
StrVar1 = "3"
End If

If Not StrVar1 = "" Then
Set Doc1 = Documents.Open("C:\My Document\VARIABLE" & StrVar1 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("BOOKMARK1").Range.Paste

Else
MsgBox "You did not select an option"
End If

' OPTION 2
Dim Doc2 As Document, StrVar2 As String
StrVar2 = ""

If OptionButton4 = True Then
StrVar2 = "1"
ElseIf OptionButton5 = True Then
StrVar2 = "2"
ElseIf OptionButton6 = True Then
StrVar2 = "3"
End If

If Not StrVar2 = "" Then
Set Doc1 = Documents.Open("C:\My Document\VARIABLE2_" & StrVar2 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("VARIABLE2").Range.Paste

Else
MsgBox "You did not select an option"
End If

End Sub



In the above example if the user forgets to select option 1, but selects option 2, the error message for option 1 will show while option 2 will execute as normal. If the user then returns to the userform correcting their error making a selection under option 1, on clicking the commandbutton the code will try to execute both option 1 and option 2 for a second time. This then causes an error.

(Hope that makes sense)

Is there a way to ensure that the macro only runs when an optionbutton in each frame is selected? :dunno

Many thanks once again.

- Neil

[gmaxey]

Neil,

Glad I could help. There are many ways to handle this sort of thing. The best approach IMHO is to ensure the error "can't" occur.

The simpliest (easiest) way is to "get out" at first sign of trouble:

Option Explicit
Private Sub CommandButton1_Click()
Dim Doc1 As Document, StrVar1 As String
StrVar1 = ""
If OptionButton1 = True Then
StrVar1 = "1"
ElseIf OptionButton2 = True Then
StrVar1 = "2"
ElseIf OptionButton3 = True Then
StrVar1 = "3"
End If
If Not StrVar1 = "" Then
Set Doc1 = Documents.Open("C:\My Document\VARIABLE" & StrVar1 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("BOOKMARK1").Range.Paste
Else
MsgBox "You did not select a group 1 option"
GoTo lbl_Exit
End If
' OPTION 2
Dim Doc2 As Document, StrVar2 As String
StrVar2 = ""

If OptionButton4 = True Then
StrVar2 = "1"
ElseIf OptionButton5 = True Then
StrVar2 = "2"
ElseIf OptionButton6 = True Then
StrVar2 = "3"
End If
If Not StrVar2 = "" Then
Set Doc1 = Documents.Open("C:\My Document\VARIABLE2_" & StrVar2 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("VARIABLE2").Range.Paste
Else
MsgBox "You did not select a group 2 option"
GoTo lbl_Exit
End If
lbl_Exit:
Exit Sub
End Sub


My preferred approach (especially when someone is paying for my time) is to ensure trouble can't occur in the first place. You can do this by disabling your command button until conditons are met:

Option Explicit
Private Sub CommandButton1_Click()
Dim Doc1 As Document, StrVar1 As String
StrVar1 = ""
If OptionButton1 = True Then
StrVar1 = "1"
ElseIf OptionButton2 = True Then
StrVar1 = "2"
ElseIf OptionButton3 = True Then
StrVar1 = "3"
End If
Set Doc1 = Documents.Open("C:\My Document\VARIABLE" & StrVar1 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("BOOKMARK1").Range.Paste
' OPTION 2
Dim Doc2 As Document, StrVar2 As String
StrVar2 = ""

If OptionButton4 = True Then
StrVar2 = "1"
ElseIf OptionButton5 = True Then
StrVar2 = "2"
ElseIf OptionButton6 = True Then
StrVar2 = "3"
End If
Set Doc1 = Documents.Open("C:\My Document\VARIABLE2_" & StrVar2 & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc1.Range.Copy
Doc1.Close savechanges:=False
ActiveDocument.Bookmarks("VARIABLE2").Range.Paste
End Sub

Private Sub OptionButton1_Click()
If EnableCB Then CommandButton1.Enabled = True
End Sub

Private Sub OptionButton2_Click()
If EnableCB Then CommandButton1.Enabled = True
End Sub

Private Sub OptionButton3_Click()
If EnableCB Then CommandButton1.Enabled = True
End Sub

Private Sub OptionButton4_Click()
If EnableCB Then CommandButton1.Enabled = True
End Sub

Private Sub OptionButton5_Click()
If EnableCB Then CommandButton1.Enabled = True
End Sub

Private Sub OptionButton6_Click()
If EnableCB Then CommandButton1.Enabled = True
End Sub

Private Sub UserForm_Initialize()
CommandButton1.Enabled = False
End Sub

Function EnableCB() As Boolean
Dim bGroup1 As Boolean
Dim bGroup2 As Boolean
bGroup1 = False
bGroup2 = False
Select Case True
Case OptionButton1.Value, OptionButton2, OptionButton3
bGroup1 = True
End Select
Select Case True
Case OptionButton4, OptionButton5, OptionButton6
bGroup2 = True
End Select
EnableCB = bGroup1 = bGroup2
End Function

[neilholmes]

Thanks once again Greg. I will give the options a try first thing tomorrow. I agree with you so will try out the second option and let you know how I get on.

- Neil

[neilholmes]

Hi Greg, (once again).

Your coding works really well. However I have one final question. How do I expand the code if my userform includes 3 or (many) more (frames) options.

I expanded the code thinking I simply needed to add additional elements should there be a further option. However this didn't work:

(I haven't posted the complete code as it is now rather large, if you need to see it let me know).


Function EnableCB() As Boolean
Dim bGroup1 As Boolean
Dim bGroup2 As Boolean
Dim bGroup3 As Boolean
Dim bGroup4 As Boolean
Dim bGroup5 As Boolean
Dim bGroup6 As Boolean

bGroup1 = False
bGroup2 = False
bGroup3 = False
bGroup4 = False
bGroup5 = False
bGroup6 = False

Select Case True
Case OptionButton1, OptionButton7
bGroup1 = True
End Select
Select Case True
Case OptionButton2, OptionButton8
bGroup2 = True
End Select
Select Case True
Case OptionButton3, OptionButton9
bGroup3 = True
End Select
Select Case True
Case OptionButton4, OptionButton10
bGroup4 = True
End Select
Select Case True
Case OptionButton5, OptionButton11
bGroup5 = True
End Select
Select Case True
Case OptionButton6, OptionButton12
bGroup6 = True
End Select

EnableCB = bGroup1 = bGroup2 = bGroup3 = bGroup4 = bGroup5 = bGroup6
End Function


Am I missing something ? As you can see in the above option the userform this time has 6(!!!!) frames with two optionbuttons in each.

Would you mind helping once again ?

Thanks in advance.

- Neil

[gmaxey]

Well that wasn't my best piece of code.

Replace:
EnableCB = bGroup1 = bGroup2 = bGroup3 = bGroup4 = bGroup5 = bGroup6

With:
Select Case False
Case bGroup1, bGroup2, bGroup3, bGroup4, bGroup5, bGroup6
EnableCB = False
Case Else
EnableCB = True
End Select

[gmaxey]

Neil,

While I don't really understand it, it seems the following will also work:

EnableCB = bGroup1 * bGroup2 * bGroup3 * bGroup4 * bGroup5 * bGroup6

[neilholmes]

Thanks Greg, I just tried the first option and it now works perfectly. Once again many thanks for your help on this one. I am still very new to vba so having a resource like this forum is a huge bonus.

I'd like to hope that this problem is solved. Will get back to you if I have any more problems.

- Neil

[gmaxey]

Neil,

Good. You're welcome.