+ Reply to Thread
Results 1 to 13 of 13

Populate Listbox1 with data from a named range when opening userform

  1. #1
    Registered User
    Join Date
    06-01-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    7

    Populate Listbox1 with data from a named range when opening userform

    I have amateur VBA coding skills. I am trying to populate listbox1 with data from a named range (Athlete), when I open userform1 using initialize. I cannot get the data to autofill into listbox1 from the named range. Below is the code I have:

    Private Sub UserForm1_Initialize()

    Dim myCell As Range
    Dim rngitems As Range
    Set rngitems = Sheets("AthleteMax").Range("Athlete")

    Me.ListBox1.Clear
    Me.ListBox2.Clear

    With Me.ListBox1

    For Each myCell In rngitems.Cells
    If Trim(myCell) <> "" Then
    .AddItem myCell.Value
    End If
    Next myCell

    End With

    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ListBox2.MultiSelect = fmMultiSelectMulti

    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Populate Listbox1 with data from a named range when opening userform

    Change Private Sub UserForm1_Initialize() to Private Sub UserForm_Initialize().
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-01-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    7

    Re: Populate Listbox1 with data from a named range when opening userform

    Norie,

    I originally did not have the "1", however without the "1" the form would not pop up at all. With the "1" the form pops up but the list box does not populate from the named range.

  4. #4
    Registered User
    Join Date
    06-01-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    7

    Re: Populate Listbox1 with data from a named range when opening userform

    Norie,

    I originally did not have the "1", however without the "1" the form would not pop up at all. With the "1" the form pops up but the list box does not populate from the named range.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Populate Listbox1 with data from a named range when opening userform

    The Initialize event of a userform is always written like this,
    Please Login or Register  to view this content.
    it's never written like this.
    Please Login or Register  to view this content.
    To open the form you use it's name like this,
    Please Login or Register  to view this content.
    which will trigger the form's Initialize event and populate the listbox.

    How were you trying to show/run the userform?

  6. #6
    Registered User
    Join Date
    06-01-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    7

    Re: Populate Listbox1 with data from a named range when opening userform

    I am assigning the macro to a picture, so when I click the picture the userform box appears, and a list of names will populate in listbox1. From which I can select certain names in order to print specific sheets.

    Should I populate listbox1 a different way, maybe using this code: ListBox1.RowSource = Range("Athlete").Address

    Then use "show" to have the userform appear when picture is clicked?

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Populate Listbox1 with data from a named range when opening userform

    The code is fine, it's the name of the Sub that's the problem and perhaps how you are calling things.

    What 'macro' are you assigning to the picture?

    If you wanted a macro to show UserForm1 it would look something like this.
    Please Login or Register  to view this content.
    If you assigned that macro to the picture then when you clicked the picture the userform should be displayed.

  8. #8
    Registered User
    Join Date
    06-01-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    7

    Re: Populate Listbox1 with data from a named range when opening userform

    My terminology is most likely not accurate, so that is not helpful. But yes, I have a macro exactly as you have described above that is assigned to the picture so it does in fact show userform1 when the picture is clicked. I deleted the initialize code and it didn't seem to effect anything.

    I still have the same problem now that I cannot get the listbox to auto populate. I have this code but I am not sure what I need to change the "click" portion to.

    Private Sub ListBox1_Click()

    ListBox1.RowSource = Range("Athlete").Address

    End Sub

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Populate Listbox1 with data from a named range when opening userform

    Your original code with the amended sub name should work, see the attached file.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-01-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    7

    Re: Populate Listbox1 with data from a named range when opening userform

    Thank you for your time. I tried to implement your code with changes above and it did not work for some reason. I have been messing with it for awhile and then I did something to mess up the "macro" with the showform code. If you would like to continue assisting, I have attached the file, I will keep attempting to fix the problem .
    Attached Files Attached Files
    Last edited by mhamm26; 06-04-2017 at 09:40 AM.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Populate Listbox1 with data from a named range when opening userform

    The problem in the workbook you've uploaded is that there's a trailing space in the sheet name which holds the data.

    Try changing the name of the sheet from 'AthleteMax ' to 'AthleteMax'.

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Populate Listbox1 with data from a named range when opening userform

    Accidental duplicate.

  13. #13
    Registered User
    Join Date
    06-01-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    7

    Re: Populate Listbox1 with data from a named range when opening userform

    I have got it all figure out. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Populate ListBox with data from named range
    By kaseyleigh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-12-2016, 12:06 PM
  2. Add list of items from named range "Comp" to ListBox1
    By kaseyleigh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2016, 10:07 AM
  3. Add list of items from named range "Comp" to ListBox1
    By kaseyleigh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2016, 02:01 PM
  4. [SOLVED] Populate Listbox2 based on value selected in ListBox1 with multiple worksheets data
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-06-2016, 11:38 AM
  5. Using Named range to populate a Combobox in userform
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2015, 09:57 PM
  6. View Data On Listbox1 Value depends upon combobox selection userform VBA
    By MicroTees in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2014, 04:58 AM
  7. [SOLVED] UserForm : Want to link Listbox1 with Listbox2 and delete data with one button
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-01-2014, 11:18 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1