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?

 

[SkipVought]

The tables however all have blank rows at the bottom."

Well THAT's your problem!

Why do you have blank rows in ANY table? Bad practice!

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

 

[grnzbra]

I am modifying an existing workbook and the tables all had the one blank row. I don't know why it is there but when deleting, there is code to be sure the blank row remains. So I can't get rid of it.

I think the extra row is to create an unprotected row on a worksheet where all cells are protected except those in the table. If it was not there, the user would not be able to add a new row.

 

[SkipVought]

Well your designer screwed you. When you go to analyze the table you have at least one row of invalid data! If you want to assure table integrity, then you complete isolate the user from the table via a form, not screw up the data on the table.

But even if you were using the Structured Table feature, when the user TABS from the last row of data, creating a new row, the Data Validation cell will display as described. So we're really back to the beginning. I do not understand, "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."


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

 

[grnzbra]

Actually, it wasn't our designer who hung us on the table rather than the form. I believe it was something the customer insisted on.

In any event, to explain the difference in the way things default.

We have named ranges that supply unchanging values. They don't change and values are neither added nor deleted. There are no rows without data.

We also have a main worksheet in which the customer manually enters a key value that is meaningful to the customer. As soon as this value is entered, a check is run to be sure it doesn't already exist, so it can be used as a key. All the remaining sheets refer to various aspects of the items entered on the first sheet and the first column on each table of the following worksheet through a drop down created by going to the menu and selcting Data > Data Tools > Data Validation.

When one goes to any of the sheets after the first one and selects the drop down for the key field, the last entries in the list show up with a highlight on the empty field (since that is always the bottom field of the range). Not really a problem when dealing with only a few rows of data. However, typically, the first sheet will have from 800 to 1000 items.

The first figure below is the key field from the first sheet. The second figure is how the drop down appears on the second sheet. The third figure is a drop down from one of the fixed ranges.

Perhaps the reason for using tables instead of forms for entering data is to have the ability to run a query in SQL Server and then copy and paste all the resulting data into the spreadsheet. But that is just a guess in my part.

 

[SkipVought]

Well a user form would not preclude being able to add blocks of data otherwise.

I set up a DV in-cell drop down and there is no default to the last value in the list. What code is being used that might cause this behavior.

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

 

[grnzbra]

Yes, but a form is not what the customer wants. We've tried telling that's what they should use, but they want what they want and we've got to give it to them.

Code is not being used to create the drop down. It was set up from the menu bar Data > Data Tools > Data Validation > List > "=EquipmentID" (the named range). If the blank line was not there, new records would not be included in the range and if all records were deleted, the named range would disappear.

I came across the following code that works very nicely to create a validation drop down box in a cell.

Visual Basic for Applications 
With Range("e5").Validation
    .Add xlValidateList, xlValidAlertStop, xlBetween, "=$A$1:$A$10"
    .InCellDropdown = True
End With

This works very nicely when the source range is on the same sheet as the cell being validated. I need it to refer to a range on a different sheet and have tried various modifications but get various errors, depending on what I try (It has always been something variation of Sheets("Sheet2").Range($A$1:$A$10"). Also, it only works once. Can't figure out how to change it as records are added to and deleted from the main table. Is there any way way to code a named range? That would allow me to manually change the named range to match only the rows with records.

 

[SkipVought]

I must be grossly misunderstanding your problem.

As I understood you, the only blank rows are in the tables containing the data validation cells.

However you just stated, "if the blank line was not there, new records would not be included in the range and if all records were deleted, the named range would disappear." You're referring to the range for the DV DropDown and that is absolutely an incorrect assumption, that if the blank line was not there, new records would not be included in the NAMED RANGE.

It sure would be helpful if you could upload a representative copy of your workbook that demonstrates these issues. I've used DV DropDowns for many years and never had issues.


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

 

[grnzbra]

Unfortunately, the workbook, while not classified is considered sensitive.

Create a range, let's say A1:A10 and name it myRange. Add values 1 through 10 to cells A1 through A10 and select myRange. All the values just entered will be in the range. If you then add 11 to cell A11 and select myRange, the cell A11 will not be included in the range. If you then delete all the rows from 1 to 11 (delete the rows not just clear the contents of the cells) and try to select myRange, you will find it no longer exists.

I did, however find a programmatic way to modify the range on the main sheet. this goes into the Worksheet_Change() event

    ActiveWorkbook.Names.Add _
        Name:="EquipmentID", _
        RefersTo:="=Equipment!$F$" & firstDataRow & ":$F$" & lastDataRow - 2

 

[SkipVought]

Yes, that is one way, to do I via code.

Some people choose not to use code. In that event, making the table a Structured Table, will 1) name the table and 2) name the data ranges. Then to be able to use the structured name to reference a DV LIST, you must create a named range in the Formulas > Defined Names > Name Manager, by making a New... Range Name, that Refers to:
[tt]
=Table1[myRange]
[/tt]
...in Structured Table referee syntax.

