none
TEMP COMBOBOX DOESNOT WORK IN PROTECTED SHEET RRS feed

  • Question

  • Hi,

    With this code "


    '==========================
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
      Cancel As Boolean)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ScreenUpdating = False

    Set cboTemp = ws.OLEObjects("TempCombo1")
      On Error Resume Next
      With cboTemp
      'clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
      End With
    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        'if the cell contains a data validation list
        Cancel = True
        Application.EnableEvents = False
        'get the data validation formula
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          'show the combobox with the list
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 5
          .Height = Target.Height + 5
          .ListFillRange = str
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        'open the drop down list automatically
        Me.tempcombo1.DropDown

      End If
     
    errHandler:
      Application.EnableEvents = True
      ScreenUpdating = True
      Exit Sub

    End Sub
    '=========================================
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.EnableEvents = False
    Application.ScreenUpdating = True

    If Application.CutCopyMode Then
      'allow copying and pasting on the worksheet
      GoTo errHandler
    End If

    Set cboTemp = ws.OLEObjects("TempCombo1")
      On Error Resume Next
      With cboTemp
        .Top = 10
        .Left = 10
        .Width = 0
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
      End With

    errHandler:
      Application.EnableEvents = True
      Exit Sub

    End Sub
    '====================================
    'Optional code to move to next cell if Tab or Enter are pressed
    'from code by Ted Lanham
    '***NOTE: if KeyDown causes problems, change to KeyUp

    Private Sub TempCombo_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        Select Case KeyCode
            Case 9 'Tab
                ActiveCell.Offset(0, 1).Activate
            Case 13 'Enter
                ActiveCell.Offset(1, 0).Activate
            Case Else
                'do nothing
        End Select
    End Sub
    '===================================="

    the temp combobox stops to work only data validation dropdown works. any problem with code ? file takes is very slow in function.

    regards,

    Wednesday, February 4, 2015 10:17 PM

Answers

  • Hi,

    I am sorry, I posted the old file. I corrected this meanwhile. When I protect it this tempcombo does not work. Only data validation drop down appears. How can I get it working in protected worksheet ? I am using MS OFFICE 2010 32 BIT and window 10 pre.



    Hi,

    >>When I protect it this tempcombo does not work.

    Which event didn’t work? The Worksheet_BeforeDoubleClick, Worksheet_SelectionChange or TempCombo_KeyDown?

    >> Only data validation drop down appears.

    If Target.Validation.Type = 3 Then
        'if the cell contains a data validation list
        Cancel = True
        ‘other part
        cboTemp.Activate
        'open the drop down list automatically
        Me.TempCombo.DropDown
    

    Based on these words and the code above, did you mean the combobox dropped down like the screenshot below?

     

    Since it only dropped down when Target.Validation.Type = 3, as the XlDVType Enumeration and the comments of that code shared, when the data validation type for the Target cell (the active cell) is checked, It is the right behavior that the combobox will drop down only if the validation type is 3 (a drop down list).

    I assume you enabled “Edit objects” when you protect that sheet because I could not get that combobox drop down if I disabled it, right?

    If that is not the case, it will help us to clarify this issue if you could share us the sample document and the screenshot.

    >> I am using MS OFFICE 2010 32 BIT and window 10 pre.

    I would recommend you test it on another OS like Windows 7 or Windows 8.1, if this issue could only be reproduced on Windows 10 Pre, you could submit it with the way How to use Windows 10 Technical Preview  shared.

    Regards,

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, February 10, 2015 5:35 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.
    Thursday, February 5, 2015 6:14 AM
  • thank you
    Thursday, February 5, 2015 2:29 PM
  • Hello,

    I have tested that code with office 2013, and I could get these events work in protected sheet.

    >>the temp combobox stops to work only data validation dropdown works. any problem with code ? file takes is very slow in function.

    To clarify this issue, would you mind sharing us where and what that issue is?

    Did you mean that combobox KeyDown event didn't work or the other event?

    It will be helpful if you could share us a sample spreadsheet and the steps to reproduce this issue.

    Regards,

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    • Edited by Carl Cai Tuesday, February 10, 2015 2:13 AM
    Friday, February 6, 2015 5:27 AM
  • Thank you,

    In this file if I protect, combobox doesnot work.

    http://1drv.ms/1LUrVJM

    https://onedrive.live.com/redir?resid=90B1344C0622B513!6089&authkey=!APdxuW-ub25gRTk&ithint=file%2cxlsm

    I want space between alfabet of one line to another line to be increased, how to do that ?

    Friday, February 6, 2015 2:59 PM
  • if those links did not work , please try this one

    http://1drv.ms/1zLa9SC

    https://onedrive.live.com/redir?resid=90B1344C0622B513!6089&authkey=!APdxuW-ub25gRTk&ithint=file%2cxlsm

    https://onedrive.live.com/redir?resid=90B1344C0622B513!6089&authkey=!APdxuW-ub25gRTk&ithint=file%2cxlsm

    Sunday, February 8, 2015 3:23 PM
  • Hello,

    I have tested the file you shared without protecting any sheet and got an error at this line below.

    Set cboTemp = ws.OLEObjects("TempCombo")

    Based on Worksheet.OLEObjects Method (Excel), it seems that the current sheet doesn't contain the object named "TempCombo".

    To confirm whether there is any oleobject, I checked its number with ActiveSheet.OLEObjects.Count, it turns out to be zero.

    You could check that count number to see whether you have added any olebobject.

    Regards,

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, February 9, 2015 6:44 AM
  • Hi,

    I am sorry, I posted the old file. I corrected this meanwhile. When I protect it this tempcombo does not work. Only data validation drop down appears. How can I get it working in protected worksheet ? I am using MS OFFICE 2010 32 BIT and window 10 pre.


    Monday, February 9, 2015 2:24 PM
  • Hi,

    I am sorry, I posted the old file. I corrected this meanwhile. When I protect it this tempcombo does not work. Only data validation drop down appears. How can I get it working in protected worksheet ? I am using MS OFFICE 2010 32 BIT and window 10 pre.



    Hi,

    >>When I protect it this tempcombo does not work.

    Which event didn’t work? The Worksheet_BeforeDoubleClick, Worksheet_SelectionChange or TempCombo_KeyDown?

    >> Only data validation drop down appears.

    If Target.Validation.Type = 3 Then
        'if the cell contains a data validation list
        Cancel = True
        ‘other part
        cboTemp.Activate
        'open the drop down list automatically
        Me.TempCombo.DropDown
    

    Based on these words and the code above, did you mean the combobox dropped down like the screenshot below?

     

    Since it only dropped down when Target.Validation.Type = 3, as the XlDVType Enumeration and the comments of that code shared, when the data validation type for the Target cell (the active cell) is checked, It is the right behavior that the combobox will drop down only if the validation type is 3 (a drop down list).

    I assume you enabled “Edit objects” when you protect that sheet because I could not get that combobox drop down if I disabled it, right?

    If that is not the case, it will help us to clarify this issue if you could share us the sample document and the screenshot.

    >> I am using MS OFFICE 2010 32 BIT and window 10 pre.

    I would recommend you test it on another OS like Windows 7 or Windows 8.1, if this issue could only be reproduced on Windows 10 Pre, you could submit it with the way How to use Windows 10 Technical Preview  shared.

    Regards,

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, February 10, 2015 5:35 AM
  • Hi Carl Cai,

    Thank you.

    It was edit enable issue.


    Tuesday, February 10, 2015 2:32 PM