Reputation: 141
I have following code
Set wb = ThisWorkbook`
ComboBox7.RowSource = wb.Worksheets("Sheet5").Range("A2", _
Range("A65536").End(xlUp)).Address
If I don't put wb.Sheets("Sheet5").Select
before this line, this code throws error
"Application defined or object-defined error"
I want this code to work without selecting the Sheet5.
If I put ComboBox7.RowSource = wb.Worksheets("Sheet5").Range("A2:A7").Address
then it works fine without selecting the sheet5.
Is there any way to use End(xlUp)
without selecting the sheet?
Upvotes: 1
Views: 2388
Reputation: 1
The other thing is to do this in one line
Private Sub CommandButton1_Click()
Set wb = ThisWorkbook
Set ws = wb.sheets("sheets5")
ComboBox7.RowSource = ws.Range("A2", ws.Range("A65536").End(xlUp)).Address
The second range thinks it is working from the active sheet, you need to tell it otherwise.
Using With is better but this is one line if you are writing code quickly just to get something done.
Upvotes: -1
Reputation: 149325
Yes it is possible.
Logic: Find the last row and then use that to create a range which you can assign to your combobox.
Is this what you are trying?
Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim lRow As Long
Dim rng As Range
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet5")
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set rng = .Range("A1:A" & lRow)
End With
ComboBox5.RowSource = rng.Address
End Sub
Upvotes: 5