Procedure runs out of order when validation invoked 1

[RP1America]

This userform procedure seems to run out of order when the validation peice (first two if statements) is invoked.

Private Sub cmdSubmit_Click()

    If optNoAct.Value = False And optNoFund.Value = False Then
        frmCASAT.Hide
    MsgBox "Please Select Contract Termination Type", 0, "Field Must Be Valued"
        frmCASAT.Show
    End If
    
    If txtSection.Value = "" Then
        frmCASAT.Hide
    MsgBox "Please Value Contract Termination Section", 0, "Field Must Be Valued"
        frmCASAT.Show
    End If
    
Dim strYearAgoToday As String
strYearAgoToday = DateAdd("yyyy", -1, Date)
Dim strLastYear As String
strLastYear = DatePart("yyyy", strYearAgoToday)
        
    With ActiveDocument
        .Bookmarks("CurrentDate").Range.Text = Format(Date, "MMMM d, yyyy")
        .Bookmarks("CTS").Range.Text = txtSection.Value
        .Bookmarks("Days30").Range.Text = DateAdd("d", 30, Date)
        .Bookmarks("Current1").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current2").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current3").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current4").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current5").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Prior1").Range.Text = strLastYear
        .Bookmarks("Prior2").Range.Text = strLastYear
        .Bookmarks("Prior3").Range.Text = strLastYear
        .Bookmarks("Prior4").Range.Text = strLastYear
    End With


    
    If optNoAct = True Then
        With ActiveDocument
            .Bookmarks("NeverFunded").Range.Text = ""
        End With
    ElseIf optNoFund = True Then
        With ActiveDocument
            .Bookmarks("NoActivity").Range.Text = ""
        End With
    End If


    If chkNonDis = False And chkAnnRep = False And chkForm5500 = False Then
        With ActiveDocument
            .Bookmarks("PFRK").Range.Text = ""
        End With
    ElseIf chkNonDis = False And chkAnnRep = True And chkForm5500 = True Then
        With ActiveDocument
            .Bookmarks("NonDis1").Range.Text = ""
            .Bookmarks("NonDis2").Range.Text = ""
        End With
    ElseIf chkNonDis = True And chkAnnRep = False And chkForm5500 = True Then
        With ActiveDocument
            .Bookmarks("NoAnnRep").Range.Text = "; and"
            .Bookmarks("AnnRep1").Range.Text = ""
            .Bookmarks("AnnRep2").Range.Text = "."
        End With
    ElseIf chkNonDis = True And chkAnnRep = True And chkForm5500 = False Then
        With ActiveDocument
            .Bookmarks("Form55001").Range.Text = ""
            .Bookmarks("Form55002").Range.Text = "."
        End With
    ElseIf chkNonDis = False And chkAnnRep = False And chkForm5500 = True Then
        With ActiveDocument
            .Bookmarks("NonDis1").Range.Text = ""
            .Bookmarks("AnnRep1").Range.Text = ""
            .Bookmarks("AnnRep2").Range.Text = "."
            .Bookmarks("NonDis2").Range.Text = ""
        End With
    ElseIf chkNonDis = False And chkAnnRep = True And chkForm5500 = False Then
        With ActiveDocument
            .Bookmarks("NonDis1").Range.Text = ""
            .Bookmarks("Form55001").Range.Text = ""
            .Bookmarks("AnnRepOnly").Range.Text = ""
        End With
    ElseIf chkNonDis = True And chkAnnRep = False And chkForm5500 = False Then
        With ActiveDocument
            .Bookmarks("NonDisOnly").Range.Text = ""
            .Bookmarks("Form55002").Range.Text = "."
        End With
    End If
    
    Application.Visible = True
            
    Unload Me
    
End Sub

Validator states that either optNoAct or optNoFund must be selected. Another states that text must be entered in txtSection.

When these things are done, everything works fine. However, when say no text is entered in txtSection, things go haywire. The validator itself seems to work okay, hiding the form, showing the message box, then showing the form again. The issue comes when you then do enter text into txtSection and click Submit, there is an error ("Run-time error '5941': The requested member of the collection does not exist.").

