Userform control problem

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am having difficulty overcoming an "Could not set the Value property. Invalid property value." with the line highlighted in code the code below.

Rich (BB code):
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,873
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,873
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,552
Messages
5,625,460
Members
416,107
Latest member
AVaes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top