Hi -
I borrowed this code from ExtendOffice. Unfortunately, it shows multiple combos when in protected mode. There are two lines to toggle on and off protection which is slow and inefficient. How can I display just one instance of the combo without having to toggle protection?
I borrowed this code from ExtendOffice. Unfortunately, it shows multiple combos when in protected mode. There are two lines to toggle on and off protection which is slow and inefficient. How can I display just one instance of the combo without having to toggle protection?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
'Variable declarations
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
'Set reference to the current worksheet
Set xWs = Application.ActiveSheet
'Handle any error by continuing through code without halt
On Error Resume Next
'Set an instance of the object for manipulation
Set xCombox = xWs.OLEObjects("TempCombo")
'Initial settings for combo box
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
'Check to see if selected cell in the worksheet is set to dropdown due to
'data validation settings and shutdown the dropdown if true
'Check the contents of the selection and exit if blank
'************************************************************************
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
Set xCombox = Nothing
Exit Sub
End If
With xCombox
'Temporarily turn off protection to prevent
'multiple combo boxes from appearing in sheet
ActiveSheet.Unprotect
'Set appearance of combo box and display it
.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
'Set a link to the active cell in the
'worksheet so combo box data fills it
.LinkedCell = Target.Address
End With
'Activate the combo and display
'the list
xCombox.Activate
Me.TempCombo.DropDown
'turn worksheet protection back on
'since it was turned off above
ActiveSheet.Protect
End If
'************************************************************************
'Release the object to prevent memory issues
Set xCombox = Nothing
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
