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.
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: