Workbook.activate acts dif when activewindow.caption changed

[jc]

Normally the active sheet in a workbook doesn't change when the workbook is
activated.

After setting the ActiveWindow.Caption to something new:

Workbook.activate changes the active sheet to Worksheets(1) no matter what
the previous active sheet was.

Can someone confirm this behavior?

Can someone explain this?

Can someone comeup with way to regain normal behavior?

 

[Joel]

The activesheet isn't changing on Excel 2003

 

[jc]

I am using EXCEL 2003

Did you set ActiveWindow.Caption to something different first?

 

[Joel]

I have a one instruction macro which is setting the caption properly. I
tried it with various different sheets being the active sheet with no
changes. Do you have any events macros in your workbook? Try the same thing
from a new workbook and see if the same thing happens.

 

[jc]

I open a new workbook with more than one sheet.
I acitvate a sheet other than sheet(1)
I enter the following code in the workbook code

Sub test()
Me.Activate
ActiveWindow.Caption = "help help"
Me.Activate
End Sub

upon execution:

The first Me.Activate does not change the active sheet

The second Me.Activate changes the active sheet to Sheet(1)

 

[Ken]

jc

I changed the active window caption using the immediate panel in Excel
2003. That does not seem to effect the the activewindow when activate
the workbook for me.

How are you changing the caption? How are you activating the
workbook? There could be something else going on if you are doing
that event code.

Ken

 

[JLGWhiz]

What does the sheet tab at the bottom read?

I open a new workbook with more than one sheet.
I acitvate a sheet other than sheet(1)
I enter the following code in the workbook code

Sub test()
Me.Activate
ActiveWindow.Caption = "help help"
Me.Activate
End Sub

upon execution:

The first Me.Activate does not change the active sheet

The second Me.Activate changes the active sheet to Sheet(1)

 

[Joel]

The problem isn't the activewindow.caption like you originally said. It is
the Me.Activate. The Me.Activate causes the change to the first worksheet.

 

[Ken]

jc

Me.activate when run through

Sub test()
Me.Activate
End Sub

which is in the workbook code, activates sheet(1). I would not have
expected that, but, it makes more sense than it being related to the
caption or the second me.activate. Try that and see if you get the
same results.

Ken

 

[jc]

Again this is the situation

I open a new workbook with more than one sheet.
I acitvate a sheet other than sheet(1)
I enter the following code in the workbook code

Sub test()
Me.Activate
ActiveWindow.Caption = "help help"
Me.Activate
End Sub

upon execution:

The first Me.Activate does not change the active sheet

The second Me.Activate changes the active sheet to Sheet(1)

=========================================

It is not part of an event and it is the me.activate that is changing the
active sheet to the sheet(1) which I believe is not normal behavior.

Can someone please replicate and confirm or deny.

 

[JLGWhiz]

I opened a workbook, put the code in ThisWorkbook to change the caption, Used
a different sub to activate the workbook and in three attemts it did not
change the caption in the title bar from the new title, however, it did in
all cases go back to sheet 1 as the active sheet when the workbook was
activated. Don't know why the OP is getting Worksheets(1) on the title bar
unless he has some odd settings in Excel.

 

[Ken]

jc

How do you know it is only the second me.activate that changes the
active sheet? It looks like the first one does it for me.

Try this

Sub test()
Me.Activate
MsgBox "what is the active sheet?"
ActiveWindow.Caption = "help xxxxx"
MsgBox "what is the active sheet now?"
Me.Activate
End Sub

Ken

 

[jc]

I know by watching it perform.

I think that you may be starting with a workbook where you already reset the
caption.

If you save the workbook as something and reopen it and activate a worksheet
other than Workshee(1) it should perform as I said.

I tried this and It resets to perform again as I initallly stated

 

[jc]

2 sheets:

"Sheet1" and "Sheet2" put Sheet1 on the left in the sheet1 position.

 

[jc]

The first Me.Acitvate doesn't change the active sheet
the second wone does.

Is this normal?

 

[jc]

I don't fully understand what you are saying

In general it appears to me that Setting the window caption

changes the behavior of Workbook.Acitivate in that it causes in all cases
the worksheets(1) to be active instead of the worksheet that was active
priort to running Workbook.Activate.

 

[Ken]

jc

This is very strange behavior. If the code below includes the
the .caption line, the active sheet changes; which seems odd; but,
even odder is that is continues to change if the .caption line is
commented out after running once. If the file is opened and run
without the .caption line, it never changes the active sheet. I tried
a couple other activewindow.things and they did not have the same
effect, nor did doing something else in between the two me.activates.

Something about the .caption line makes it lose track of me, but, a
rerunning of the code, with the .caption line commented out after
running with it once, returns the active sheet being the sheet1
immediately, even if you do a bunch of things to another sheet before
running it again.

Sub test()
Me.Activate
MsgBox ActiveSheet.Name
'ActiveWindow.Caption = "xxx"
'ActiveWindow.DisplayGridlines = True
'Cells(1, 1).Value = 5
MsgBox ActiveSheet.Name
Me.Activate
End Sub

 

[jc]

I think you are saying you confirm what I've been saying.

If so is there anything to do about it?

Do you work for Microsoft?

Is there a way to alert them to this apparent bug?

 

[JLGWhiz]

Most folks name a new workbook when they create it and then save it. Those
folks would never see the phenomenon that you are experiencing. I am even
having a hard time duplicating your finding. I have tried creating a new
workbook, changing the window caption and then using the me.activate command.
It does not change the caption. Even if it did, I would not get very
excited, I can always set the name right when I save it, because it will
automatically bring up the SaveAs dialog box and it can be corrected at that
time. I don't believe Microsoft would be too concerned about the anomaly.

 

[jc]

My main concern is to have someone duplicate what is happening to me so that
I know that I don't have a corrupted macro file. I'm creating lots of
simalar workbooks in a loop that I don't need to save but the user does see
while they are creating. I'm trying to get away without saving each file
(Save time and file space). When you say you're having trouble duplicating
my finding (that the caption doesn't change with me.activate), this is not
what I said I was noticing. I noticed that the activation of the workbook
will change the active sheet to sheets(1). If there is no other underlying
problem I can activate the sheet I need. I wonder though if there is and
underlying problem that will crop up.