PDA

View Full Version : [SOLVED] ListBox - Population - Load For Each Worksheet



dj44
12-08-2017, 08:03 AM
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
12-08-2017, 09:05 AM
When do they steal it?
pinching my worksheetIf 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
12-08-2017, 10:13 AM
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
12-08-2017, 10:21 AM
Ok that error has gone away - but no data loaded in the form now - when i go to each sheet.
its just blank

SamT
12-08-2017, 11:21 AM
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
12-08-2017, 11:33 AM
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
12-08-2017, 12:12 PM
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
12-08-2017, 01:02 PM
Pardon me,
even im confused, it gets so convulated

21127


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
12-08-2017, 01:07 PM
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
12-08-2017, 02:15 PM
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
12-08-2017, 02:20 PM
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
12-08-2017, 02:44 PM
Thank you Sam for all the help,
let me play about with this userform :type
and get things in order first

dj44
12-08-2017, 03:24 PM
I managed to make the code compact and i didnt need an array after all :doh:

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 :grinhalo:




thanks again for the help and great weekend sam and all

SamT
12-08-2017, 05:23 PM
Great News! :thumb