Prevent Multiple Instances of TempCombo

tlafferty

New Member
Joined
Mar 29, 2019
Messages
5
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?
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

tlafferty

New Member
Joined
Mar 29, 2019
Messages
5
Hmmmm.... Was it bad form to borrow code and the ask for help modifying it? Just wondering why there are no responses.... Also, for the code to run, you would need cells which contain validation dropdowns which refer to named ranges and a Ms.Forms2.0 combobox named TempCombo. If you need further clarification, let me know.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-pr...-prevent-multiple-instances-of-tempcombo.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

tlafferty

New Member
Joined
Mar 29, 2019
Messages
5
Thanks for letting me know! Incidentally, the reason for posting here is that nothing was done in the other forum, other than a brief proposal which ignored what I wanted. The replies then dropped off the radar...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top