Display cell range in listbox
 

I managed to add and delete listbox entries on a sheet. However I need some
help to display that entries (range) in the listbox once I load a userform
again. Lastly, I would like some help for when I delete a specific entry in
the listbox to delete the corresponding cell value on the sheet.

Thanks!

Johann

Display cell range in listbox
 

With worksheets("Sheet1")
Userform1.Listbox1.List =
..Range(.Cells(5,"F"),.Cells(5,"F").End(xldown)).V alue
End with


Dim rng as Range
idex = Userform.Listbox1.ListIndex
if idex = -1 then exit sub
With worksheets("Sheet1")
set rng = .Range(.Cells(5,"F"),.Cells(5,"F").End(xldown))(id ex)
end with

msgbox rng.Address


--
Regards,
Tom Ogilvy


"Johann" wrote in message
...
I managed to add and delete listbox entries on a sheet. However I need
some
help to display that entries (range) in the listbox once I load a userform
again. Lastly, I would like some help for when I delete a specific entry
in
the listbox to delete the corresponding cell value on the sheet.

Thanks!

Johann

Display cell range in listbox
 

Thanks for your response Tom, this really helped a lot. I would however like
to simultaneously select the specific cell on the sheet when I select the
item in the list box. Currently the msgbox does the indication. Then lastly,
when I run your code, the listbox builds the item list at the bottom. I
presume it is running down the whole column.

Thx, Johann

"Tom Ogilvy" wrote:

With worksheets("Sheet1")
Userform1.Listbox1.List =
..Range(.Cells(5,"F"),.Cells(5,"F").End(xldown)).V alue
End with


Dim rng as Range
idex = Userform.Listbox1.ListIndex
if idex = -1 then exit sub
With worksheets("Sheet1")
set rng = .Range(.Cells(5,"F"),.Cells(5,"F").End(xldown))(id ex)
end with

msgbox rng.Address


--
Regards,
Tom Ogilvy


"Johann" wrote in message
...
I managed to add and delete listbox entries on a sheet. However I need
some
help to display that entries (range) in the listbox once I load a userform
again. Lastly, I would like some help for when I delete a specific entry
in
the listbox to delete the corresponding cell value on the sheet.

Thanks!

Johann

Display cell range in listbox
 

msgbox was used to show you the results - it was there for illustration, not
for literal use. Obviously I have no idea what you are trying to achieve
beyond the technical question you asked.

Private Sub Listbox1_Click()
Dim rng as Range
idex = Userform.Listbox1.ListIndex
if idex = -1 then exit sub
With worksheets("Sheet1")
.Activate
set rng = .Range(.Cells(5,"F"),.Cells(5,"F").End(xldown))(id ex)
end with

rng.Select
End sub


I don't understand your "lastly" comments.

--
Regards,
Tom Ogilvy


"Johann" wrote in message
...
Thanks for your response Tom, this really helped a lot. I would however
like
to simultaneously select the specific cell on the sheet when I select the
item in the list box. Currently the msgbox does the indication. Then
lastly,
when I run your code, the listbox builds the item list at the bottom. I
presume it is running down the whole column.

Thx, Johann

"Tom Ogilvy" wrote:

With worksheets("Sheet1")
Userform1.Listbox1.List =
..Range(.Cells(5,"F"),.Cells(5,"F").End(xldown)).V alue
End with


Dim rng as Range
idex = Userform.Listbox1.ListIndex
if idex = -1 then exit sub
With worksheets("Sheet1")
set rng = .Range(.Cells(5,"F"),.Cells(5,"F").End(xldown))(id ex)
end with

msgbox rng.Address


--
Regards,
Tom Ogilvy


"Johann" wrote in message
...
I managed to add and delete listbox entries on a sheet. However I need
some
help to display that entries (range) in the listbox once I load a
userform
again. Lastly, I would like some help for when I delete a specific
entry
in
the listbox to delete the corresponding cell value on the sheet.

Thanks!

Johann

Display cell range in listbox
 

I think you anticipated what I needed, as your example works just fine.
Thanks for your help and patience Tom.

"Tom Ogilvy" wrote:

msgbox was used to show you the results - it was there for illustration, not
for literal use. Obviously I have no idea what you are trying to achieve
beyond the technical question you asked.

Private Sub Listbox1_Click()
Dim rng as Range
idex = Userform.Listbox1.ListIndex
if idex = -1 then exit sub
With worksheets("Sheet1")
.Activate
set rng = .Range(.Cells(5,"F"),.Cells(5,"F").End(xldown))(id ex)
end with

rng.Select
End sub


I don't understand your "lastly" comments.

--
Regards,
Tom Ogilvy


"Johann" wrote in message
...
Thanks for your response Tom, this really helped a lot. I would however
like
to simultaneously select the specific cell on the sheet when I select the
item in the list box. Currently the msgbox does the indication. Then
lastly,
when I run your code, the listbox builds the item list at the bottom. I
presume it is running down the whole column.

Thx, Johann

"Tom Ogilvy" wrote:

With worksheets("Sheet1")
Userform1.Listbox1.List =
..Range(.Cells(5,"F"),.Cells(5,"F").End(xldown)).V alue
End with


Dim rng as Range
idex = Userform.Listbox1.ListIndex
if idex = -1 then exit sub
With worksheets("Sheet1")
set rng = .Range(.Cells(5,"F"),.Cells(5,"F").End(xldown))(id ex)
end with

msgbox rng.Address


--
Regards,
Tom Ogilvy


"Johann" wrote in message
...
I managed to add and delete listbox entries on a sheet. However I need
some
help to display that entries (range) in the listbox once I load a
userform
again. Lastly, I would like some help for when I delete a specific
entry
in
the listbox to delete the corresponding cell value on the sheet.

Thanks!

Johann