ListBox - Population - Load For Each Worksheet

[dj44]

good day folks,

I am working on my userform and need some array help as usual.


This below works

 Private Sub UserForm_Initialize()




    '---  LOAD SPECIFIC DATA For Each Worksheet


    Dim oData As Range

    If ActiveSheet.Name = "Sheet1" Then
    Title.Caption = ActiveSheet.Name
    Set oData = ActiveWorkbook.Sheets("Data").Range("B2:C5")
    ListBox1.List = oData.Value

    '--------------------------
    ElseIf ActiveSheet.Name = "Sheet2" Then

    Title.Caption = ActiveSheet.Name
    Set oData = ActiveWorkbook.Sheets("Data").Range("B6:C10")
    ListBox1.List = oData.Value

    '---------------------------
    ElseIf ActiveSheet.Name = "Sheet3" Then
    Title.Caption = ActiveSheet.Name
    Set oData = ActiveWorkbook.Sheets("Data").Range("B11:C20")
    ListBox1.List = oData.Value


    End If
    
    
    End Sub

Now i need to translate to an array becuase some one keeps pinching my worksheet, and i would like an array to populate it instead

Private Sub UserForm_Initialize()

   ' Array_Version()


    Dim i                           As Long
    Dim oSrcSht                     As Variant   ' Worksheet
    Dim ws                          As Worksheet

    Dim oData As Variant   'Range

    oData = Array("B2:C5", "B6:C10")

    With ThisWorkbook
    oSrcSht = Array(.Sheets("Sheet1"), .Sheets("Sheet2"))
    End With



    Set ws = Worksheets("Data")


   ' For i = 2 To 500
    If ActiveSheet.Name = oSrcSht(i).Value Then


    Title.Caption = ActiveSheet.Name

    ListBox1.List = ws.Range(oData(i)).Value


    End If
   ' Next i

    'Next

    End Sub

doesnt work and i cant seem to spot the coding errors amongst many please do advise on this array gone wrong

[SamT]

When do they steal it?

pinching my worksheet

If it's before the code runs, you're out of luck. If it's after the code, it doesn't make a difference.

If the Sheets are always there when the workbook opens, you can use the WorkBook_Open event sub

Public List1 as Variant
Public List2 'also as Variant
Public List3

Private Sub Workbook_Open()
With Sheets("Data")
List1 = .Range("B2:C5").Value 
List2 = .Range("B6:C10").Value
List3 = .Range("B11:C20") .Range("B11:C20") .Value
End with
End Sub

Private Sub UserForm_Initialize() 

Select Case Right(ActiveSheet.Name, 1)
Case "1": ListBox1.List = List1
Case "2": ListBox1.List = List2
Case "3": ListBox1.List = List3
Case Else: MsgBox "???"
End Select
'Blah
'Blah
End Sub

[dj44]

Hello Sam,

thank you for this alternative way of doing this.

I added the new code to my userform, but i keep getting an variable not defined.

But it is defined

Public List1 As Variant
Public List2 As Variant


I put this in thisWorkbook

[dj44]

Ok that error has gone away - but no data loaded in the form now - when i go to each sheet.
its just blank

[SamT]

but no data loaded in the form now - when i go to each sheet.
its just blank

What?
What do you mean when you say, "Form?"

[dj44]

Hello Sam,

I mean when i load my userform which is called Form, its meant to load the listbox with the data from the worksheet range allocated to it

The data is not loading for some reason.

[SamT]

Did you create the USerForm in VBA? Or did you use a Worksheet as the "Form?"

You said, "No data in the Form," and you said, "The Worksheet is just blank"

To me, those two statements are not usually associated with each other in one phrase. I am lost as to what you are trying to tell me.

If the sheet is blank, there is no data for the "Form" to load.

Try Compiling the Code and try using F8 to step thru the code.

Try being precise when you tell us what is happening. Don't make up your own meanings for words. Try to use words as defined by VBA and by Excel.

[dj44]

Pardon me,
even im confused, it gets so convulated

Data Not Loading.jpg


The Userform pulls the data from the worksheet "Data"

The Userform Listbox is not loading the data.

The listbox is empty as shown above

When i call the form from Sheet1 - the userform appears, but the listbox is empty
im not sure why

[dj44]

What i was really trying to do was to simplyfy my code.

As you can see my original code has a lot of if else statements and i was adding more worksheets.

So i thought an array would maybe hold the ranges or worksheet names to make it shorter.

While pulling the data from the "Data" worksheet.

but as usual i can never explain these things

[SamT]

Try adding the "Me" Variable to the three lines in my code

 Me.ListBox1.List =...

And you might try

Me.ListBox1.List() =...

Ms Office changed a lot after Excel 2003. Now I'm confoozed

[SamT]

Temporarily add these as the last 2 lines in the Workbook Open sub

MsgBox List1(1, 1) 'Lawrence
MgsBox List1(1, 2) 'Richards

They will verify that at least List1 has 2 columns of data.

You can also put them into the Form Initialize sub as the first 2 lines. That will verify that the Form can read at least List1

Finally, In the Form Initialize sub, prefix the List variables with "ThisWorkbook.":

Me.ListBox1.List() = ThisWorkbook.List1

[dj44]

Thank you Sam for all the help,
let me play about with this userform
and get things in order first

[dj44]

I managed to make the code compact and i didnt need an array after all

Well you only know these things when you get your hands stuck in the nuts and bolts and

i needed this line

Select Case Right(ActiveSheet.Name, 1)

Private Sub UserForm_Initialize()

Dim ws As Worksheet

     
Set ws = Worksheets("Data")
 
    Select Case Right(ActiveSheet.Name, 1)
    Case "1": Me.ListBox1.List() = ws.Range("B2:C5").Value           ' 1  -    Sheet 1
    Case "2": Me.ListBox1.List() = ws.Range("B6:C10").Value         ' 2  -    Sheet 2
    Case "3": Me.ListBox1.List() = ws.Range("B11:C20").Value       ' 3  -    Sheet 3
    Case "4": Me.ListBox1.List() = ws.Range("B21:C30").Value       ' 4  -    and again etc
    Case "5": Me.ListBox1.List() = ws.Range("B32:C40").Value       ' 5  -
    Case "6": Me.ListBox1.List() = ws.Range("B40:C50").Value       ' 6  -
    End Select

End Sub

as for somebody pinching my worksheets Sam

"SamT When do they steal it?"


you'd be surprised how many people try to get their hands on my nice formatted worksheet,

i do take pride in making a nice worksheet




thanks again for the help and great weekend sam and all

[SamT]

Great News!