+ Reply to Thread
Results 1 to 5 of 5

Looping through columns for listbox items in a user form

  1. 06-14-2016, 01:27 AM #1
    dksr84
    dksr84 is offline
    Registered User
    Join Date
    01-12-2015
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    6

    Unhappy Looping through columns for listbox items in a user form

    Hello!

    I have an issue when I try to loop through multiple items in a list box (which has 4 columns). I had modified a bit of code that I found on onlinepclearning.com
    which goes like this:

    Set MYRange = Sheet3.Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1)
    For a = 0 To Me.Reg3.ListCount - 1
    If Me.Reg3.Selected(a) Then
    MYRange = Me.Reg3.List(a)
    MYRange.Offset(0, 1) = Me.Reg3.List(a, 1)
    MYRange.Offset(0, 2) = Me.Reg3.List(a, 2)
    MYRange.Offset(0, 3) = Me.Reg3.List(a, 3)
    MYRange.Offset(0, 4) = Me.Reg3.List(a, 4)
    Set MYRange = MYRange.Offset(0, 1)
    End If
    Next a

    My problem is that this code does not somehow pick up the first column of the mutiple column list box and shows me an error messgae on "MYRange.Offset(0, 4) = Me.Reg3.List(a, 4)" which says --"Me.Reg3.List(a, 4)= Could not get Listproperty.Invalid argument.

    I am unable to attach my file here hence the detailed explanation.Do let me know if you should require anything else to help me out.

    Br,
    Deepthi
    Reply With QuoteRegister To Reply
  2. 06-14-2016, 02:16 AM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Looping through columns for listbox items in a user form

    Row numbering Starts at 0

    Column Number starts at 0

    Try 
    Please Login or Register  to view this content.
    You do not need a loop to save the contents of a list box.
    Please Login or Register  to view this content.
    Tested using a 4 column listbox thus:-

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 06-14-2016 at 02:20 AM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  3. 06-14-2016, 04:08 AM #3
    dksr84
    dksr84 is offline
    Registered User
    Join Date
    01-12-2015
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    6

    Re: Looping through columns for listbox items in a user form

    Thank you for the very swift response! My listbox items are via row source.
    what I intend to do is transfer all the selected items into the same row into adjacent columns. so the second selected list item of 4 columns follows the first selected list item of 4 columns in a lingle row. If it helps I could send the book over via mail. I am unable to attach it here.

    I had tried changing it as below:

    MYRange = Me.Reg3.List(a)
    MYRange.Offset(0, 1) = Me.Reg3.List(a, 0)
    MYRange.Offset(0, 2) = Me.Reg3.List(a, 1)
    MYRange.Offset(0, 3) = Me.Reg3.List(a, 2)
    MYRange.Offset(0, 4) = Me.Reg3.List(a, 3)
    Set MYRange = MYRange.Offset(0, 4)
    End If
    Next a

    However, with this the 4th column of the first selected item disappears and the first column of the second list box item duplicates.

    Could you please suggest any remedies for this. Does it have anything to do with the no. of bound columns?
    Last edited by dksr84; 06-14-2016 at 04:13 AM.
    Reply With QuoteRegister To Reply
  4. 06-14-2016, 01:01 PM #4
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Looping through columns for listbox items in a user form

    Ok this works for me:-

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 06-15-2016, 02:26 AM #5
    dksr84
    dksr84 is offline
    Registered User
    Join Date
    01-12-2015
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    6

    Re: Looping through columns for listbox items in a user form

    Thank you for the reply however is it possible to send me an eg via an excel sheet so that I can corelate?
    Attached Files Attached Files
    Last edited by dksr84; 06-15-2016 at 02:31 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to pass user selected list items from listbox to another procedure in excel using vba
    By mahendra.asapu in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-20-2016, 03:51 PM
  2. User Form ListBox - Find Records (ListBox Populating Issue)
    By LONeillSSC in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-20-2015, 12:06 PM
  3. How to generate a new form according to items in listbox
    By lavender57_1014 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-26-2014, 03:00 PM
  4. [SOLVED] Form items chosen in Listbox to populate specific cells in spreadsheet
    By floydian in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-14-2013, 07:48 AM
  5. [SOLVED] User Form ListBox Column Widths - How to set widths for unknown number of columns
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-10-2013, 05:24 AM
  6. Check if 2 consecutive Form listbox items selected
    By Foreverlearning in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-24-2012, 07:55 PM
  7. [SOLVED] User form with looping doesn't start at right place.
    By Rominall in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2006, 03:40 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1