+ Reply to Thread
Results 1 to 5 of 5

ListBox rowsource named range offset by 1

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-29-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    103

    ListBox rowsource named range offset by 1

    As the title says, I have a listbox populate with a named range on startup in UserForm_Initialize(). The named range contains the header "Vendor" on the first line. I want the listbox to omit the first line from the named range, but no matter what code I try to toss at it, I'm getting errors. Here's my latest failed attempt at it:

    Private Sub UserForm_Initialize()
        SpinButton1.Value = 0
        Dim lbrng As Range: Set lbrng = Sheets("Vendor").Range("VendorList").Offset(1, 0).Value
        ListBox1.RowSource = lbrng.Address
    End Sub
    BTW, this code alone works without error, but does not offset the list...
        ListBox1.RowSource = Worksheets("Data").Range("VendorList").Offset(1).Value
    Strange, I retract that. It suddenly isn't working now once I restarted the project.
    Last edited by Taemex; 12-17-2014 at 02:21 AM.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,649

    Re: ListBox rowsource named range offset by 1

    Try this to see if this helps......

    ListBox1.RowSource = Sheets("Vendor").Range("VendorList").Offset(1, 0).Address
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,649

    Re: ListBox rowsource named range offset by 1

    Or your code should be like this.....
    Private Sub UserForm_Initialize()
        SpinButton1.Value = 0
        Dim lbrng As Range
        Set lbrng = Sheets("Vendor").Range("VendorList").Offset(1, 0)
        ListBox1.RowSource = lbrng.Address
    End Sub

  4. #4
    Forum Contributor
    Join Date
    08-29-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: ListBox rowsource named range offset by 1

    Script out of range. Is it because my named range is <---15---> column's across?

  5. #5
    Forum Contributor
    Join Date
    08-29-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    103

    Re: ListBox rowsource named range offset by 1

    I had to make a new range called Vendor 1-column wide. Here's the code if anyone's interested:

    Private Sub UserForm_Initialize()
        Dim ventu As Range, rven As Long: Set ventu = Worksheets("Vendors").Range("VendorList")
        rven = ventu.Rows.Count
        With ventu
        Set ventu = ventu.Resize(rven, 1).Offset(1, 0): ventu.Name = "Vendor"
        End With
        ListBox1.RowSource = ThisWorkbook.Name & "!Vendor"
    End Sub

+ 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. Forms - Named Range as the RowSource
    By Michael Beckinsale in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-12-2023, 04:33 AM
  2. [SOLVED] VBA: UserForm ListBox Column Headings RowSource = Named Range
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2012, 02:21 AM
  3. ListBox RowSource from multiple named ranges
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-22-2011, 04:56 PM
  4. Listbox w/ Range.name rowsource only displaying 6 of the 8 range elements
    By jazaddict in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-29-2009, 05:06 PM
  5. range name as rowsource of listbox?
    By Stefi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2006, 09:30 AM

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