Consulting

Results 1 to 10 of 10

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

  1. #1
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location

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

    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!

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    'Variable not defined' Error = oRng has not been "Dim"ed
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    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

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Sub EnterBut_click does nothing for Subs Button1_click and Button2_Click
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    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!

  7. #7
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  8. #8
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    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

  9. #9
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  10. #10
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    I hadn't realised that I was making up my own syntax.

    That's solved the problem. Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •