Selecting Option Button Throws Up A 'Variable not defined' Error

[HTSCF Fareha]

I'm now trying to tidy up my existing forms to utilise gmayor's submacro for Bookmarks, but I have an variable error popping up when either Button1 or Button2 are selected on the UserForm. The idea is for the user to only be able to select Button1 or Button2.

I've been staring at the code for so long now I just cannot see what is wrong! :dunno


Option Explicit
'Cancel button
Private Sub CancelBut_Click()
Unload Me
End Sub

Private Sub EnterBut_Click()
Dim onumber As Range, oName As Range
Dim oAddress As Range, oAggrieved As Range, oCommunication As Range
Dim oMethod As Range, oReason As Range, oReviewer As Range

'Check required fields are filled out

If TextBox1.Text = "44200" Then
MsgBox "Enter full number", vbCritical, "Triage Hub"
TextBox1.SetFocus
Exit Sub
End If

If TextBox2.Text = "" Then
MsgBox "Provide full Name", vbCritical, "Triage Hub"
TextBox2.SetFocus
Exit Sub
End If

If TextBox3.Text = "" Then
MsgBox "Provide full Address", vbCritical, "Triage Hub"
TextBox3.SetFocus
Exit Sub
End If

If TextBox4.Text = "" Then
MsgBox "Provide Checker's Name", vbCritical, "Triage Hub"
TextBox4.SetFocus
Exit Sub
End If

If ComboBox1.ListIndex = 0 Then
MsgBox "Provide Communication Method", vbCritical, "Triage Hub"
ComboBox1.SetFocus
Exit Sub
End If

If ComboBox2.ListIndex = 0 Then
MsgBox "State either Enclosed or Attached", vbCritical, "Triage Hub"
ComboBox2.SetFocus
Exit Sub
End If

If TextBox5.Text = "" Then
MsgBox "Enter Reviewer's Details", vbCritical, "Triage Hub"
TextBox5.SetFocus
Exit Sub
End If

'use FillBM function to write to bookmarks
FillBM "number", TextBox1.Text
FillBM "Name", TextBox2.Text
'FillBM "Name1", TextBox2.Text
FillBM "Address", TextBox3.Text
FillBM "Communication", ComboBox1.Value
FillBM "Method", ComboBox2.Value
'FillBM "Reason", ComboBox3.Value
FillBM "Reviewer", TextBox5.Text

Set onumber = Nothing
Set oName = Nothing
'Set oName1 = Nothing
Set oAddress = Nothing
Set oCommunication = Nothing
Set oMethod = Nothing
'Set oReason = Nothing
Set oReviewer = Nothing
Unload Me
lbl_Exit:

'Provide prompt to record that review has taken place
MsgBox "Remember to update the stats to indicate Cyber Crime!", vbExclamation, "Triage Hub"
Exit Sub
End Sub

'Only one of these should be selected
Private Sub Button1_Click()
If Me.Button1.Value = True Then
Set oRng = ActiveDocument.Bookmarks("Reason").Range
oRng.Text = "Lorem ipsum dolor sit amet, consectetur adipiscing elit."
ActiveDocument.Bookmarks.Add "Reason", oRng
End If
End Sub

Private Sub Button2_Click()
If Me.Button2.Value = True Then
Set oRng = ActiveDocument.Bookmarks("Reason").Range
oRng.Text = "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu."
ActiveDocument.Bookmarks.Add "Reason", oRng
End If
End Sub

Private Sub UserForm_Initialize()
Dim myArray() As String
'Use Split function to return a zero based one dimensional array
myArray = Split("- Select Method of Offence -|by text|by social media|by email|" _
& "by letter|by phone", "|")
'Use List method to populate listbox
ComboBox1.List = myArray
ComboBox1.ListIndex = 0

'Redefine list
'Use Split function to return a zero based one dimensional array
myArray = Split("- Select -|enclosed|attached", "|")
'Use List method to populate listbox
ComboBox2.List = myArray
ComboBox2.ListIndex = 0

lbl_Exit:
Exit Sub
End Sub

Private Sub FillBM(strbmName As String, strValue As String)
'Graham Mayor - http://www.gmayor.com
Dim oRng As Range
With ActiveDocument
On Error GoTo lbl_Exit
If .Bookmarks.Exists(strbmName) = True Then
Set oRng = .Bookmarks(strbmName).Range
oRng.Text = strValue
oRng.Font.Color = lngColor
oRng.Bookmarks.Add strbmName
End If
End With
lbl_Exit:
Set oRng = Nothing
Exit Sub
End Sub

[SamT]

'Variable not defined' Error = oRng has not been "Dim"ed

[HTSCF Fareha]

I've added oRng to Dim as below, but it's still showing the same 'variable not defined' error.


Private Sub EnterBut_Click()
Dim onumber As Range, oName As Range
Dim oAddress As Range, oAggrieved As Range, oCommunication As Range
Dim oMethod As Range, oReason As Range, oRng As Range, oReviewer As Range

[SamT]

Sub EnterBut_click does nothing for Subs Button1_click and Button2_Click

[Paul_Hossler]

Scope problem

You Dim-ed oRng inside EnterBut_Click, so it's only 'visible' within that sub

Try making it a module level variable like the <<<<<<<<<<< below, and it will be 'visible' to everything within that module





Option Explicit

Dim oRng as Range ' <<<<<<<<<<<<<<<<<<<<

'Cancel button
Private Sub CancelBut_Click()
Unload Me
End Sub


Private Sub EnterBut_Click()
Dim onumber As Range, oName As Range
Dim oAddress As Range, oAggrieved As Range, oCommunication As Range
Dim oMethod As Range, oReason As Range, oReviewer As Range

[HTSCF Fareha]

That seems to have solved the issue nicely, thanks.


Just a small detail to get the form fully working, how do I invoke error checking to ensure that either Button1 or Button2 have been selected? At the moment this will only check for Button1. Is there a way to check if either have been selected?


If Button1.Value = 0 Then
MsgBox "Provide reason for review", vbCritical, "Triage Hub"
Button1.SetFocus
Exit Sub
End If

Thanks!

[gmayor]

Selecting an option button, unselects other option buttons in the same group, so set the default button to True and then one is always selected.

Change the code to


Private Sub Button1_Change()
If Button1.value = True Then
FillBM "Reason", "Lorem ipsum dolor sit amet, consectetur adipiscing elit."
Else
FillBM "Reason", "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu."
End If
End Sub

[HTSCF Fareha]

I could see that this would work if one had a default option, but my form there isn't a default, so I don't think this would work.

I was thinking something like this might work, but it still fails in its current form with a Run-time error '438': Object doesn't support this property or method.


If Button1.Checked = False And Button2.Checked = False Then
MsgBox "Provide reason for review", vbCritical, "Triage Hub"

Exit Sub
End If

[gmayor]

You can't make up your own syntax


If Button1.value = False And Button2.value = False Then
MsgBox "Provide reason for review", vbCritical, "Triage Hub"
Exit Sub
End If

[HTSCF Fareha]

I hadn't realised that I was making up my own syntax.

That's solved the problem. Many thanks!