I have written code that updates a list of programs. The intent is when a user opens the workbook, it will refresh the list of programs.
When I test the code it runs as intended. But when it runs as the workbook opens it does not execute one section of the code.
If I run PopulatePrograms manually after the workbook has opened, it runs fine. But when it is called upon open, it skips that one section of the code.
Please and Thank You
When I test the code it runs as intended. But when it runs as the workbook opens it does not execute one section of the code.
Code:
Excel 2003 on XP Pro
Named Range "Programs" =OFFSET(Profitability!$CA$4,0,0,COUNTA(Profitability!$CA:$CA)+1,1)
--------------------------------------
Private Sub Workbook_Open()
PopulatePrograms 'for use in program dropdown list
End Sub
------------------------------------
Sub PopulatePrograms()
'Clear the existing list
Sheet6.Columns("CA:CA").ClearContents
'get a unique filtered list [COLOR=Red]<=This the code that is not working at workbook open[/COLOR]
Sheet6.Range("D4:D65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("D4:D65536"), CopyToRange:=Range("CA4"), Unique:=True
'Sort list [COLOR=Red]<= my error occurs here because the range is null because of the above code not running[/COLOR]
Range("Programs").Sort Key1:=Range("CA4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
'Add list to combobox
Sheet1.cmbPrograms.AddItem "All"
For Each cll In Range("Programs")
Sheet1.cmbPrograms.AddItem cll.Value
Next
End Sub
Please and Thank You