Posted to microsoft.public.excel.programming
|
|
Automated Delete Item from ComboBox List
Yes, that is a typo. It should be:
set rng = rng.Resize(rng.Rows.Count - 1)
It is probably better to attach it to another button rather than use the
click event. Just but it in the event code for that button.
--
Regards,
Tom Ogilvy
"ToferKing" wrote in message
...
Further help needed:
My combobox is on a worksheet so here is my adjusted coding:
Dim rng As Range, s As String
Dim idex As Long
If ComboBoxCrewDelete.ListIndex = -1 Then Exit Sub
Set rng = Range(ComboBoxCrewDelete.ListFillRange)
s = rng.Address(0, 0, xlA1, True)
idex = ComboBoxCrewDelete.ListIndex
ComboBoxCrewDelete.ListFillRange = ""
rng(idex).EntireRow.Delete
Set rng = Range(s)
rng.Resize (rng.Rows.Count - 1)
ComboBoxCrewDelete.ListFillRange = rng.Address(0, 0, xlA1, True)
The macro is stopping on the line
rng.Resize (rng.Rows.Count - 1)
and giving me an error of
Invalid use of property on the .Resize
In addition, it is not deleting the item I selected.
What did I goof up on?
Tofer
"Tom Ogilvy" wrote:
Not if you have used RowSource or listfillrange to populate the listbox.
Assume your combobox is on a userform
Private Sub Combobox1_Click()
Dim rng as Range, s as String
Dim idex as Long
if Combobox1.ListIndex = -1 then exit sub
set rng = Range(Combobox1.RowSource)
s = rng.Address(0,0,xlA1,True)
idex = Combobox1.ListIndex
Combobox1.RowSource = ''
rng(idex).EntireRow.Delete
set rng = Range(s)
rng.resize(rng.rows.count-1)
Combobox1.RowSource = rng.Address(0,0,xlA1,True)
End Sub
if it is on a worksheet, change rowsource to listfillrange in each
instance.
--
Regards,
Tom Ogilvy
"ToferKing" wrote in message
...
First, to the general public reading this group of posts, I have said
that
I
have figured out how to add items to the list.
So, you may be reading this saying, "Well if she can add an item, she
can
delete an item."
So I should step back a little and say that I didn't do it through
AddItem -
first off because I didn't know about AddItem when I was solving how
to
add
an item and secondly because even if I had found AddItem, I couldn't
have
used it because I don't understand it.
So, more specifically to Chip and those others who will suggest
RemoveItem.
I have entered the code as suggeted by Chip, but I get an error
message
that
says, "Invalid Argument" and it stops on the line .RemoveItem
..ListIndex
So I am assuming that is because I didn't tell the macro which line
item
to
remove, and if that is the case, then I need help programming for the
line
number.
I have a list that has 70 items in it and I would like for the user to
be
able to open the list, selecti an item on the list and then have the
macro
delete it.
Is that what .RemoveItem .ListIndex will do?
Thanks for hanging in here with me.
Tofer
"Chip Pearson" wrote:
Try something like
Private Sub CommandButton1_Click()
With Me.ComboBox1
.RemoveItem .ListIndex
End With
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"ToferKing" wrote in
message
...
I would like to be able to have my users click a button that
allows them to
delete an item from a ComboBox list.
It will have a list of employee names in it, and I have figured
out how to
add an employee name through an input box, but now I want the
user to be able
to click on a button, have it display the ComboBox list, have
him select one
of the names and then have the macro delete that name from the
list.
Any help you can provide is certainly appreciated.
Tofer
|