Hi all,
I am trying to use the referenced code on a spreadsheet used for DataEntry; only two cells are to work with the referenced. The code was obtained from your friends at http://www.contextures.com/xlDataVal11.html (great tutorial btw), with a minor tweek on my behalf to limit it's use to two cells on my DataEntry spreadsheet.
The first cell works correctly, while the second cell does NOT show the drop-down list pertinent to that second cell (ie. it comes out blank)...
The DV are derived from Dependent Dynamic Ranges against Excel Tables in other worksheets, and both work correctly.
The code I am using is:
I am not an experienced VBA programmer, and would appreciate all the help / guidance you can provide.
Kind regrads
New forum member DMurray3
I am trying to use the referenced code on a spreadsheet used for DataEntry; only two cells are to work with the referenced. The code was obtained from your friends at http://www.contextures.com/xlDataVal11.html (great tutorial btw), with a minor tweek on my behalf to limit it's use to two cells on my DataEntry spreadsheet.
The first cell works correctly, while the second cell does NOT show the drop-down list pertinent to that second cell (ie. it comes out blank)...
The DV are derived from Dependent Dynamic Ranges against Excel Tables in other worksheets, and both work correctly.
The code I am using is:
Code:
'Data Validation Drop Downs With Combo Box Using Named Ranges
'from www.contextures.com
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'*****To Define the cells where the DV w/CBx will be applied*****
If Not Intersect(Target, Target, Range("H3,H5")) Is Nothing Then
'*******************************
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("FVIngresoCombo")
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 + 15
.Height = Target.Height + 10
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically - this works for the 1st cell (h3) but not for the second cell (h5)
Me.FVIngresoCombo.DropDown
End If
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 = True
If Application.CutCopyMode Then
'allow copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("FVIngresoCombo")
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 FVIngresoCombo_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 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
I am not an experienced VBA programmer, and would appreciate all the help / guidance you can provide.
Kind regrads
New forum member DMurray3