VBA Dropdown, not working on older versions of excel

[BlakeBessigner]

Mainly looking for What version of Excel is causing this issue and how to work around it. The VBA code pastes all the devices I need to a column on the "Dropdowns" sheet, then it uses the Sort, Unique, and Filter functions to create the array of devices the dropdowns point to. When I run this on my machine it works 100% of the time, but when this code is ran on my Boss's machine it fails with an error. 

Here is the code before (with added comments to help understand my intents, and unincluded variables) :

    'not shown variables in this code snippit
    'With Worksheets("Structure") which is the sheet the dropdown is for
    'NewRow is the row on that structure sheet, PLCtable is the table the info is at
    'DropdownStart is the string for the column letter the list of devices is on
    'DropdownColumn is the integer for the column the list of devices is on
    'The formula is pasted on the column next to the list of devices it refers to
    
    'pastes the ModuleDrop array onto the column DropdownStart to ensure the Device list is created and up to date
    Worksheets("Dropdowns").Range(DropdownStart & "3").Resize(UBound(ModuleDrop) + 1).Value = WorksheetFunction.Transpose(ModuleDrop)
    
    'creates the formula to reference the newly updated Device list
    'results look like this: =SORT(UNIQUE(FILTER($A$3:$A$1000,$A$3:$A$1000<>"")))
    Worksheets("Dropdowns").Cells(3, DropdownColumn + 1).Formula2 = "=SORT(UNIQUE(FILTER($" _
        & DropdownStart & "$3:$" & DropdownStart & "$1000,$" & DropdownStart & "$3:$" & DropdownStart & "$1000<>" & Chr(34) & Chr(34) & ")))"
    'changes DropdownStart to be the column letter of the formula column
    DropdownStart = Split(Worksheets("Dropdowns").Cells(1, DropdownColumn + 1).Address, "$")(1)
    
    'adds the validation to reference the corresponding formula column
    .Cells(NewRow, PLCtable.ListColumns("Device").Index).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Dropdowns!" & "$" & DropdownStart & "$3#"
    .Cells(NewRow, PLCtable.ListColumns("Device").Index).Validation.IgnoreBlank = True

This is the "on the fly" workaround I made so the boss can continue to work. The Dropdown luckily isn't crucial, but definitely needs to be there in the future since it's a feature they asked for. 

    'error handling for something that is only happening on Mark's computer. possibly a version issue
    On Error Resume Next
    Worksheets("Dropdowns").Cells(3, DropdownColumn + 1).Formula2 = "=SORT(UNIQUE(FILTER($" _
        & DropdownStart & "$3:$" & DropdownStart & "$1000,$" & DropdownStart & "$3:$" & DropdownStart & "$1000<>" & Chr(34) & Chr(34) & ")))"
    If Err <> 0 Then
        Call ErrorMessage(Errors, "Creating Device Dropdown", "I think the formula I'm using is version exclusive to the latest Excel version")
        Call ErrorMessage(Errors, "Creating Device Dropdown", "Panel: " + Panel + ", Slot: " + CStr(SlotsArray(Count, 1)) + ", Device: " + CStr(SlotsArray(Count, 2)))
    End If
    On Error GoTo 0
    
    DropdownStart = Split(Worksheets("Dropdowns").Cells(1, DropdownColumn + 1).Address, "$")(1)
    On Error Resume Next
    .Cells(NewRow, PLCtable.ListColumns("Device").Index).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Dropdowns!" & "$" & DropdownStart & "$3#"
    
    .Cells(NewRow, PLCtable.ListColumns("Device").Index).Validation.IgnoreBlank = True
    On Error GoTo 0

The ErrorMessage() subroutine is some error handling I have built into this excel. I was using to see if it was the Device referenced or just the dropdown formula in general. It was happening on all Devices I feed it.

Also is there anything else along this line to look out for in the future? Sorry for my suboptimal code, I'm still learning VBA and am an Electrical Engineer by trade. I originally had it just use a string for the dropdown (IE. "Device1,Device2,Device3"), but those fail when they're more then 256 characters and there are too many devices in the dropdowns to fit in that limit.

Note: I would attach the entire Excel if I could, but I'm unsure on the legality since it's technically company property at this point.

[#2]

@BlakeBessigner 

---

@BlakeBessigner wrote:

Mainly looking for What version of Excel is causing this issue and how to work around it. The VBA code pastes all the devices I need to a column on the "Dropdowns" sheet, then it uses the Sort, Unique, and Filter functions to create the array of devices the dropdowns point to. When I run this on my machine it works 100% of the time, but when this code is ran on my Boss's machine it fails with an error. 

Try using "Search_deList_v2.1", it's a free Excel add-in, it works on Excel 2007 or later.
Its function is to speed up the search in the data validation list. In any cell that has data validation (with type List) pressing ALT+RIGHT arrow will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list. It also gives you a unique & sorted list.
By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx.

This new version has some additional features, some of them:
Several ways to search, like using AND or OR or LIKE operator , with or without keyword order.
Sort the list by original order or ascending order.
Widen or shorten the combobox width at run time.
Insert multiple entries into the cell.

You can find it here:

Search deList 

[#3]

https://club.excelhome.net/forum.php?mod=viewthread&tid=567518&extra=&mobile=

[#4]

This is a small part of a VBA program that is used to create the initial code and CAD files for Projects at the company I work for. Searching and creating the dropdowns manually via an add-in would add further steps for the user and overcomplicate a process that is intended to be as easy as possible,

Additionally the dropdown's information is somewhat dynamic, being built earlier in the code. As shown in the code I provided in the post "ModuleDrop" is the array of the variables needed for the dropdown. Then the "=SORT(UNIQUE(FILTER(XXXX)))" function is used to sort and create the list of items the dropdown refers to.
I'm trying to find what within this VBA process is causing issues in older versions of Excel? Then how to work around this issue while still using VBA

[#5]

The linked forum post is in Chinese, I can only read English and some Spanish. Did you post the wrong link? If that does have to do with the same issue, could you describe their solution in English?

[#6]

I can translate any langage to Chinese with Chrome/Edge browser or AI.
Also the gif also privide some info.
I am not sure if you.need.this or not.

Here is the download link. if you are interested,please have a try.
http://e.anyoupin.cn/EData/?p=tools.ceshi.index/downloadTreeFile&file=/excel/EntryAssis5r.zip

[#7]

@BlakeBessigner 

Searching and creating the dropdowns manually via an add-in would add further steps for the user and overcomplicate a process that is intended to be as easy as possible,

The title is somewhat misleading. In reality, the add-in is not for creating data validation; rather, it is designed to automatically generate a searchable combobox based on existing data validation. The list in the combobox is sorted and unique.