Results 1 to 6 of 6

Thread: ListFillRange - how to specify range

  1. #1
    Seeker amyfb's Avatar
    Join Date
    Dec 2020
    Location
    Southeastern PA, USA
    Posts
    14
    Articles
    0
    Excel Version
    O365

    ListFillRange - how to specify range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Can anyone help me with how to specify the range for this bit of code I found online?
    the original code snip has " " for a listfillrange value and nothing displays in the combo box created for it.
    so i went looking for listfillrange help but can only get a locked up file.

    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Update by Extendoffice: 2020/01/16
        Dim xCombox As OLEObject
        Dim xStr As String
        Dim xWs As Worksheet
        Dim xArr
        
        Set xWs = Application.ActiveSheet
        On Error Resume Next
        Set xCombox = xWs.OLEObjects("TempCombo")
        
     'following snip from a different website causes a reference not valid error and the workbook locks up
     
     '  With objOLE
      '  .Name = "TempCombo"
        '.ListFillRange = "'Sheet6'!A1:A16"
       ' .ListFillRange = "'" & Sheets("Data").Name & "'!" & Sheets("Data").Range("r1:r1600").Address
    'End With
     
        
     'how is the range specified here?
     
        With xCombox
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
        End With
        If Target.Validation.Type = 3 Then
            Target.Validation.InCellDropdown = False
            Cancel = True
            xStr = Target.Validation.Formula1
            xStr = Right(xStr, Len(xStr) - 1)
            If xStr = "" Then Exit Sub
            With xCombox
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = Target.Width + 5
                .Height = Target.Height + 5
                .ListFillRange = xStr
                If .ListFillRange = "" Then
                    xArr = Split(xStr, ",")
                    Me.TempCombo.List = xArr
                End If
                .LinkedCell = Target.Address
            End With
            xCombox.Activate
            Me.TempCombo.DropDown
        End If
    End Sub
    Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Select Case KeyCode
            Case 9
                Application.ActiveCell.Offset(0, 1).Activate
            Case 13
                Application.ActiveCell.Offset(1, 0).Activate
        End Select
    End Sub
    Last edited by JoePublic; 2022-05-24 at 02:23 PM. Reason: Added code tags

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    210
    Articles
    0
    Excel Version
    2010
    You want the address as it would appear in a formula. So, for example:

    Code:
    .ListFillRange = "Sheet1!A1:A16"
    Out of many, one people

  3. #3
    Seeker amyfb's Avatar
    Join Date
    Dec 2020
    Location
    Southeastern PA, USA
    Posts
    14
    Articles
    0
    Excel Version
    O365
    I wish it were that simple. The code has a variable xStr that connects to something called Target.validation and the listfillrange refers to the xStr variable, but I cannot make sense of where I'm supposed to put the actual range information.

    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Update by Extendoffice: 2020/01/16
        Dim xCombox As OLEObject
        Dim xStr As String
        Dim xWs As Worksheet
        Dim xArr
        
        Set xWs = Application.ActiveSheet
        On Error Resume Next
        Set xCombox = xWs.OLEObjects("TempCombo")
    
        
     'how is the range specified here?
     
        With xCombox
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
        End With
        If Target.Validation.Type = 3 Then
            Target.Validation.InCellDropdown = False
            Cancel = True
            xStr = Target.Validation.Formula1
            xStr = Right(xStr, Len(xStr) - 1)
            If xStr = "" Then Exit Sub
            With xCombox
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = Target.Width + 5
                .Height = Target.Height + 5
                .ListFillRange = xStr
                If .ListFillRange = "" Then
                    xArr = Split(xStr, ",")
                    Me.TempCombo.List = xArr
                End If
                .LinkedCell = Target.Address
            End With
            xCombox.Activate
            Me.TempCombo.DropDown
        End If
    End Sub
    Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Select Case KeyCode
            Case 9
                Application.ActiveCell.Offset(0, 1).Activate
            Case 13
                Application.ActiveCell.Offset(1, 0).Activate
        End Select
    End Sub
    Last edited by p45cal; 2022-05-25 at 07:57 PM. Reason: added code tags

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    210
    Articles
    0
    Excel Version
    2010
    I should have scrolled down in your original code. The ListFillRange is being set correctly, assuming your data validation formula refers to a range.
    Out of many, one people

  5. #5
    Seeker amyfb's Avatar
    Join Date
    Dec 2020
    Location
    Southeastern PA, USA
    Posts
    14
    Articles
    0
    Excel Version
    O365
    'Variable not defined' error is what I get when running that code exactly as i copied from the web.

    I wish I could understand how the code is getting the range values without me specifiying them .

    I've tried putting the combo box on the cell where the list of values is set, and also on an adjacent cell, but I get nothing.

    All ideas are welcome. thanks for listening and trying to help!

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    210
    Articles
    0
    Excel Version
    2010
    You don't need to do anything with the combobox. When you select a cell with a data validation list, the code will position the combobox over the cell and assign the range specified in the DV list to the combobox's listfillrange.

    You should remove the Cancel = True line though.
    Out of many, one people

Posting Permissions

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