Userform control problem

[Ark68]

I am having difficulty overcoming an "Could not set the Value property. Invalid property value." with the line highlighted in code the code below.

Private Sub UserForm_Initialize()
    
    Dim ws_vh As Worksheet
    Dim ws_ss As Worksheet
    Dim ws_staff As Worksheet
    Dim ws_lists As Worksheet
    Dim qdate As Long
    Dim norec As Long
    Dim st_srchfn As String
    Dim rng_rd As Range
    Dim stn As String
    Dim sft As String
    Dim rng_fac As Range
    Dim i, z1 As Long
    Dim ui As String
    Dim tdia As Long
    Dim tfld As Long
    Dim tcrt As Long
    Dim tother As Long
    Dim dv As String
    Dim tdia_min, tfld_min, tcrt_min, tother_min, tdia_max, tfld_max, tcrt_max, tother_max As Long
    
    
    Set ws_vh = Worksheets("VAR_HOLD")
    Set ws_ss = Worksheets("Sunset")
    Set ws_staff = Worksheets("STAFF_SCHEDULE")
    Set ws_lists = Worksheets("LISTS")
    Set ws_rd = Worksheets("Rental_Data")
    Set ws_fac = Worksheets("Facilities")
    'Set ws_gd = Worksheets("Group_Defaults")
    
    Set rng_rd = ws_rd.Range("A:AZ")
    Set rng_fac = ws_fac.Range("A:H")
        
    qdate = ws_vh.Range("C2")
    qfile = ws_vh.Range("B3")
    
    Unload uf_create_wo1

    st_srchfn = "H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & qfile
    Set wb_base = Workbooks.Open(st_srchfn)
    Set ws_core = wb_base.Worksheets("CORE")
    With ws_core 'Workbooks(qfile)
        norec = WorksheetFunction.Count(ws_core.Range("C:C"))
    End With
    MsgBox qfile & " activated." & Chr(13) & norec & " raw records.", , "DATA FILE"
            
    If ws_core.Range("A2") = "" Then 'data not refined. Clean up database."
        MsgBox "Modifying base file. [Module12]"
        clean_up_database
    End If
    
    ui = MsgBox("Do you want to include hospitality rentals in this workorder package?", vbYesNo, "PACKAGE SCOPE")
    If ui = vbNo Then
        With ws_core
            For i = 2 To norec
                If .Range("K" & i) = "Ln" Then
                    .Range("A" & i) = ""
                    z1 = z1 + 1
                ElseIf .Range("K" & i) = "Pc" And .Range("H" & i) = "Wk" Then
                    .Range("A" & i) = ""
                    z1 = z1 + 1
                ElseIf .Range("K" & i) = "Gn" Then
                    .Range("A" & i) = ""
                    z1 = z1 + 1
                ElseIf .Range("K" & i) = "Gl" Then
                    .Range("A" & i) = ""
                    z1 = z1 + 1
                End If
            Next i
            MsgBox "Ignored hospitality rentals: " & z1
            On Error Resume Next
            Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            'norec = norec - z1
            'MsgBox "norec after exemptions: " & norec
            For i = 2 To norec
                dv = Format(.Range("B" & i), "00000")
                .Range("A" & i) = dv & Format(i - 1, "000")
            Next i
         End With
    End If
     
    With Me
        mbEvents = False
        .MultiPage1.Value = 5
    
        .Caption = Format(qdate, "dddd, dd-mmm-yyyy")
        .sunset.Value = Format(WorksheetFunction.VLookup(qdate, ws_ss.Range("A:D"), 2, False), "hh:mm am/pm")
        .offset.Value = Format(WorksheetFunction.VLookup(qdate, ws_ss.Range("A:D"), 3, False), "hh:mm am/pm")
    
    End With
    
    mbEvents = True
    
End Sub

This is a userform initialization routine. This "userform uf_create_wo2" is called from a commandbutton from a previously opened userform, "uf_create_wo1" "uf_create_wo1" is closed in this routine. Both userforms are in opened workbook "sports15b.xlsm".
This routine opens up a second workbook defined by variable qfile. Once this is open, worksheets "ws_core" is defined and some simple analysis of data is performed.
The user is faced with a vbYesNo question (ui1). (blue line)
If the user answers no to the prompt, the if vbno code is run, and no error is encountered.
However, if the user selects vbyes, once the code reaches the line in red, I encounter the error, and the debug takes me back to the commandbutton code from the previous userform "uf_create_wo1" code.

I am not certain what is creating the error in the one case (vbyes) but not the other (vbno). Any assistance will be greatly appreciated. If I can provide additional details to help, please ask.

 

[NdNoviceHlp]

If ui = "vbNo" Then

HTH. Dave

 

[Ark68]

Thanks Dave. I made the change hoping it was as simple as that ...
But no. Same error. In fact now I get the error regardless of whether the user selects yes or no to the prompt. In fact the "If ui="vbno" then" code is skipped even when the no button was selected. It does work though without the quotes around vbno.

 

[Rick Rothstein]

Thanks Dave. I made the change hoping it was as simple as that ...
But no. Same error. In fact now I get the error regardless of whether the user selects yes or no to the prompt. In fact the "If ui="vbno" then" code is skipped even when the no button was selected. It does work though without the quotes around vbno.

You declared the ui variable as String, but the return value from the MessageBox is an integer value... try declaring the ui variable as Long and see if that works for you.

 

[Ark68]

Hi Rick, thanks for pointing that out to me. Regrettably, I still get the error.

 

[Rick Rothstein]

The user is faced with a vbYesNo question (ui1). (blue line)
If the user answers no to the prompt, the if vbno code is run, and no error is encountered.
However, if the user selects vbyes, once the code reaches the line in red, I encounter the error, and the debug takes me back to the commandbutton code from the previous userform "uf_create_wo1" code.

I am not certain what is creating the error in the one case (vbyes) but not the other (vbno). Any assistance will be greatly appreciated. If I can provide additional details to help, please ask.

I would be willing to bet the error is occurring for both vbYes and vbNo; however, if the answer was vbNo, the routine you run enables On Error Resume Next and never turns it off meaning it remains active when the red line is executed... because it is in effect, the error that I am sure is occurring is hidden from you. Try commenting the On Error Resume Next code line out and run it again answering vbNo to the MessageBox... I'll bet the error will occur. By the way, the way to turn that error checking off is by executing this line of code... On Error GoTo 0

As for the error itself... how many pages are there in your MultiPage control? If only 5, then the value you need to set it to for the last page is 4, not 5... the page indexes are zero-based, not one-based.

 

[Andrew Poulsom]

Have you tried declaring ui as vbMsgBoxResult?

 

[Ark68]

Hi Rick,

With commenting out the "On Error Resume Next", you indeed were correct that I would encounter the error with both vbyes and vbno. With that, your suloution of changing the multipage control value from 5 to 4 resolved the error. So far, so good!!

Thanks!!

Andrew, having had resolved the error with Rick's suggestion, I was unable to test your advice.