locked
TEMPCOMBO opens in double click RRS feed

  • Question

  • Hi,

    https://social.msdn.microsoft.com/Forums/office/en-US/01ba96be-5801-4eda-95d9-e01cea835567/temp-combobox-doesnot-work-in-protected-sheet?forum=exceldev

    '==========================

    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("TempCombo")
      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.tempcombo.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("TempCombo")
      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 question is , the tempcombo needs double click to open dropdown. Is it possible that it appears when mouce hover over the cell or with one click ??


    Wednesday, February 11, 2015 1:58 PM

Answers

  • Hi,

    now it is working with

    Private Sub TempCombo_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ActiveSheet.TempCombo.DropDown
    End Sub

    regards

    drsantosh

    Monday, February 23, 2015 3:20 PM

All replies

  • Hi drsantoshsinghrathore,

    I made a simple with your code, and I could reproduce your issue. Based on the code, I think this behavior is expected since the tempcombo needs double click to open dropdown in your code. And if you want to achieve it with one click, I think you could remove the "Worksheet_BeforeDoubleClick" and modify the "Worksheet_SelectionChange".

    The simple code as below:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim wsList As Worksheet
    Set ws = ActiveSheet
    Set wsList = Sheets("ValidationLists")
    Set cboTemp = ws.OLEObjects("TempCombo")
      On Error Resume Next
      With cboTemp
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
      End With
    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 15
          .Height = Target.Height + 5
          .ListFillRange = str
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        Else
        With cboTemp
        .Top = 10
        .Left = 10
        .Width = 0
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
      End With
      End If  
    errHandler:
      Application.EnableEvents = True
      Exit Sub
    End Sub

    Best Regards,

    Edward


    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.



    Thursday, February 12, 2015 11:01 AM
  • Hi Edward Z,

    Thank you for your kind help.

    Actually in working it still needs double click. I wanted either with mouse hovering over or with one click in the cell. It is only to avoid double click and make it more convenient.

    regards

    Thursday, February 19, 2015 11:09 AM
  • Hi drsantoshsinghrathore,

    >> Actually in working it still needs double click. I wanted either with mouse hovering over or with one click in the cell. It is only to avoid double click and make it more convenient.

    I made a test again but I failed to reproduce your issue by using the code in my last post. It would be helpful if you could share us a sample spreadsheet through OneDrive to reproduce your issue.

    Best Regards,

    Edward


    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.


    Friday, February 20, 2015 6:31 AM
  • Hi,

    Thank you for your reply,

    By double click I mean, once we have to enter the cell by clicking it and then again we need to click there to open combo box. The same we had to do in previous version of code. Though improment in working and vibration on worksheet is now corrected. I want it to open combobox as soon as we select cell or we click in cell.

    https://onedrive.live.com/redir?resid=90B1344C0622B513!6807&authkey=!AJ7yUMpeX1O3aP8&ithint=file%2cxlsm

    https://onedrive.live.com/redir?resid=90B1344C0622B513!6807&authkey=!AJ7yUMpeX1O3aP8&ithint=file%2cxlsm

    http://1drv.ms/1ExpOGs

    regards


    Friday, February 20, 2015 2:31 PM
  • Hi drsantoshsinghrathore,

    >>The same we had to do in previous version of code. Though improment in working and vibration on worksheet is now corrected. 

    I have made a test with your excel file, and when you click the cell one time, the combobox has been show. At this time, you need to click the narrow down on the right to drop down the combobox list. It is due to that when you click the cell, you just set the combobox visible and the combobox did not get the click, so you need to click again.

    >>I want it to open combobox as soon as we select cell or we click in cell.

    I think you could add a Change event on the combobox. The code below shows the details.

     Private Sub TempCombo_Change()
    ActiveSheet.TempCombo.DropDown
    End Sub

    Best Regards,


    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 23, 2015 9:29 AM
  • Hi Edward Z Pactera,

    Thank you very much,

    But it does not work always in the same way ? I put the same code from above.

    drsantosh

    Monday, February 23, 2015 2:57 PM
  • Hi,

    now it is working with

    Private Sub TempCombo_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ActiveSheet.TempCombo.DropDown
    End Sub

    regards

    drsantosh

    Monday, February 23, 2015 3:20 PM
  • Hi drsantoshsinghrathore,

    I am glad your issue has been resolved.

    Best Regards,

    Edward


    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.


    Wednesday, February 25, 2015 6:01 AM