Can seem to get Excel to go to open to a specific worksheet.

[ijourneaux]

I am having trouble getting Excel to open to a specific worksheet.

I am using

Sub Workbook_Open()
ActiveWorkbook.Worksheets("ControlLoops").Activate
MsgBox ("Test")
End Sub

This was insufficient as Excel ends up on a different sheet (possibly the last work sheet I created) that wasn't the worksheet I save the workbook with.

When I added,

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox ("Test2")
End Sub

It seems that I end up on the correct worksheet. Removing the MsgBox inside _SheetActivate causes the wrong worksheet to be opened.

Ian

[Richard Buttrey]

I suspect you have created a module level Workbook_Open procedure.

You should be using the Workboook level

Private Sub Workbook_Open()

procedure.

[ijourneaux]

Hmm. I only have one workbook_open () subroutine defined in the ThisWorkbook.

[Richard Buttrey]

Hmm. I only have one workbook_open () subroutine defined in the ThisWorkbook.

Can I suggest you upload the workbook so that we can see the behaviour in context.

[Norie]

Does this work?

Please Login or Register  to view this content.

[ijourneaux]

I spent most of yesterday Trying to track down what was causing the strange behavior but not luck.

Darn. The size is too large. I am also using a specialized control so I am not sure if you could run it if I could upload it.

I did a search for activate in my code and can't seem to find anything that would make it go to the worksheet in question and override my direct call to activate the worksheet I want.

[Richard Buttrey]

I spent most of yesterday Trying to track down what was causing the strange behavior but not luck.

Darn. The size is too large. I am also using a specialized control so I am not sure if you could run it if I could upload it.

Delete most of the stuff, in fact since the problem is associated with the Open event delete the lot, an empty workbook (with sheets obviously) will do just as well.

[ijourneaux]

I deleted the component and the worksheet now opens on the correct sheet. I thought at first that it was the component that was causing the issue but no such luck. THe problem seems to be more random than that.

Let me run somemore test to come upw ith a version that is uploadable and demonstrates the problem.

[ijourneaux]

I deleted the component and the worksheet now opens on the correct sheet. I thought at first that it was the component that was causing the issue but no such luck. THe problem seems to be more random than that.

Let me run somemore test to come upw ith a version that is uploadable and demonstrates the problem.

[Norie]

Did you try the code I posted?

[ijourneaux]

I consider myself to be pretty proficient with Excel but this one is really odd.

Whether the worksheet I want is opened or not when I open the workbook is kind of random. When I read Norie's comment it was working as I wanted it to. I made a sime change
from

Please Login or Register  to view this content.

To Norie's recommendation

Please Login or Register  to view this content.

what worked (abit briefly) no longer works.

Alittle more testing incdicates that the msgbox must be there for the Workbook to open the correct worksheet. If it is not there, the worksheet alwasys opens the wrong worksheet (always the same wrong worksheet).

[Norie]

Is there any other code in the workbook?

Also, what is this 'component' you mention?

Is it an add-in? A third-party control? Something else?

PS Can you add code tags when posting code?

[ijourneaux]

There is quite a bit of code but I have search though the code to wits end to see if I could find anything that would unintentionally active the wrong window.

In one of my worksheets, the last one I added, I have a control installed that display data from a process historian (AspenTech IP.21). This is also the worksheet that is incorrectly activated when I open up the workbook.

[Norie]

Is AspenTech IP.21 an add-in?

[ijourneaux]

It is an OCX control that I am using to display data from the process historian.

[Norie]

So there's no add-ins?

[ijourneaux]

It seems that one of the controls I am using is causing Excel to change to the wrong worksheet. Is it possible to determine which event are getting fired when and excel workbook is loaded? That way I might be able to track down that is cuaseing the active worksheet to change. I ahve search through all of my code using the name of the worksheet and I don't have any calls to activate that spreadsheet.
Ian