Search code examples
excelvbalistcomboboxuserform

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

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

while im on sheets(2)

while im on sheets(1)

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!

Solution

  • 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