0

I'm a VBA NewBe (this is my first post asking a question on any VBA help site) working on a Property/Expense Management application in excel. the ComboBoxes in question are used to select worksheets based on a Property ID, propID, in cmbPropID based on a range in a "Control" worksheet and the worksheet year, wsYr, in cmbYear determined by the individual propID's beginning year, wsStartYr, and the current calendar year, wdCurYear. I am able to select/activate and view all worksheets of the first property I select. However, when I select another property to work on the application crashes with Debug error: 9 - Script out of range! I have been going blind for over a week searching for a solution without success.

This issue is important in the overall functionality of the application. I hope someone is able to help me with this. Thanks in advance.

Below are the code sections for the ComboBoxes The first configures cmbPropID 'this is an excert form MultiPage1 Case 2

    'configure cmbPropID  DDL
    wkstControl.Activate
    selectedRow = cmbPropID.ListIndex + 3   'I presume this is 3 instead of 2 because when using 2
                                                        'this throws an error - not sure why
    For Each cPart In wsCntrl.Range(Range("propIDs"), Range("A" & Rows.Count).End(xlUp))
     pAct = wsCntrl.Cells(selectedRow, 11).Value 'Value used to test the "isActive" status of a  RealEstate property location
      With Me.cmbPropID
        If pAct = True And cPart.Value <> "" Then cmbPropID.AddItem cPart.Value 'Never Shows pAct as False ???
            'this presents an issue that if  is inActive of does'nt have worksheets it causes
            ' Debug error "Error: 13,  Type Mismatch"
      End With
    Next cPart

This sub configures the contents of the cmbYears DDL and should reset the DDL's contents when selection a different propID ... see notes in next sub-routine


    Private Sub cmbPropID_Change()
    Dim i
    Dim strValue As String
    wsCntrl.Activate
    pID = ""
    wsA = ""
    wsYr = "" 
    selectedRow = cmbPropID.ListIndex + 2
    
    wsStartYr = wsCntrl.Cells(selectedRow, 13).Text
    
          With cmbYears
                .Clear
    
            For i = wsStartYr To wbCurYear
                pAct = wsCntrl.Cells(selectedRow, 11).Value
                If wbCurYear <> wsStartYr And pAct = True Then
                    .AddItem i
                ElseIf wbCurYear = wsStartYr Then
                    .AddItem wbCurYear
                End If
            Next i
          End With
    lstDsplyUtil1.RowSource = ""
    pID = cmbPropID.Text
    
    End Sub

and finally, cmbYears. The code crashes at Set wsUtil = Worksheets(wsA) when a when cmbPropID changes.


    Private Sub cmbYears_Change()
     wsYr = cmbYears.Text
     wsA = pID & "_" & wsYr
        Debug.Print pID, wsYr, wsA
     Set wsUtil = Worksheets(wsA) 
     lstDsplyUtil1.RowSource = wsA & "!$A$5:$Y$16"
     
    Debug.Print pID, wsYr, wsA
    'Remove after testing
    Label120.Caption = wsA
    Label136.Caption = pID
    Label138.Caption = wsYr
    Label134.Caption = lstDsplyUtil1.RowSource
    
     wsUtil.Activate
     
    End Sub
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
hermannjt
  • 37
  • 8

1 Answers1

0

I went back to troubleshooting the Crash Issue after posting my Question. I developed the habit of creating Labels on the various UserForms, assigned their .Caption value equal to the the various Variable values, and used Debug.Print to track those Values in the Immediate Window.

What I noticed was that when Debug threw its Error the Immediate Window showed the new pID Value after the cmbPropID_Change() Event to be the same as BEFORE the cmbPropID_Change Event and the cmbPropID.ListIndex = -1 when it should have been >=0 depending on the selection in cmbPropID. This lead me think that the problem was with the cmbPropID_Change() Event configuration. However, no matter what changes I made to the cmbPropID_Change() Event configuration the issue remained.

I began looking at the cmbYears_Change() Event configuration where the Error happens and it occurred to me that I could pre-Trap and thereby avoid the Error by encapsulating cmbYears_Change() code in a set of If Then ElseIf statements like this:

    If cmbYears.ListIndex <> -1 Then
      'cmbYears' original code
      ElseIf cmbYears.ListIndex >= 0 Then
      'cmbYears' original code
    End If

This worked perfectly and this is the final code!

    Private Sub cmbYears_Change()

    If cmbYears.ListIndex <> -1 Then
        pID = cmbPropID.Text
        wsYr = cmbYears.Text
        wsA = pID & "_" & wsYr
        lstDsplyUtil1.RowSource = wsA & "!$A$5:$Y$16"
    Debug.Print pID, wsYr, wsA
    Set wsUtil = Worksheets(wsA)
    wsUtil.Select
  ElseIf cmbYears.ListIndex >= 0 Then
    pID = cmbPropID.Text
    wsYr = cmbYears.Text
    wsA = pID & "_" & wsYr
    lstDsplyUtil1.RowSource = wsA & "!$A$5:$Y$16"
    Debug.Print pID, wsYr, wsA
    Set wsUtil = Worksheets(wsA)
    wsUtil.Select
End If

End Sub

Issue Resolved!

hermannjt
  • 37
  • 8