Excel 2007 VBA error - Excel Help Forum

		Excel Help Forum > Usenet Groups > Excel 2007 Help
Excel 2007 VBA error

Notices

Hi All,
The big green guy here, just wanted to let you all know that we now have the reputation system in place, the reputation system allows you to give a visible pat on the back to those that have helped you or whose information has been helpful.

You will see an icon in every post (Light Blue scales), click this to give the reputation point to whom you think deserves it, a green square appears for a reputation with more and more adding over time.Pretty soon you will see a string of green squares appear in the top right of your posts as your reputation grows and your forum presence!

Good Luck!
The Hulkster!

Thread Tools

Search this Thread

Display Modes

07-07-2008, 12:22 PM

carmennorman carmennorman is offline

Registered User

Join Date: 07 Jul 2008

Location: Cambridge, UK

Posts: 4

Excel 2007 VBA error

Hello

I hope someone can help me. I have a very simple piece of code, that works perfectly in Excel 2003, but my users have now upgraded to 2007 and the code doesn't work. I keep getting an error: Object variable not set (Error 91). But as far as I can see I have declared everything.

The code is:

Code:

Dim strWkNo As Integer
Dim strColNum As Integer

    strWkNo = InputBox("Which week number do you want to update to?", "Update Chart")
    
    strColNum = strWkNo + 1

    ActiveChart.SeriesCollection(1).XValues = "='Yields - Foam'!R3C2:R3C" & strColNum & ""
    ActiveChart.SeriesCollection(1).Values = "='Yields - Foam'!R5C2:R5C" & strColNum & ""
    ActiveChart.SeriesCollection(2).XValues = "='Yields - Foam'!R3C2:R3C" & strColNum & ""
    ActiveChart.SeriesCollection(2).Values = "='Yields - Foam'!R16C2:R16C" & strColNum & ""

The error message appears the first time I am using the variable for the activechart. Very confused

, like I said it works in 2003. What do I need to do to get it working in 2007? Please help

07-07-2008, 12:29 PM

Andy Pope Andy Pope is offline

Forum Moderator

Join Date: 10 May 2004

Location: Essex, UK

Posts: 2,110

Andy Pope will become famous soon enough

The code fails because there is current no active chart.

you can check before executing code

Code:

    If ActiveChart Is Nothing Then
        MsgBox "No chart Active", vbExclamation
    Else
        ActiveChart.SeriesCollection(1).XValues = "='Yields - Foam'!R3C2:R3C" & strColNum & ""
        ActiveChart.SeriesCollection(1).Values = "='Yields - Foam'!R5C2:R5C" & strColNum & ""
        ActiveChart.SeriesCollection(2).XValues = "='Yields - Foam'!R3C2:R3C" & strColNum & ""
        ActiveChart.SeriesCollection(2).Values = "='Yields - Foam'!R16C2:R16C" & strColNum & ""
    End If

Do you have any other code that may cause the activechart to lose focus before reaching the code you posted?

__________________
Cheers
Andy

07-07-2008, 12:37 PM

carmennorman carmennorman is offline

Registered User

Join Date: 07 Jul 2008

Location: Cambridge, UK

Posts: 4

Active chart

Thanks Andy

The user is prompted to select the chart, because the sheet has loads of charts they need to select the chart that needs updating before they run the macro. I already have an error in place...

The full example of the code is:

Code:

Dim strWkNo As Integer
Dim strColNo As Integer

    strWkNo = InputBox("Which week number do you want to update to?", "Update Chart")
    strColNo = strWkNo + 1
    
    On Error GoTo ErrMsg
    
    ActiveChart.SeriesCollection(1).XValues = "=Forming!R31C2:R31C" & strColNo & ""
    ActiveChart.SeriesCollection(1).Values = "=Forming!R78C2:R78C" & strColNo & ""
    ActiveChart.SeriesCollection(2).XValues = "=Forming!R31C2:R31C" & strColNo & ""
    ActiveChart.SeriesCollection(2).Values = "=Forming!R77C2:R77C" & strColNo & ""
    
    Exit Sub
    
ErrMsg:
    MsgBox "You have not selected a chart - select the PC Forming Chart"

07-07-2008, 12:44 PM

Andy Pope Andy Pope is offline

Forum Moderator

Join Date: 10 May 2004

Location: Essex, UK

Posts: 2,110

So how are they starting the macro?

Your example, for me, displays a error message as expected.

Can you post example workbook where the untrapped error is raised?

__________________
Cheers
Andy

07-07-2008, 12:49 PM

Simon Lloyd Simon Lloyd is offline

Administrator

Join Date: 02 Mar 2004

Location: Cheshire, UK

Posts: 4,073

Simon Lloyd will become famous soon enough

Moved to correct forum

__________________
Regards,
Simon

Please read this before cross posting!
Please always give feedback...good or bad!

Please take the time to read the Forum Rules before posting!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode