VBA - Set data list

[joshua1990]

Hey guys!


I have the problem that some users destroy certain Excel files by moving the formatting or similar. So it often happens that the data list, which applies to the area D3: Z20, often crashes. This data list is based on a selection from Table 2 using the name manager.


Now I would like to know that when starting the Excel file, this data list is always updated to a defined range.

How is this possible?


With the recorder I couldn't develop an approach.




greeting
Joshua

[mancubus]

try Workbook_Open event.

[joshua1990]

thanks! of course, the code is placed in the workbook_open event.
But I can't create a loop, that creates a datalist/ combo field of the value of the range tbl2("D4:End") for tbl1("D3:Z20")

Is there any help?

[mancubus]

post your code and workbook pls.

[joshua1990]

Ok cool, now it works (I think)
But I need a border or something that sets a dynamic end/ last cell for the Range "Formula1:="='Tbl2'!$D$4:$D$14"".
So maybe the next time da date list is based on the value of D4:D20.
How can I manage that?


Sub SetDataList() Worksheets("Tbl1").Range("D3:Z22").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="='Tbl2'!$D$4:$D$14"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub




Are there any points in the code that could be optimized?
21551


Thanks a lot!

[SamT]

The reason I avoid using Named Ranges in Code.

If the list is always in D3:Z20, then a simple

Set dataList: = SomeSheet.Range("D3:Z20")

If the Users change sheet names, merely change the Code Name of the sheet in the VBA Properties Window and use the CodeName in your code. ie Change the Code Name of that sheet to "MasterList," then in code

Set DataList = MasterList.Range("D3:Z20")


Sometimes, you may want to refer to "Named" Columns by header String. The identical Function in all such Worksheets can provide this functionality without using User accessible Named Ranges. Again, change the Sheet's Code Name.

In each such worksheet

Public Function GetColumn(HeaderValue As String) As Variant
Const HeaderRow as Long = 1
Set GetColumn = Rows(HeaderRow).Find(HeaderValue).EntireColumn
End Function
To Use in Code

Set WorkingColumn = MasterList.GetColumn("Employee Id Number")
Set OtherVariable = OtherSheetCodeName.GetColumn("Other Column Header")

This can be refactored to return other type Ranges. ie, for a sheet with several Tables separated by empty cells

Public Function GetTable(TableLabel As String) As Variant
Set GetTable = Cells.Find(TableLabel).CurrentRegion
End Function
Obviously, such a function can easily return Arrays of values.

[joshua1990]

Thanks a lot for your advice and approach!

I am looking for something for a dynamic range for the Data list.
It always starts at D4 but the end cell is dynamic.

How could I manage this?

[mancubus]

Sub vbax_61940_validation_list_based_on_dynamic_range()

Dim LastRow As Long

With Worksheets("Tbl2")
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

With Worksheets("Tbl1")
With .Range("D3:Z22").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="='Tbl2'!$D$4:$D$" & LastRow
End With
End With

End Sub

[SamT]

One consideration when designing Workbooks (Projects) is to keep data layed out in a manner optimized for code, Another is keeping Reports layed out in a manner optimized for Humans. Many Reports may extract many different sets of knowledge from the same Data.

Even in sheets solely used for Code, one must keep humans in mind... Code does not require Column Headers, but Coders do. The best 'for code' Data is layed out with Headers in Row 1 and all data starting in cell A2.

The fact that your list starts in D4, tells me that your Code Data Sheet is layed out for humans, ie, it is a mixed Data/Report sheet. But Data is Data, it never changes. Reports include many arithmetic results; Averages, Sums, Percentages, etc, IOW, non Data that always changes, if only on the basis of date.

The fact that your data's block size changes is proof that the Data Sheet is not optimized as a pure Data Sheet for Code.

In the case of a List, that Block is typically one column, although Lists for ListBoxes and ComboBoxs may contain a few more columns. for a Table, the number of columns can get quite high. For example a by-Date Table can contain more than 366 columns. In either Case the number of columns is mostly fixed once they have been determined. Note that I have not mentioned the number of Rows since it is always expected that more data can and will be added as time goes on.

Once you have separated the User interaction sheets and Forms from the Data, you can hide Data sheets so that Users can not even see them, almost guaranteeing that the Users will never mess with them. At this point, there is never an issue with determining the exent of the data.


I am looking for something for a dynamic range for the Data list.
It always starts at D4 but the end cell is dynamic.

How could I manage this?That really depends on the Physical layout of your data sheet. With a mixed Data/Report sheet, it could be complicated.