Data Validation list

[Phill032]

Hi guys,

Is there anyway to open a data validation list when the sheet is opened?
I deal with people who are not at all excel literate and constantly have to show them where the dropdown box is located.
So hoping to solve the problem with the list appearing right from the start..

 

[Peter_SSs]

Assumptions:
- The workbook only has one worksheet.
- The DV you want to drop-down on workbook open is in cell D2
- The worksheet may have other DV cells the you would also like to drop-down immediately if the user selects one of them.

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lDVType As XlDVType
    
    If Target.Cells.Count = 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

3. In the vba window, double click the ThisWorkbook module of your project and paste the following code into the right hand pane that this creates.

Private Sub Workbook_Open()
  Range("A1").Select
  Range("D2").Select
End Sub

4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).
5. After saving, close and re-open the workbook to test.

(Basis of solution came from here)

 

[My Aswer Is This]

Assuming your Data validation list in in Column "F"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Modify if needed.

Private Sub Worksheet_Activate()
Range("F1").Select
End Sub

 

[Phill032]

So the sheet is opened by a macro, and I can't seem to get either of these options to work..
The DV list is in cell U2, this is a merged cell would that make a difference?
I am assuming that the second option here from my answer is will only locate the cell thus enabling the drop down arrow to be seen.
I was hoping for the list to open up and be visible.

 

[Peter_SSs]

.. U2, this is a merged cell would that make a difference?

Yes it would. Merged cells often cause difficulties with vba code.
However, try making these modifications to my first code (2 deletions, 1 addition) then Save, Close, Open.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lDVType As XlDVType
  
  <del>If Target.Cells.Count = 1 Then</del>
    On Error Resume Next
    lDVType = Target.Cells(1).Validation.Type
    On Error GoTo 0
    If lDVType = xlValidateList Then
      SendKeys "%{down}"
    End If
  <del>End If</del>
End Sub

 

[Phill032]

Yes, worked perfectly.. thanks heaps for that.

 

[Peter_SSs]

Yes, worked perfectly.. thanks heaps for that.

That's good news.

However, as well as when the workbook opens, the code runs every time the user selects a different cell or range and that could have an impact on the sheet's performance. It may be able to be further restricted depending on ..

a) Whether this assumption is correct or not

- The worksheet may have other DV cells the you would also like to drop-down immediately if the user selects one of them.

Can you advise whether that is the case or not?


b) Do you need the DV in U2 to automatically drop down if the user selects that cell some time after the workbook has been opened, or only that one time as it opens?

 

[Phill032]

It seems to work fine, i have several Validation lists within the workbook that i have applied it to. However i have not entered the code into the startup but into the macro that opens the sheet.
The list only drops down when the sheet first opens and only when the cell has been selected, which is exactly what i wanted.

 

[Peter_SSs]

..which is exactly what i wanted.

OK, that's great!