Workbook_open macro - weird behaviour( XL97VBA)

[davidtrickett]

I have a workbook with an open event macro which in a simple form goes like this:

Private Sub Workbook_Open()
ActiveCell.Offset(5, 0).Select
ActiveCell.Value = "anything"
End Sub

This of course works fine when the workbook is opened manually, but when it is opened by a macro in another workbook the cursor movement is ignored but it still cheerfully enters the value in the active cell!

I have tested this in several workbooks and for other event macros - e.g. Workbook_Activate - with the same result.

In a rather more complex macro it does everything it is supposed to do, but stubbornly refuses to move the cursor - ie in the above example
Activecell.Offset(5,0).Value="anything"
works.

Does anyone have any explanation for this weird behaviour, and better still any idea as to how I can persuade it to behave?

Thanks

[NateO]

Cells aren't active under this scenario, they're selected, but dormant. Persuasion:

Private Sub Workbook_Open()

Selection.Offset(5, 0).Select

ActiveCell.Value = "anything"

End Sub

This will move the entire selection in the event that multiple cells are selection. The following will limit the offset to a single cell selection:

Private Sub Workbook_Open()

Selection.Activate

ActiveCell.Offset(5, 0).Select

ActiveCell.Value = "anything"

End Sub

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-14 12:05 ]

[davidtrickett]

Many thanks for your help (again!), but I still can't persuade it to move the cursor - in both of your examples it resolutely sticks to A1! I am of course storing the macro in "This workbook" - I assume that this is the only place for it.

Any other ideas, or am I stuck with it?

Thanks

[Colo]

Hi, an easy way is...
Using "Auto_Open" on standerd module instead of "Private Sub Workbook_Open"

Here is a sample code.

'Standerd modele of Book1.xls

Sub Auto_Open()

  ActiveCell.Offset(5, 0).Select

  ActiveCell.Value = "anything"

End Sub



'Standerd modele of Book2.xls

Sub OpenAnotheBook()

  Dim strPath As String

  'Need change following path.

  strPath = "C:Documents and SettingssomewhereBook1.xls"

  Workbooks.Open(FileName:=strPath).RunAutoMacros Which:=xlAutoOpen

End Sub

[Vas]

I have the same problem, but with other operations. I can't open a workbook in the Workbook_Open event, when opening via VBA.

That sounded weird... OK, this is what I mean
1. I open a workbook via VBA code.
2. That workbook opens another Workbook in Workbook_Open.
3. It doesn't work.

What's more, it changes some of the links I have in the second workbook. I usually don't open workbooks via VBA anymore. I use Hyperlinks instead.

[Colo]

Hi Vas,
I guess, maybe you are Microsoft Excel 97 user.
In it, there are two methods you can use to run a macro automatically when you open a workbook.
But Excel 97 have a problem with above.
The case function procedure execute or workbook is opened via VBA, open event may not be performed.

This is a reason I recommended "Sub Auto_Open" to davidtrickett.

I've known about it on MS Knowledge Base Japan. Sorry I could not find English site.

[NateO]

Colo, don't mean to be argumentative, but I thought it was the other way, where auto macros, if launched via vba, won't fire unless you add a command. Nice add-in incidentally!

Dave, I am using 2000, where it works (I'll test in '97 tonight). You want to open the workbook in a normal module, if you use the workbook_open event, you must have it in the 'ThisWorkbook' module. The code I posted earlier worked for me, but let's define the range and see if that helps. In the file to be opened, in the 'ThisWorkbook' module:

Private Sub workbook_open()

Dim n As Range

Set n = Range(Selection.Address)

n.Offset(5).Value = "anything"

n.Offset(5).Select

End Sub

Make sure your events are enabled. In a normal module in any workbook, if the above fails, try:

sub st()

application.enableevents = true

end sub

If this does not work we can try an auto macro, like Colo mentioned. In a normal module in the target file place:

Private Sub auto_open()

Dim n As Range

Set n = Range(Selection.Address)

n.Offset(5).Value = "anything"

n.Offset(5).Select

End Sub

And in the file that you're opening the code with, do:

Workbooks.Open ("Whatever you have here")

ActiveWorkbook.RunAutoMacros xlAutoOpen

Hopefully one of these two methods gets you on track.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-15 19:18 ]

[Mark O'Brien]

On 2002-05-15 02:36, Colo wrote:
Hi, an easy way is...
Using "Auto_Open" on standerd module instead of "Private Sub Workbook_Open"

Here is a sample code.

'Standerd modele of Book1.xls

Sub Auto_Open()

  ActiveCell.Offset(5, 0).Select

  ActiveCell.Value = "anything"

End Sub



'Standerd modele of Book2.xls

Sub OpenAnotheBook()

  Dim strPath As String

  'Need change following path.

  strPath = "C:Documents and SettingssomewhereBook1.xls"

  Workbooks.Open(FileName:=strPath).RunAutoMacros Which:=xlAutoOpen

End Sub

I'm glad you suggested Auto_Open Colo. I asked if people used in versions of Excel 97 or above a few weeks ago. I could think of a few reasons I would use it, but was curious if anyway else still used this.

[davidtrickett]

NateO and all

Many thanks for your further help. I did of course know about the "Auto_open" option - I was just trying to be clever (and lazy - less code to write) by using the Workbook_open event - the situation is that I have a "Control" workbook which opens other workbooks selected by the user and runs their macros. My workround incidentally is to forget about automation and simply use the

Workbooks("any").Open
Application.Run('"any'!initialisemacro")

option. It just seems weird that if the workbook is opened by another macro everything - and some of my procedures are pretty complex - works except cursor movement (and as I have now found out thanks to Niklas opening another workbook). Another MS "undocumented feature"?