PDA

View Full Version : Combo Box Data Validation Macro not working



bglumac
10-18-2016, 04:27 AM
Hi guys,

I'm a noob in this area but I'm trying to make my daily job easier...
To do so, I created an excel order page with some VLOOKUP and data validation dependent drop down lists.
I have a mess with internal codes and linking between codes so the purpose of this tool is to always return the correct code and export it to an .xml file for upload in SAP CRM.

So, Cells in Column F have name ranged drop down list.
Cells in Column G have dependent drop down list defined by another name range.
As in drop down list only 8 rows cean be displayed, it becomes quite hard to navigate (no mouse scroll also) when there is a lot of rows defined under one name.
https://s21.postimg.org/nr8viryv7/Untitled_2.jpg (https://postimg.org/image/nr8viryv7/)

Because of it, I added VBA temp Combo box in sub Worksheet_BeforeDoubleClick.
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

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 + 1
.Height = Target.Height + 1
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
Me.TempCombo.DropDown
End If

errHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Private Sub TempCombo_LostFocus()
With Me.TempCombo
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End Sub
Problem starts in dependent dropdown box as it's returning only "-" or text that was already in it.
No drop down list is shown, like it's not dependent any more.
https://s11.postimg.org/5bg1r7667/Untitled_4.jpg (https://postimg.org/image/5bg1r7667/)

Also, when I type first letter inside Combo box I dont get suggestions, why?
I selected MatchEntryComplete option in the Combo box properties.
https://s9.postimg.org/ku7kc39x7/Untitled_5.jpg (https://postimg.org/image/ku7kc39x7/)

I searched trough threads but any of the sugesstions isn't working.
I can upload the file if needed.

THX in advance