+ Reply to Thread
Results 1 to 5 of 5

Display cell range in listbox

Hybrid View

  1. #1
    Johann
    Guest

    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

  2. #2
    Tom Ogilvy
    Guest

    Re: Display cell range in listbox

    With worksheets("Sheet1")
    Userform1.Listbox1.List =
    ..Range(.Cells(5,"F"),.Cells(5,"F").End(xldown)).Value
    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))(idex)
    end with

    msgbox rng.Address


    --
    Regards,
    Tom Ogilvy


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




  3. #3
    Johann
    Guest

    Re: 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)).Value
    > 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))(idex)
    > end with
    >
    > msgbox rng.Address
    >
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Johann" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: 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))(idex)
    end with

    rng.Select
    End sub


    I don't understand your "lastly" comments.

    --
    Regards,
    Tom Ogilvy


    "Johann" <[email protected]> wrote in message
    news:[email protected]...
    > 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)).Value
    > > 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))(idex)
    > > end with
    > >
    > > msgbox rng.Address
    > >
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "Johann" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >
    > >




  5. #5
    Johann
    Guest

    Re: 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))(idex)
    > end with
    >
    > rng.Select
    > End sub
    >
    >
    > I don't understand your "lastly" comments.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Johann" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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)).Value
    > > > 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))(idex)
    > > > end with
    > > >
    > > > msgbox rng.Address
    > > >
    > > >
    > > > --
    > > > Regards,
    > > > Tom Ogilvy
    > > >
    > > >
    > > > "Johann" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1