1 Attachment(s)
Automatic Show Drop down Lists
Some extra info for this Thread
https://excelfox.com/forum/showthrea...rop-Down-Lists
This is just some extra information, just out of passing interest.
You may be interested in adding another macro in the Appraisals worksheet object code module, a Private Sub Worksheet_SelectionChange(ByVal Target As Range) , :
https://imgur.com/BssuDnk , https://i.imgur.com/BssuDnk
https://i.imgur.com/BssuDnk.jpg
This macro will make the drop down lists appear in columns D an E when the cell is selected:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rem 1 Restrict most of the macro workings to only be done when specific ranges are chosen. In our case those ranges are column A and C
If Application.Intersect(Target, Me.Range("D2:E8")) Is Nothing Then
' do nothing because there was no intersection of the changed range, Target, and the cells of lists 3 and 4
Else ' https://www.mrexcel.com/board/threads/auto-show-drop-down-list-when-selecting-the-cell.1144911/#post-5550521
Dim lDVType As XlDVType
If Target.Cells.CountLarge = 1 Then
On Error Resume Next
lDVType = Target.Validation.Type
On Error GoTo 0
If lDVType = xlValidateList Then SendKeys "%{down}"
End If
End If
End Sub
This macro will make the drop down lists appear in columns C and D an E when the cell is selected:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rem 1 Restrict most of the macro workings to only be done when specific ranges are chosen. In our case those ranges are column A and C
If Application.Intersect(Target, Me.Range("C2:E8")) Is Nothing Then
' do nothing because there was no intersection of the changed range, Target, and the cells of lists 2 and 3 and 4
Else ' https://www.mrexcel.com/board/threads/auto-show-drop-down-list-when-selecting-the-cell.1144911/#post-5550521
Dim lDVType As XlDVType
If Target.Cells.CountLarge = 1 Then
On Error Resume Next
lDVType = Target.Validation.Type
On Error GoTo 0
If lDVType = xlValidateList Then SendKeys "%{down}"
End If
End If
End Sub
This macro will make the drop down lists for any cells appear when selecting the cell associated with the list.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rem 1 Restrict most of the macro workings to only be done when specific ranges are chosen. In our case those ranges are column A and C
If Application.Intersect(Target, Me.Range("C2:E8")) Is Nothing Then
' do nothing because there was no intersection of the changed range, Target, and the cells of lists 2 and 3 and 4
Else ' https://www.mrexcel.com/board/threads/auto-show-drop-down-list-when-selecting-the-cell.1144911/#post-5550521
Dim lDVType As XlDVType
If Target.Cells.CountLarge = 1 Then
On Error Resume Next
lDVType = Target.Validation.Type
On Error GoTo 0
If lDVType = xlValidateList Then SendKeys "%{down}"
End If
End If
' .....Merged cells often cause difficulties with vba code. ..... https://www.mrexcel.com/board/threads/auto-show-drop-down-list-when-selecting-the-cell.1144911/#post-5550550
If Application.Intersect(Target, Me.Range("A2:B8")) Is Nothing Then
' do nothing because there was no intersection of the changed range, Target, and the cells of list 1
Else
If Target.Cells.CountLarge = 2 And Target.Rows.CountLarge = 1 Then
SendKeys "%{down}"
End If
End If
Ref
https://www.mrexcel.com/board/thread...-cell.1144911/
https://excelfox.com/forum/showthrea...cting-the-Cell
Share ‘Appraisal - Automatic Drop Down.xls’ : https://app.box.com/s/wj11tpgc9fsuoekp023cd7ndkkqyvtm1