When debug is selected, this line is highlighted:

.Bookmarks("CurrentDate").Range.Text = Format(Date, "MMMM d, yyyy")

It "does not exist" because the macro has already entered today's date in "CurrentDate" and therefore has deleted that bookmark.

My question is, why does the code seem sort of 'loop' through the rest of the procedure during validating the selections, then once those selections are made, the code has already been through where it is erroring out now?


Thanks in advance!!

 

[fumei]

1. "The validator itself seems to work okay, hiding the form, showing the message box, then showing the form again. "

Why are you hiding the userform? Why not just show the message and Exit the cmdSubmit_Click procedure? The userform will still be there, and more importantly...the other instructions will not execute.

2. The other instructions have executed.

If txtSection.Value = "" Then
   frmCASAT.Hide
   MsgBox "Please Value Contract Termination Section", 0, "Field Must Be Valued"
   frmCASAT.Show
End If

OK, so if txtSection is "", the userform is hidden, the messagebox is displayed, the userform is shown again, and.....

    With ActiveDocument
        .Bookmarks("CurrentDate").Range.Text = Format(Date, "MMMM d, yyyy")
        .Bookmarks("CTS").Range.Text = txtSection.Value
        .Bookmarks("Days30").Range.Text = DateAdd("d", 30, Date)
        .Bookmarks("Current1").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current2").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current3").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current4").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Current5").Range.Text = DatePart("yyyy", Date)
        .Bookmarks("Prior1").Range.Text = strLastYear
        .Bookmarks("Prior2").Range.Text = strLastYear
        .Bookmarks("Prior3").Range.Text = strLastYear
        .Bookmarks("Prior4").Range.Text = strLastYear
    End With

is immediateley executed.

AND, since you are not putting the text value into (inside) the bookmarks, yes, the bookmarks are deleted, and thus you get: Run-time error '5941': The requested member of the collection does not exist

Solution?

Solution A) On error display message and exit the procedure.

If txtSection.Value = "" Then
    MsgBox "Please Value Contract Termination Section", 0, "Field Must Be Valued"
    txtSection.SetFocus
    Exit Sub
End If

The userform is still there, and focus is moved to txtSection. Oh, and btw, it is better to use the actual property you want, in this case txtSection.Text, rather than txtSection.Value.

True, in this particular case they are equal. However, .Value is always the default property of an object. Unless you know - for sure - what each default property of an object is, it is better to be explicit. You want the Text...get the .Text.

Solution B) Use a procedure that places text INSIDE a bookmark, ie. the bookmark is NOT deleted. Technically it is anyway, it is simply recreated with the newer value (text). Like this:

Sub FillBM(strBM As String, strText As String)
Dim r As Range
   Set r = ActiveDocument.Bookmarks(strBM).Range
   r.Text = strText
   ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End Sub

Now your bookmark filling instruction would be (for example):

Call FillBM("CTS", txtSection.Value)
Call FillBM("Days30", DateAdd("d", 30, Date))
Call FillBM("Current1", DatePart("yyyy", Date))
Call FillBM("Current2", DatePart("yyyy", Date))

etc. etc.

Now, the bookmark remains and you can insert any text value you want, changing it as many times as you want. The bookmark remains.

However, unless you do Solution A, those instructions WILL still execute, which seems a waste, since you do not actually want them until things are good, yes?

So I would use A, but then also use B to put the text into the bookmarks. I do not know if it is required, but by putting the text INTO the bookmarks, you can run the userform again (as many times as you like) and it will work because the bookmarks are still there.


unknown

 

[RP1America]

Thank you so much fumei!

I feel silly for not seeing why the rest of the procedure was executing.

Regardless, thank you for your in-depth explanation! This is exactly what I needed.

 

[fumei]

I feel silly for not seeing why the rest of the procedure was executing."

Do not feel silly. It is normal human anthropomorphizing. We think that code actually "thinks". We stopped at the right place - textbox is empty...end of story - and forget that code execution does EXACTLY what we tell it to do.

If you do not say "stop", it will not stop.


unknown