ListBox AddItem/RemoveItem

[RBrown]

I have a list of order numbers in column “A” of sheet2 used to populate a userform listbox.
Is it possible to edit the list box and the sheet from the userform (add or delete) even though sheet2 is not the active sheet when the userform is loaded?
Any help will be appreciated.

 

[RBrown]

Bump

 

[viquarshaikh]

Yep you sure can do that.....

Private Sub UserForm_initialize()
dim l as long
dim r as range

set r = sheets("Sheet2").Range("A1")

l = 1 'Comment this line if the Column A does not have header rows
Listbox1.Clear
while r.Offset(l,0).Value <> ""
ListBox1.AddItem r.Offset(l,0).Value
l=l+1
Wend

End Sub

 

[RBrown]

Thanks viquarshaikh,
This works as far as populating the listbox from an inactive sheet but is there a way to edit the listbox and the worksheet (add or delete cell values) from the userform.

 

[viquarshaikh]

Have a command Button on the Form and on its Click Event you could simply remove a selected item from a listbox by using the .Remove method..... Using the same value delete it from the Inactive sheet too by finding and deleting it....(though take care to delete the entire row)

However, to add a value what you could do is, just have a textbox on the form type some values into it and on the click event of another button add the typed text into the Listbox and the Excel Column