code works normally but errors on workbook open

davper

New Member
Joined
Jun 30, 2010
Messages
11
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.



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
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
Have you checked your code for the advanced filter?

Specifically the range references, you seem to be using a worksheet reference for the range to filter but not for the criteria or the copy to destination.

Also have you considered not using a dynamic named range for 'Programs'?

You should be able to create the named range in the code with out all the OFFSET stuff.

One last thing - do you have any error handling set up?

I ask because I can't see anything that would cause the code to be 'skipped'.

It might actually be running but not doing what you expect and/or producing an error(s).
 

davper

New Member
Joined
Jun 30, 2010
Messages
11
Bingo. I was missing the worksheet reference in my filter as you mentioned.

I place my error handling after I finished my code so I can see all errors as I am developing.


Thanks for your help. It is always a simple error that causes me the most problems.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,451
Messages
5,837,345
Members
430,496
Latest member
Steph_88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top