R
ranswrt
I have the following code for a userform:
Private Sub UserForm_Initialize()
Dim item As String
Dim num As Integer
Dim xcell As Range
Dim ycell As Range
Dim i As Integer
Dim j As Integer
item = Range("currentdb")
num = Range(item & "itemnum")
With updateDB
.caption = item & " Database"
End With
If num > 0 Then
ReDim a(num, 7)
Set xcell = Range(item & "itemno")
For i = 0 To num - 1
For j = 0 To 7
a(i, j) = xcell.Offset(i + 1, j + 1)
Next
Next
With ListBox1
.BackColor = &H80000005
.ForeColor = &H80000008
.AddItem
For i = 0 To num - 1
For j = 0 To 6
.List(i, j) = a(i, j)
Next
If a(i, 7) = "T" Then
.Selected(i) = True
Else
.Selected(i) = False
End If
Next
End With
End If
With CommandButton3
.Enabled = False
End With
With Label1
.caption = "Select " & item & " Items to Update"
.Enabled = True
End With
With Label2
.Enabled = False
End With
With Label3
.caption = "Unit"
.Enabled = False
End With
With Label4
.Enabled = False
.caption = "Add New Item to " & item & " Database"
End With
With Label7
.caption = "Total Number of Items for " & item & " Database"
.TextAlign = fmTextAlignRight
End With
With Label8
.caption = num
End With
With TextBox1
.Enabled = False
.MaxLength = 40
End With
With ComboBox1
.Enabled = False
End With
With CommandButton11
.Enabled = False
End With
With CommandButton12
.Enabled = False
End With
With CommandButton7
.Enabled = False
End With
With ComboBox1
.AddItem "EA"
.AddItem "CY"
.AddItem "LF"
.AddItem "SF"
.AddItem "SQ"
.AddItem "SY"
.AddItem "CY"
.AddItem "Ton"
.AddItem "HR"
.AddItem "Day"
.AddItem "Month"
.AddItem "Perc."
.AddItem "N/A"
.ListRows = 13
End With
End Sub
I get a "Run time errror 381', 'could not set the list property. Invalid
property array index.' I am trying to use 'additem' to load a listbox .
What am I doing wrong?
Thanks
Private Sub UserForm_Initialize()
Dim item As String
Dim num As Integer
Dim xcell As Range
Dim ycell As Range
Dim i As Integer
Dim j As Integer
item = Range("currentdb")
num = Range(item & "itemnum")
With updateDB
.caption = item & " Database"
End With
If num > 0 Then
ReDim a(num, 7)
Set xcell = Range(item & "itemno")
For i = 0 To num - 1
For j = 0 To 7
a(i, j) = xcell.Offset(i + 1, j + 1)
Next
Next
With ListBox1
.BackColor = &H80000005
.ForeColor = &H80000008
.AddItem
For i = 0 To num - 1
For j = 0 To 6
.List(i, j) = a(i, j)
Next
If a(i, 7) = "T" Then
.Selected(i) = True
Else
.Selected(i) = False
End If
Next
End With
End If
With CommandButton3
.Enabled = False
End With
With Label1
.caption = "Select " & item & " Items to Update"
.Enabled = True
End With
With Label2
.Enabled = False
End With
With Label3
.caption = "Unit"
.Enabled = False
End With
With Label4
.Enabled = False
.caption = "Add New Item to " & item & " Database"
End With
With Label7
.caption = "Total Number of Items for " & item & " Database"
.TextAlign = fmTextAlignRight
End With
With Label8
.caption = num
End With
With TextBox1
.Enabled = False
.MaxLength = 40
End With
With ComboBox1
.Enabled = False
End With
With CommandButton11
.Enabled = False
End With
With CommandButton12
.Enabled = False
End With
With CommandButton7
.Enabled = False
End With
With ComboBox1
.AddItem "EA"
.AddItem "CY"
.AddItem "LF"
.AddItem "SF"
.AddItem "SQ"
.AddItem "SY"
.AddItem "CY"
.AddItem "Ton"
.AddItem "HR"
.AddItem "Day"
.AddItem "Month"
.AddItem "Perc."
.AddItem "N/A"
.ListRows = 13
End With
End Sub
I get a "Run time errror 381', 'could not set the list property. Invalid
property array index.' I am trying to use 'additem' to load a listbox .
What am I doing wrong?
Thanks