Automated Delete Item from ComboBox List

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

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" <[email protected]> wrote in
message
news:[email protected]...
>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

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" <[email protected]> wrote in
> message
> news:[email protected]...
> >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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >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
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> > > message
> > > news:[email protected]...
> > > >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
> > >
> > >
> > >
>
>
>

And on last thing, can I attach this code to a button?

What I mean, is that I have some buttons on the workbook that are for adding
items and deleting items, so my question is, can I get a button to invoke a
list box or a combo list box and from that box have the user delete the item?

I have adjusted my workbook to include another drop down list with the
coding given to me by Tom Ogilvy, but I was wanting to attach the drop down
list to a button to be consistent with the rest of my workbook.

Please hang in here with me.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> > > message
> > > news:[email protected]...
> > > >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
> > >
> > >
> > >
>
>
>

Funny you should question this as I have just created a process in one
of my Excel Program to do the very same thing. Shoot me an E-mail and
Ill send you a URL to download the workbook. I have it setup so that
the user fills out a two question form (First Name & Last Name) and
then it submits it to another spreadsheet. The macro then goes to the
combo box and selects the new range generated in another cell (on the
same sheet) and updates it according to how the cell has changed. Does
this on qutie a few of my spreadsheet as soon as they are opened (to
ensure accuracy). Email me your request.. [email protected]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
> > > > message
> > > > news:[email protected]...
> > > > >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
> > > >
> > > >
> > > >
> >
> >
> >

There must be one other adjustment

Tom, there is a line in your original post that looks like this:

Combobox1.RowSource = ''

The sub failed on that line and so I just added a second quote, but now I
wonder if there is something else that goes on that line.

Obviously, the process isn't working for me still and I would hate not to
incorporate your solution, because I am positive you know what to do, but
dang it, it isn't deleting the person I clicked on.

Tofer


"Tom Ogilvy" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> > > > > message
> > > > > news:[email protected]...
> > > > > >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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

I tested it in xl2000 in the Click event of the ComboBox and it worked
although you need one adjustment (deleted the previous row)

If you are using xl97, that version was a bit flakey, so it probably won't
workt there, but should work in xl2000 and later

Private Sub ComboBoxCrewDelete_Click()
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)
' adjustment to idex to refer to correct row
idex = ComboBoxCrewDelete.ListIndex + 1
ComboBoxCrewDelete.ListFillRange = ""
rng(idex).EntireRow.Delete
Set rng = Range(s)
Set rng = rng.Resize (rng.Rows.Count - 1)
ComboBoxCrewDelete.ListFillRange = rng.Address(0, 0, xlA1, True)
End sub

This assumes that the range used as the rowsource is on the same sheet as
the control

--
Regards,
Tom Ogilvy



"ToferKing" <[email protected]> wrote in message
news:[email protected]...
> There must be one other adjustment
>
> Tom, there is a line in your original post that looks like this:
>
> Combobox1.RowSource = ''
>
> The sub failed on that line and so I just added a second quote, but now I
> wonder if there is something else that goes on that line.
>
> Obviously, the process isn't working for me still and I would hate not to
> incorporate your solution, because I am positive you know what to do, but
> dang it, it isn't deleting the person I clicked on.
>
> Tofer
>
>
> "Tom Ogilvy" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
> > > > > > message
> > > > > > news:[email protected]...
> > > > > > >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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

There it goes!

Tom, thank you for all of your follow up and all the work you did.

It is truly, truly, truly appreciated.

Tofer



"Tom Ogilvy" wrote:

> I tested it in xl2000 in the Click event of the ComboBox and it worked
> although you need one adjustment (deleted the previous row)
>
> If you are using xl97, that version was a bit flakey, so it probably won't
> workt there, but should work in xl2000 and later
>
> Private Sub ComboBoxCrewDelete_Click()
> 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)
> ' adjustment to idex to refer to correct row
> idex = ComboBoxCrewDelete.ListIndex + 1
> ComboBoxCrewDelete.ListFillRange = ""
> rng(idex).EntireRow.Delete
> Set rng = Range(s)
> Set rng = rng.Resize (rng.Rows.Count - 1)
> ComboBoxCrewDelete.ListFillRange = rng.Address(0, 0, xlA1, True)
> End sub
>
> This assumes that the range used as the rowsource is on the same sheet as
> the control
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "ToferKing" <[email protected]> wrote in message
> news:[email protected]...
> > There must be one other adjustment
> >
> > Tom, there is a line in your original post that looks like this:
> >
> > Combobox1.RowSource = ''
> >
> > The sub failed on that line and so I just added a second quote, but now I
> > wonder if there is something else that goes on that line.
> >
> > Obviously, the process isn't working for me still and I would hate not to
> > incorporate your solution, because I am positive you know what to do, but
> > dang it, it isn't deleting the person I clicked on.
> >
> > Tofer
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in
> > > > > > > message
> > > > > > > news:[email protected]...
> > > > > > > >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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>