none
Data Validation Combo Box RRS feed

  • Question

  • Hi, I recently stumbled across this combo box tutorial regarding auto completing data validation lists.

    http://www.contextures.com/xlDataVal14.html#Top

    Now, this works great if my data validation list is on the same sheet, however when my list is on another sheet this will not work.

    Any help would be great, I'm terrible with vba D: 

    Tuesday, January 26, 2016 9:36 AM

Answers

  • >>>Now, this works great if my data validation list is on the same sheet, however when my list is on another sheet this will not work.

    According to your description, I have made a sample with your providing link, I suggest that you could create data validation ComboBox using Named Ranges.

    First create Name Ranges, refer to below screenshot:

    then refer below code:

    Option Explicit
    Private Sub TempCombo_KeyDown(ByVal _
            KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        'Hide combo box and move to next cell on Enter and Tab
        Select Case KeyCode
            Case 9
                ActiveCell.Offset(0, 1).Activate
            Case 13
                ActiveCell.Offset(1, 0).Activate
            Case Else
                'do nothing
        End Select
    End Sub
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    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
       Cancel = True
        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
      End If
      
    errHandler:
      Application.EnableEvents = 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 = False
    
    If Application.CutCopyMode Then
      'allows 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.ScreenUpdating = True
      Application.EnableEvents = True
      Exit Sub
    End Sub

    In addition, could you provide more information about your issue, for example sample code, screenshot etc., that will help us reproduce and resovle it.

    Thanks for your understanding.


    Wednesday, January 27, 2016 6:10 AM