Why is the ComboBox loading different data when I am on a different sheet?

Question

I am not understanding whats going on here... While I run the code from sheets(1) being active the userform dropbox loads just fine, but when I shift over to sheets(2) being active it thinks that shhet (1)- row 4 is the end of my data... (Which happens to be the sheet the code is set to a command button on...)

Am I just special???

Here is the code...

    Private Sub UserForm_Initialize()   'sets up combobox's

    PurchUnit.List = Sheets(1).Range("PurchasingSizes" & Range("K41").End(xlUp).Row).Value
    UoM.List = Sheets(1).Range("ConvAbv").Value

    End Sub

So end of the day I need it to recognize my data list independent of which sheet I am on. Also if you could explain why it is doing this... I am so confused by it!

Dude_Scott · Accepted Answer · 2019-03-10T03:47:59.673

1

Reference the Sheet(1) in every range reference like so

Private Sub UserForm_Initialize()   'sets up combobox's
   PurchUnit.List = Sheets(1).Range("PurchasingSizes" & sheet(1).Range("K41").End(xlUp).Row).Value
   UoM.List = Sheets(1).Range("ConvAbv").Value
End Sub

Or better yet

Private Sub UserForm_Initialize()   'sets up combobox's
  With Sheets(1)
     PurchUnit.List = .Range("PurchasingSizes" & .Range("K41").End(xlUp).Row).Value
     UoM.List = .Range("ConvAbv").Value
  End with
End Sub

edited Mar 10 '19 at 03:47

answered Mar 10 '19 at 03:16

Dude_Scott

641
5
9

1

`With Sheet(1)` >> `With Sheets(1)` ? – Tim Williams Mar 10 '19 at 03:43
Something as simple as syntax and placement... Sometimes vba makes me cringe... lol Thank you very much sir! That did the trick, and I have learned from it!!! – Daniel Roy Mar 10 '19 at 04:02

score 1 · Answer 2 · answered Mar 11 '19 at 08:45

As others have pointed out: Range() references the active sheet.
Which you don't want, so you need better references, one way to do this as pointed out by the other answers, is adding Sheets(index) before every Range() instance, whether by adding a With Sheets(index) before the line and putting a . before every Range() instance or by adding Sheets(index) itself before it.

This works for you, for now, but Sheets(index) is not safe to use either, especially when one doesn't know what is referenced.
I used index instead of 1 for a reason: Sheets(index) references a Sheet object of the ActiveWorkbook at a particular index or with that particular name, say Sheets("Sheet 1") would reference the first sheet of the workbook if you hadn't changed its name, and, to top it all off, the index is variable because it changes every time the Sheets are moved inside the Workbook.
Moving your second Sheet to be the first Sheet will result in your code referencing the Sheet which you don't want to be referenced.

To counter this you should use the sheet's Codename which can be viewed and changed inside the VBE. However the Codename cannot be changed at Run-time which helps a great deal when you or your users move Sheets manually around or change their name. Which can always happen by accident.

To sum everything up you should always use ThisWorkbook, which references the Workbook in which the code is running, instead of the implied ActiveWorkbook.
And you should use Codenames of Sheets where applicable, in your case, if I assume you use the English Excel version and haven't changed the Codename it would look like this:

Private Sub UserForm_Initialize()   'sets up combobox's
    PurchUnit.List = Sheet1.Range("PurchasingSizes" & Sheet1.Range("K41").End(xlUp).Row).Value
    UoM.List = Sheet1.Range("ConvAbv").Value
End Sub

For further reading on ActiveWorkbook, ActiveSheet and Select in general I recommend this answer to a different question.

Super informative!!! quick question to it though, if the control button to start the macro is in the active workbook, is it still then better to designate [ThisWorkbook] vrs. implied activeworkbook? — Daniel Roy, Mar 13 '19 at 20:50
@DanielRoy If it’s a long running macro then yes, because users might have multiple workbooks open and, if your macro has ‘DoEvents’ inside a loop, may switch to a different workbook while the macro finishes, which could cause a great deal of pain — Nacorid, Mar 13 '19 at 21:50

score 0 · Answer 3 · answered Mar 10 '19 at 03:14

I'm pretty certain that the Range("K41") being used to construct the argument to your outer range will use the current sheet. The Sheets(1) at the start of the expression will only affect the range it's explicitly tied to, not all ranges withing the parentheses as well.

If you want to use K41 on sheet 1, you should probably be using Sheets(1).Range("K41") instead. In other words:

PurchUnit.List = Sheets(1).Range("PurchasingSizes" & _
    Sheets(1).Range("K41").End(xlUp).Row).Value
'   ^^^^^^^^^^
'   Added this bit.

Why is the ComboBox loading different data when I am on a different sheet?

3 Answers3