So NO BLANK ROW HERE!

Does this solve the stated issue?

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

 

[SkipVought]

regarding your code, you can make this more universal.

If you have one sheet with all your lists...

Private Sub [b]Worksheet_Change[/b](ByVal Target As Range)
    Application.DisplayAlerts = False
    Target.CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
    Application.DisplayAlerts = False
End Sub

If you have multiple sheets for your lists...

Private Sub [b]Workbook_SheetChange[/b](ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        Case "List1", "List2", "List3"
            Application.DisplayAlerts = False
            Target.CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
            Application.DisplayAlerts = False
    End Select
End Sub

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

 

[grnzbra]

I looked into the .CreateNames type code but couldn't make it work.(don't remember what the problem was)

I've set up the .add code above on one of the ranges and it works very nicely. However, the word Equipment refers to the name of the sheet that appears on the spreadsheet. In the case of the Equipment sheet, this is the same as the name that appears at the top of the Properties list for that sheet. However, there is a second sheet that I need to be able to change the range that is on a sheet with the name PM Template appearing on the tab at the bottom of the sheet(it has a space in it). The name at the top of the Properties list for that sheet is PMTemplate.

I've tried putting brackets around the PM Template the way I would with Access or SQL Server, but that doesn't work. PMTemplate doesn't work. Is there any way to set this up so that this code will work? (I'm trying to avoid any major in the code for this thing).

 

[grnzbra]

I found the solution to the PM Template name problem - put single quotes around it.

ActiveWorkbook.Names.Add _
        Name:="rngPMTemplateName", _
        RefersTo:="='PM Template'!$C$" & firstDataRow & ":$C$" & lastDataRow - 2

 

[SkipVought]

I looked into the .CreateNames type code but couldn't make it work."

You must SELECT the data & the cell(s) containing the NAME(s) for the range. Hence [tt]Target.CurrentRegion[/tt]

"However, the word Equipment refers to the name of the sheet that appears on the spreadsheet. In the case of the Equipment sheet, this is the same as the name that appears at the top of the Properties list for that sheet. However, there is a second sheet that I need to be able to change the range that is on a sheet with the name PM Template appearing on the tab at the bottom of the sheet(it has a space in it). The name at the top of the Properties list for that sheet is PMTemplate. "

Well a Range Name cannot contain any SPACE character. So when I design a table, I's use a name like PMTemplate or PM_Template as a header in my table, so that my lookup Range Name would match the header name.

"I've tried putting brackets around the PM Template the way I would with Access or SQL Server, but that doesn't work. PMTemplate doesn't work. Is there any way to set this up so that this code will work?"

You have disclosed nothing that tells me what you're trying to do with PM Template. You won't upload even a cleaned up sample of ANY of these issues in a workbook, so it is very difficult to determine exactly what the nature of these problems are. I've already stated that the blank row at the bottom of your lookup tables is irrelevant and I have no idea why you are subtracting 2 from the lastrow in your code. This I cannot figure out! I feel as if I am tilting at windmills.


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

 

[grnzbra]

The problem with PMTemplate and PM_Template is that this is to be used by real people. Normally I would use PMTepmlate. However, on the tabs on the bottom of the sheet, PMTemplate and PM_Template would look weird. No_one_writes_the_English_language_like_this. OrThis. It's ok for a database with an interface where the customer doesn't see the table, but where the customer sees the table it's not acceptable.

The subtraction of 2 is because the last data row value is either 1 or 2 more than the actual last row of the table. Don't know why this occurs so I'm not about to change it, but there is code to add a empty row at the bottom of the table after each new record is added, and other code to make sure there is a blank line in an empty table.

 

[SkipVought]

I'm in the dark!

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

 

[grnzbra]

Me too
I can handle VBA in access and VB linked to Access or SQL Server as well as stored procedures in SQL Server. But this VBA in Excel is all mud to me. In order to do anything here, I had to look at how the original designer (who is long gone) did things to see if there was something similar to what I wanted to do.

I do thank you; your answers got me thinking in ways I wouldn't have.

By the way, I create a sample workboook to try to illustrate the problem, but couldn't figure out how to upload it here.

 

[SkipVought]

Below the Reply To This Thread window: Attachment Click here...

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

 

[grnzbra]

Here is a workbook to illustrate

 

[SkipVought]

Okay, got your sample workbook. Thank you!

I see no code although your workbook is a macro enables workbook. I assume that there is no code in this workbook by design.

I see your two lookup tables and two tables with DV DropDowns.

Only one table has a blank row.

The way to get a NEW blank row is to TAB from the bottom right cell in the table.

Beyond that, what other issues are there?

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