ListFillRange - how to specify range

A

amyfb

New member
Joined
Dec 18, 2020
Messages
24
Reaction score
0
Points
1
Location
Southeastern PA, USA
Excel Version(s)
O365
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 a moderator:
You want the address as it would appear in a formula. So, for example:

Code: 
.ListFillRange = "Sheet1!A1:A16"
 
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
    [B]Dim xStr As String[/B]
    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
        [B].ListFillRange = ""
        .LinkedCell = ""[/B]
        .Visible = False
    End With
  [B]  If Target.Validation.Type = 3 [/B]Then
        Target.Validation.InCellDropdown = False
        Cancel = True
       [B] xStr = Target.Validation.Formula1[/B]
        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
           [B] .ListFillRange = xStr[/B]
            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 a moderator:
I should have scrolled down in your original code. The ListFillRange is being set correctly, assuming your data validation formula refers to a range.
 
'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!
 
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.
 
You must log in or register to reply here.
Back
Top