Data Validation Dropdown Default Problem 1

[grnzbra]

I have a sheet with several reference ranges. For example, the frequency range relates symbol with definition - MO-Monthly, AN-Annually etc.

I also have, on other sheets, tables with various columns being ranges for use in data validation lists on other sheets.

All of these are named ranges.

The reference ranges don't change and every row of the range has an entry - there are no empty rows in any of these ranges. The tables, however, all have a blank row at the bottom. Any data validation list that uses a range from a reference range does not default to anything and displays from the top of the range and one must scroll down to get to the bottom. Any data validation list that references a range in one of the tables defaults to the blank value at to bottom of the range and one must scroll up to get to the top of the range. In all the data validation definitions, the "Ignore Blank" checkbox has been checked. Is there any way to get the validation lists that reference table ranges to stop defaulting?

 

[grnzbra]

Yes, but we can't be sure that the customer will enter data all the way to the last column and tab out of the row. He may go to another sheet or he may want to start a new record. So in the code for Woorksheet_change() there is a line to add another row to the table. As soon as he fills in the first field of a new record, a blank line is added to the bottom of the table.

Also, if you go to the PM Schedule sheet and clear one of the PM Template Name fields and then look at the dropdown, you will see that it defaults to the bottom (Due to the fact that the rngPMTemplateName range includes the blank row at the bottom of the PM Template table. Not a problem her with only 2 or 3 records. However, we are bringing inseveral hundred records so when one goes to use the drop down, one finds oneself at the bottom of the drop down. It's one of those things that any of us would just scroll up to what we want rather than scroll down. But scrolling down is the norm, so the customer can be expected to complain about it.

On the other hand, the EquipmentID range on the Equipment sheet doesn't include the last row, so going to any of those drop downs, one finds oneself at the top, the "normal" way things are done. What I needed to do was restrict the ranges used by drop downs to just the rows containing data.

 

[SkipVought]

Woorksheet_change() there is a line to add another row to the table.

Not in the workbook you uploaded.

Also, if you go to the PM Schedule sheet and ...

Your workbook does not have a PM Schedule sheet. There's a PM Template and a PM Template Stuff. I'm guessing that you are referring to the PM Template Stuff, that has drop downs in the PM Template Nam e column (that's exactly how the heading appears).

So I added a bunch more rows to the PM Template sheet...
[pre]
PM Template Name
Temp 1
Temp 2
Temp 3
Temp 4
Temp 5
Temp 6
Temp 7
Temp 8
Temp 9
Temp 10
Temp 11
Temp 12
Temp 13
Temp 14
[highlight #204A87] [/highlight]
[/pre]

So on the PM Template Stuff the drop down shows these values when the drop down is opened...
[pre]
Temp 1
Temp 2
Temp 3
Temp 4
Temp 5
Temp 6
Temp 7
Temp 8
[/pre]

If I scroll down, the LAST items is Temp 14. The EMPTY cell is not displayed. If I put a SPACE in the drop down list, then I see BLANK after Temp 14.

Where is this getting us? I do not see, any of the issues you are referring to.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[grnzbra]

Not getting us anywhere anymore. But the conversation has been very helpful to me in finding a solution of setting a range on the fly.

If you add another record to the PM Template Stuff sheet, you should see the drop down with the blank space at the bottom highlighted with "Temp 8" through "Temp 14" above it. The drop down defaults to whatever value in the range matches the value in the cell. In the case of this PM Template Stuff sheet, that is "Temp 1". Normally, when one first enters a record, the cell will be empty and the drop down will go straight to the bottom if there is a blank at the bottom of the range.

This code sits somewhere in the middle of the code that runs when a value is changed on the PM Template worksheet.

Private Sub worksheet_Change(ByVal Target As Range)

[I] Code associated with changes on worksheet[/I]

'Get first data row and last data row
    lastDataRow = PMTemplateList.DataBodyRange.row + PMTemplateList.DataBodyRange.Rows().Count
    firstDataRow = PMTemplateList.DataBodyRange.row

'Account for one row or no rows of data.    
    If lastDataRow - firstDataRow < 2 Then
        RangeAdjSel = 1
    Else
        RangeAdjSel = 2
    End If

'Set new parameters for rngPMTemplateName    
    ActiveWorkbook.Names.Add _
        Name:="rngPMTemplateName", _
        RefersTo:="='PM Template'!$C$" & firstDataRow & ":$C$" & lastDataRow - RangeAdjSel

[I]More code associated with changes on worksheet[/I]

End Sub