Subscript out of range error on userform

Question

Howdy. I am getting a subscript out of range error on a userform.  Based on web and Microsoft forum research I assume there is a problem with the sheet I am trying to access. It is not clear to me what that problem is. 

The Userform launches using a button control on Sheet3. It is intended to edit and add to data on Sheet1. Listbox data on the userform is pulled from lists on Sheet2.

Here is the VBA code in the Userform.

Private Sub UserForm_Activate()

'Define the range of cells rWBSnums on the List Data sheet containing the WBS numbers

    Dim strLists As String

    strLists = "Sheet2"

    Dim intSubProject As Integer

    intSubProjectCol = 3

'    txtSubProjectCol = "C"

    Dim lLastRow As Long

    Dim rFirstWBS As Range

    Dim rLastWBS As Range

    Dim rWBSnums As Range

    lLastRow = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, intSubProjectCol).End(xlUp).Row

The subscript out of range error occurs on the last statement above.  A couple of things to note:

• Sheet2 does exist in this notebook.  See attached picture.
• I changed the Name of the UserForm to CheckbookEntryForm. Does this matter?

I appreciate whatever suggestions you have.

Thank you.

Answer 1

Sheet2 is the code name of the worksheet; the name on the sheet tab is List Data.

So you can use

    With Sheet2

        lLastRow = .Cells(.Rows.Count, intSubProjectCol).End(xlUp).Row

    End With

or

    With Worksheets("List Data")

        lLastRow = .Cells(.Rows.Count, intSubProjectCol).End(xlUp).Row

    End With

Answer 2

I have edited this post since initial posting.

Rows.Count should to be referenced to a worksheet as follows or it references the Active Sheet. The code normally works if the sheet is the ActiveSheet but if not then problems can occur.

lLastRow = ThisWorkbook.Sheets("List Data").Cells(ThisWorkbook.Sheets("List Data").Rows.Count, intSubProjectCol).End(xlUp).Row

I prefer to use the following code example. Note the dot in front of .Rows.Count that ties it back to the With statement.

    With ThisWorkbook.Sheets("List Data")

        lLastRow = .Cells(.Rows.Count, intSubProjectCol).End(xlUp).Row

    End With

However, the error could be due to having changed the Userform Name and your project has lost its link to the event. If the above does not fix the problem then rename the sub to the following.

Private Sub UserForm_ActivateOld()

And then re-create the UserForm_Activate sub and copy the code from the old sub to the new one and then delete the old sub.

Answer 3

OssieMac,

Your recommendations worked. 

First I tried the With statement, still referring to ThisWorkbook.Sheets("Sheet2"). Got the subscript out of range message again. Then I made two changes: I rebuilt the UserForm_Activate sub AND I changed to the usage of ThisWorkbook.Sheets("List Data").

Between those last two changes it now works.  Thank you!

Tsquared3

Answer 4

HansV,

With suggestions from you and OssieMac the statement to find the last row now works.

Thank you!

Tsquared3