Macro to dynamically add drop downs failing only on certain computers

[EricMacWright]

I built a workbook which is intended to be a GUI (of sorts) for another program. In order to give the user the ability to add/remove/update inputs/outputs to the program I've made the spreadsheet configurable. By which I mean, on a given worksheet they're allowed to adjust the order/colors/available options for inputs/output, as well as the name of sheets that the inputs/outputs show up on, etc. The code that I wrote is working as expected on 99% of the computers that I've tried it on, but for one office (located in France) it's not working. I've tried computers in US regional settings, French regional settings, with and without admin rights, in English and French language settings, and I can't get it to fail on my machines or any of our test machines in those settings. The only thing that seems to not be working is adding drop down lists to the input sheets. The macro adds a sheet called ValuesTab, on which it adds all the specified values, creates a named list, and then assigns it to the range where the user is supposed to see the drop down. The macro (on French machines) adds the ValuesTab, adds the values I'm expecting to see, and is breaking somewhere after that. Here is the segment of the code that is failing, any help would be greatly appreciated...I've searched for a while and can't seem to locate the issue:

If UCase(sValueType) = "STRING" Then                bAddValues = True
                On Error GoTo 0
                
                sValues = objSheet.Cells(iCurRow, iValueColumn).Value
                sValArray = Split(sValues, ",")
                For i = 0 To UBound(sValArray)
                    objValuesTab.Cells(i + 1, iValuesTabColumn).Value = sValArray(i)
                Next
                'We've added all of the values to the column,
                'Now we'll set up these values as a drop down list
                objBook.Names.Add Name:=sTitleNoSpace, RefersToR1C1:= _
                                            "=ValuesTab!R1C" + CStr(iValuesTabColumn) + ":R" + CStr(i) + "C" + CStr(iValuesTabColumn)
                objBook.Names(sTitleNoSpace).Comment = ""
                iValuesTabColumn = iValuesTabColumn + 1
            End If
            Application.DisplayAlerts = False
            On Error Resume Next
            If objBook.Worksheets(sTab) Is Nothing Then
                Set objInputSheet = objBook.Sheets.Add(After:=objBook.Sheets("Generate Workbook"))
                objInputSheet.Name = sTab
                
                Set VBProj = objBook.VBProject
                Application.VBE.MainWindow.Visible = False
                Set VBComp = VBProj.VBComponents(objInputSheet.CodeName)
                Set CodeMod = VBComp.CodeModule
                
                With CodeMod
                    LineNum = .CreateEventProc("Change", "Worksheet")
                    LineNum = LineNum + 1
                    .InsertLines LineNum, "   Call PsychCalcs(Target)"
                End With
            End If
            objInputSheet.Cells(4, iColOutput).Value = sTitle
            objInputSheet.Cells(4, iColOutput).Font.Bold = True
            
            'adjust the column width
            objInputSheet.Columns(iColOutput).ColumnWidth = Len(sTitle) + 5
            'change the color to match the Inputs List sheet
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = dColor
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
                         
            'add the path into the spreadsheet
            objInputSheet.Cells(1, iColOutput).Value = sPath
            objInputSheet.Cells(2, iColOutput).Value = sConversion
            objInputSheet.Cells(3, iColOutput).Value = sValueType
            
            'Assign the drop down
            If bAddValues = True Then
                objInputSheet.Range(Cells(5, iColOutput), Cells(1048576, iColOutput)).Select
                objInputSheet.Range(Cells(5, iColOutput), Cells(1048576, iColOutput)).Activate
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                                    xlBetween, Formula1:="=" & sTitleNoSpace
                    .IgnoreBlank = True
                    .inCellDropDown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
            End If
            iColOutput = iColOutput + 1
        End If
        iCurRow = iCurRow + 1
    Loop

 

[John_w]

Welcome to MrExcel forums.

I've scanned through your code and saw the following:

1. You have On Error Resume Next, but not a following On Error GoTo 0, therefore the former could be hiding an error in the code. Put OERN and OEG0 around the minimum number of lines to trap an expected error.

2. You're using the number 1048576, whereas it would be better to use Rows.Count, in case the worksheets are the old .xls sheets (65536 rows).