MrExcel Publishing
Your One Stop for Excel Tips & Solutions

how can i store data of a sheet into a list box then i


Posted by Adnan Badar on January 31, 2002 2:50 AM

how can i store data of a sheet into a list box then i can use that list box to apply on another sheet n both sheets are placed in a same work book
plz reply me soon as possible i m at work :(


Posted by JohnG on January 31, 2002 8:57 AM

An Example
Private Sub CommandButton1_Click()
ListBox1.Clear
Do While Worksheets("Sheet1").Range("A" & EntryCount + 1).Value <> ""
'put color list in listbox
ListBox1.AddItem Worksheets("Sheet1").Range("A" & EntryCount + 1).Value
EntryCount = EntryCount + 1
Loop
End Sub

Private Sub CommandButton2_Click()
'Ensure ListBox contains list items
If ListBox1.ListCount >= 1 Then
'If no selection, choose last list item.
If ListBox1.ListIndex = -1 Then
ListBox1.ListIndex = _
ListBox1.ListCount - 1
End If
ListBox1.RemoveItem (ListBox1.ListIndex)
End If
End Sub

Private Sub CommandButton3_Click()
Unload UserForm1
End Sub

Private Sub ListBox1_Click()
'set A1 to color selected from list in sheet1
For i = 0 To ListBox1.ListCount
If ListBox1.Selected(i) = True Then
Worksheets("Sheet2").Range("A1").Interior.ColorIndex = Val(ListBox1.List(i))
Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet2").Range("A1").Interior.ColorIndex
End If
Next i

End Sub

Private Sub UserForm_Initialize()
EntryCount = 0
CommandButton1.Caption = "Add List"
CommandButton2.Caption = "Remove Item"
CommandButton2.Caption = "Exit"
End Sub
This takes a list from column A in sheet1 and changes A1 on sheet2 to what ever the user selects.