• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation and Combo Box with Named Ranges

DMurray3

New Member
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:
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
 
DMurray

Firstly, Welcome to the Chandoo.org Forums

Can you post the entire file to make it easier to resolve
 
Hi Murray ,

I don't find any problem with the code. Can you see this file ?

Narayan
 

Attachments

  • Book1.xlsm
    22.4 KB · Views: 7
Hi Murray ,

I don't find any problem with the code. Can you see this file ?

Narayan

Hi Narayan,

On loading your book1 file stopped at "Set cboTemp = ws.OLEObjects("FVIngresoCombo")" line in the "PrivateSub Worksheet_SelectionChange(ByVal Target As Range)" Sub..

On second loading of said file, the combo box on double-click does NOT appear on fields/cells H3 nor H5.

Somewhere to look for an error?

Thanks.
 
DMurray

Firstly, Welcome to the Chandoo.org Forums

Can you post the entire file to make it easier to resolve

Hi Hui... In some forums one is not allowed to attatch files... great if on this one, we can...

Enclosed is my actual file. It's a multipurpose Purchase & Sale Invoice DataEntry System I am attempting to build based on on different macros I have been able to gather and adapt for my use.

Sheet "FVIngreso" is where I am trying to get the combo box / data validation to work in cells $h$3 and $h$5.

Thanks for your interest and support.

Regards, DMurray3
 

Attachments

  • SC4.xlsm
    413.3 KB · Views: 15
Hi Murray ,

I downloaded the file from my post as well as yours , and I did not face any problem.

Two of the selections in H3 do not have any options to be displayed in H5. The remaining 3 selections do display the combobox list.

Narayan
 
I'll re check to see if my problem has anything to do with interruptions of the "Application.EnableEvents = False" / "Application.EnableEvents = True".... or maybe the sequence of my "Sub's" are off... I´ll let you know....

Again, many thanks for your interest and support !!
 
I'll re check to see if my problem has anything to do with interruptions of the "Application.EnableEvents = False" / "Application.EnableEvents = True".... or maybe the sequence of my "Sub's" are off... I´ll let you know....

Again, many thanks for your interest and support !!

I solved my problem... Apparently is was the "Application.EnableEvents = False" / "Application.EnableEvents = True"....

I shut down my Excel and restarted, ans ever since, no problems.

Case closed. I appreciate very much your help. KR, DMurray3
 
Back
Top