Three other possibilities I can think of (from just seeing your code):
______________________________________________________________________________________________________________________
[Possibility B] "sheet 5" does not exist.
Worksheets("sheet5") is relying on a sheet to be named "sheet5" (Case sensitive)
By default, sheets are named with a capital S. So if the tab is actually named "Sheet5", change your code to:
VBA Code:
Private Sub CommandButton2_Click()
Dim wks As Worksheet
Dim additem As Range
Dim rng As Range
Set wks = Sheet5
Set rng = Worksheets("Sheet5").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
rng.Offset(0, 1) = Label1.Caption
rng.Offset(0, 2) = Label2.Caption
rng.Offset(0, 3) = Label3.Caption
.....
(Unlike the "a" in Cells(), which case sensitivity doesn't matter.)
______________________________________________________________________________________________________________________
[Possibility C] The sheet code name is not the same as the sheet name.
Temporarily put this sub in in a normal module and run it.
VBA Code:
Sub Test__SheetName_is_SheetCodeName()
MsgBox Worksheets("sheet5").Name = Worksheets("sheet5").CodeName
End Sub
If it returns
False, then you probably have something like this in your Worksheets list in the VBA project module for that Workbook.
From the start of this Workbook's "life", you have either renamed or deleted (maybe both) sheets, so that the sheet code names have become off-sync with the sheet names (the names that you see in the sheet tab names).
As an analogy,
Sheet code name is to absolute cell reference as sheet name is to named range.
When you did the following assignment,
you set wks equal to the sheet
code name. If a later line of code down in the sub (or one of the subs/functions it callses), your code relies on the result that
Sub Test__SheetName_is_SheetCodeName is
True.
If you want to set it to the sheet name, you need to do the following instead (you need quotes)
VBA Code:
Set wks = Worksheets("sheet5")
To fix this issue, you need to therefore set wks equal to the sheet name (not sheet
code name)
VBA Code:
Private Sub CommandButton2_Click()
Dim wks As Worksheet
Dim additem As Range
Dim rng As Range
Set wks = Worksheets("sheet5")
Set rng = wks.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
rng.Offset(0, 1) = Label1.Caption
rng.Offset(0, 2) = Label2.Caption
rng.Offset(0, 3) = Label3.Caption
.....
______________________________________________________________________________________________________________________
[Possibility D] You need the last non-blank row in the entire worksheet, rather than just the last non-blank row in
Column A.
By the looks of this portion of your code,
VBA Code:
Set rng = Worksheets("sheet5").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
rng.Offset(0, 1) = Label1.Caption
rng.Offset(0, 2) = Label2.Caption
rng.Offset(0, 3) = Label3.Caption
It relies on the fact that
Column A is the column whose last used row is the largest row number. But if this is
not necessarily the case, then you need to put some type of function like the following (many people prefer Range.Find, but if the following doesn't slow down your form, it's definitely much "safer" to use) in a standard code module permanently for future use in your Workbook.
VBA Code:
Sub Test__Last_Blank_Row_In_Sheet()
MsgBox Last_Blank_Row_In_Sheet(ActiveSheet.Name)
End Sub
Function Last_Blank_Row_In_Sheet(sheetName As String)
Dim numberOfUsedColumns As Integer, previousLargestLastRow As Long, i As Integer
With Worksheets(sheetName)
numberOfUsedColumns = .UsedRange.Columns.Count + .UsedRange.Column - 1
previousLargestLastRow = 1
i = 1
Do While i <= numberOfUsedColumns
If .Cells(Rows.Count, i).End(xlUp).Row > previousLargestLastRow Then previousLargestLastRow = .Cells(Rows.Count, i).End(xlUp).Row
i = i + 1
Loop
End With
Last_Blank_Row_In_Sheet = previousLargestLastRow
End Function
And therefore, your original code in the userform would need to be changed to:
VBA Code:
Private Sub CommandButton2_Click()
Dim wks As Worksheet
Dim additem As Range
Dim rng As Range
Set wks = Sheet5
Set rng = Worksheets("sheet5").Range("A" & Last_Blank_Row_In_Sheet("sheet5") + 1)
rng.Offset(0, 1) = Label1.Caption
rng.Offset(0, 2) = Label2.Caption
rng.Offset(0, 3) = Label3.Caption
.....
And by the way, note that:
and
are equivalent. (Just to shorten your code from here on out, if you want!)
