Understand Validation Property for specifying another sheet in ComboBox list

dclasen

New Member
Joined
Dec 4, 2012
Messages
21
I'm using what is similar VBA I see in examples on the web of a ComboBox which looks at the validation in the cell and retrieves the list for the ComboBox. However, the code is designed for all the lists on the same worksheet. I have the lists on a different sheet. If I explicitly fill the ListFillRange it works of course for a single validation list, but the idea is the ComboBox appears in the active cell and if there is validation it reads the source. I'm hoping somebody can explain more how this works so I can mod the code to look at the lists on the other worksheet.

'.ListFillRange = "'" & Sheets("Lists").Name & ";!" & Sheets("Lists").Range(str).Address
.ListFillRange = ws.Range(str).Address


I'll encase all the code and even leave in my commented out last attempt... I'm not good but I'm trying. Thanks for any help.

Code:
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
Set ws = ActiveSheet
On Error GoTo errHandler


If Target.Validation.Type = 3 Then
Cancel = True
End If


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 = "'" & Sheets("Lists").Name & ";!" & Sheets("Lists").Range(str).Address
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown
  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


Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If


errHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub
 
Last edited:

Forum statistics

Threads
1,081,528
Messages
5,359,311
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top