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