Selecting Yes/No Options on UserForm Not Working

[HTSCF Fareha]

I have the following sub which should enable the user to select an answer for a number of Yes/No questions by selecting the relevant optionbutton on the UserForm, which will then populate the respective content control. Each Yes/No pair are grouped.

At the moment I'm getting a Run Time error 91: Object variable or with block object not set, but no hint on where this might be failing. I'm hoping that I'm not too far off what I wish to achieve?


Option Explicit

Dim oRng As Range
Dim HasFocus As String
Dim occ As ContentControl
Dim oCtr As MSForms.control
Dim oDoc As Document

Private Sub UserForm_Initialize()

' Sort out the options for yes/no questions

For Each oCtr In Me.Controls

Select Case TypeName(oCtr)

Case "OptionButton"
For Each occ In oDoc.ContentControls

With Me.Controls(oCtr.Name).Value

Select Case Me.Controls(oCtr.Name).GroupName

Case "Delivered"
Set occ = ActiveDocument.SelectContentControlsByTitle("Delivered").Item(1)

Select Case Me.Controls(oCtr.Name).Caption
Case "Yes"
occ.Range.Text = "YES"
Case "No"
occ.Range.Text = "NO"
End Select

Case "Evidence"
Set occ = ActiveDocument.SelectContentControlsByTitle("Evidence").Item(1)

Select Case Me.Controls(oCtr.Name).Caption
Case "Yes"
occ.Range.Text = "YES"
Case "No"
occ.Range.Text = "NO"
End Select

Case "Employee"
Set occ = ActiveDocument.SelectContentControlsByTitle("Employee").Item(1)

Select Case Me.Controls(oCtr.Name).Caption
Case "Yes"
occ.Range.Text = "YES"
Case "No"
occ.Range.Text = "NO"
End Select

Case "Funds"
Set occ = ActiveDocument.SelectContentControlsByTitle("Funds").Item(1)

Select Case Me.Controls(oCtr.Name).Caption
Case "Yes"
occ.Range.Text = "YES"
Case "No"
occ.Range.Text = "NO"
End Select

End Select
End With
Next occ
End Select
Next

lbl_Exit:
Exit Sub
End Sub

[SamT]

Private Sub UserForm_Initialize() Runs before every other event/code on the Form. Are you sure everything else is hard coded? For example: All OptionButton states are set during Design time.

Usually the Initialize sub is used to set the State of the Form to match the status of the Document. Not the other way around.

[HTSCF Fareha]

The default states I've set on the UserForm itself and each is set to "No". I've double checked that each option group and individual option is named correctly too.

Would I be correct in saying that this sub is better placed in the main Module rather than the UserForm?

[HTSCF Fareha]

I've realised that I had not uploaded my template.

[SamT]

Looking at the code, there is no "Document" class in Excel. Try oDoc as Word.Document or oDoc as Object

Also You haven't Set oDoc = to anything


Why not hard code the Word Template with defaults? In any case, I would NOT use the UserForm's Controls to set the Word Doc's defaults.

If there was some reason I MUST set the Doc's values at startup, I would set both the Doc's values and the UF's values with the same Procedure since they are identical.

[gmayor]

I've realised that I had not uploaded my template.That helps :yes
There was a lot of unnecessary code in the user form and you appear to have made up your own syntax for writing to named content controls. See attached

[HTSCF Fareha]

Many thanks Graham!


...you appear to have made up your own syntax for writing to named content controls.

Hmm, I'm guessing Microsoft won't be needing my suggestions? :giggle

Thinking about my use of option buttons, is this technically correct to use in this situation or should I really be using checkboxes instead? I've only shown three question options for the benefit of trying to get this to work, but my final form will have eleven.

[gmayor]

I don't think there is a 'technically correct' way to make such choices. Option buttons are best when there are two or more choices. As long as the pairs are grouped correctly, this will work.