Results 1 to 38 of 38

Data Validation Combobox Help

Threaded View

  1. #1
    Registered User
    Join Date
    03-12-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    19

    Data Validation Combobox Help

    Hi All
    I am rather new to Excel so excuse me if this is a dumb/old question.

    I am trying to use the code developed by Contextures.com to link a Combobox to a Dropdown box.

    I can get the Combobox to show the data from the table that I have created but as soon as I have copied and pasted the code into the VBA, I double click on a Dropdown box and it is blank??

    The sheet with the Dropdown Lists & Combox is named "Test Results" and the sheet with the Table is called "Max Zs values".

    I have posted the code and the file attachment below. Let me know if you require any more info.

    I have tried looking at the example workbook on the Contextures website but I can't see where I am going wrong.

    Can anyone help?

    Thanks in advance.
    Ben


    Option Explicit
    ' Developed by Contextures Inc.
    ' www.contextures.com
    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_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("Max Zs values")
    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
    
    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
        'open the drop down list automatically
        Me.TempCombo.DropDown
        End If
    
    errHandler:
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Exit Sub
    
    End Sub
    Attached Files Attached Files
    Last edited by BennyL; 03-12-2016 at 11:45 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data Validation + ComboBox Autocomplete
    By kiwi_harry in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-16-2014, 10:15 PM
  2. Combobox with Data Validation so that same value cannot be selected
    By brillison in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2014, 11:02 PM
  3. Data Validation ComboBox Error
    By shnoli in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2013, 05:53 PM
  4. Replies: 0
    Last Post: 10-21-2012, 06:25 AM
  5. Another Dependent Data Validation Combobox
    By blacque in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-07-2009, 03:40 AM
  6. ComboBox in a Data Validation List...
    By Bob Barnes in forum Excel General
    Replies: 3
    Last Post: 01-06-2006, 07:10 PM
  7. data validation vs combobox
    By paul in forum Excel General
    Replies: 4
    Last Post: 10-24-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1