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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,764
Office Version
365
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,567
Messages
5,512,096
Members
408,880
Latest member
FrankHH

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top