Adding functionality - Deleting row in Named region from ListBox selection.

[Cameron]

Hi all,

Slowly working away on my "Medical Expences" spreadsheet and trying to add some functionality.

I've been successful (with assistance) in adding Doctors to a Named List (sorted), now I'm wanting to delete Doctors.
I've populated a ListBox of the Doctors Names ...

ListBox1.Clear
ListBox1.RowSource = ""
With Worksheets("Extra Tables")
For Each cell In .Range("Doctors")
ListBox1.AddItem cell.Value
Next
End With

Now I'm having trouble working out how to remove the selected Doctor based on the ListBox1.Value or other ListBox1 property.

I tried to perform a find whilst recording a macro in order to start with something and it saved nothing.

I've gathered I need to do the following:
a) User select Doctor to be deleted ...... Done!
b) Find selected Doctor in Doctors Named region.
c) Display and confirm deletion to user.
d) Delete Doctor from Named region.

Many thanks in advance.

Cheers,
Cameron

 

[steve smallman]

Cameron,

I haven't followed your developments, so this may be off
track, so forgive me if I restate your problem.

1. you have a named range on worksheet extratables
2. you use that range to populate a list box
3. the user selects the name from the list
ok so far
4. you want the selected name to be deleted from the range

without testing the code, I would suggest you have a two
stage deletion process, 1. selection, 2. delete button

in the delete button click event

response=msgbox("Deleting Dr." & cboDoctorList,
vbokcancel)
if response = vbcancel then exit sub
sheets("extratables").activate
range("Doctors").select
for each c in doctors
if c.value= cbodoctorList then
activecell.entirerow.delete
exit for
end if
next

call (your code to populate list

exit sub

I have assumed you called the list box cboDoctorList

HTH

Steve

-----Original Message-----
Hi all,

Slowly working away on my "Medical Expences" spreadsheet

and trying to add some functionality.

I've been successful (with assistance) in adding Doctors

to a Named List (sorted), now I'm wanting to delete
Doctors.

I've populated a ListBox of the Doctors Names ...

ListBox1.Clear
ListBox1.RowSource = ""
With Worksheets("Extra Tables")
For Each cell In .Range("Doctors")
ListBox1.AddItem cell.Value
Next
End With

Now I'm having trouble working out how to remove the

selected Doctor based on the ListBox1.Value or other
ListBox1 property.

I tried to perform a find whilst recording a macro in

order to start with something and it saved nothing.