Hello,
I've posted a similar question recently but now I'm facing one last specific problem which now doesn't match my original post title... I hope someone can help me with this.
On my attached file "License Details" tab, cells B32 and down have cascading data validation with below formula.
=OFFSET(Brands!$B$1,MATCH(A32,Brands!$B:$B,0)-1,1,COUNTIF(Brands!$B:$B,A32),1)
Although I have a separate list on tab "Brands," the following code that I'm using for dropdown list formatting isn't working for this column...
I'm assuming this is because it's not a simple range but a dependent, but I don't know how to make the VBA code to apply to this as well.
Any input is much appreciated.
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 Tgt As Range
Dim TgtMrg As Range
Dim c As Range
Dim TgtW As Double
Dim AddW As Long
Dim AddH As Long
Set ws = ActiveSheet
On Error Resume Next
'extra width to cover drop down arrow
AddW = 15
'extra height to cover cell
AddH = 5
If Target.Rows.Count > 1 Then GoTo exitHandler
Set Tgt = Target.Cells(1, 1)
Set TgtMrg = Tgt.MergeArea
On Error GoTo errHandler
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
On Error GoTo errHandler
If Tgt.Validation.Type = 3 Then
Application.EnableEvents = False
If Not TgtMrg Is Nothing Then
'get total width of merged cells
TgtW = 0
For Each c In TgtMrg.Cells
TgtW = TgtW + c.Width
Next c
End If
str = Tgt.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Tgt.Left
.Top = Tgt.Top
If TgtW <> 0 Then
'use total width for merged cells
.Width = TgtW + AddW
Else
.Width = Tgt.Width + AddW
End If
.Height = Tgt.Height + AddH
.ListFillRange = str
.LinkedCell = Tgt.Address
End With
cboTemp.Activate
Me.TempCombo.DropDown
End If
exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
errHandler:
Resume exitHandler
End Sub
Bookmarks