Having problems with Private Sub Workbook_open() and Private Sub Workbook_activate()

[still learning]

I can’t seem to get this to work.
When I open the workbook, I want to open sheet1. I have 7 sheets and I don’t always go back to sheet 1 before I save and close
When I open the work book, it goes to sheet1 but the sub “movebox” doesn’t run
If I open another sheet in the workbook, and go back to sheet1, it runs
I tried to change “Open” and “Activate”
Here is what I’m trying to use:
This is in the “Thisworkbook”

Private Sub Workbook_open()
Sheets("sheet1").Select
End Sub

This is Sheet1

Private Sub Worksheet_activate()
movebox
opening
End Sub

mike

 

[rabsofty]

Private Sub Workbook_open()
Sheets("sheet1").Activate
End Sub

This is Sheet1
VBA Code:
Private Sub Worksheet_activate()
movebox
opening
End Sub

 

[still learning]

Hi rabsofty
I copied and pasted the two macros where you said, and I still can't get the macro "movebox" to run on opening sheet i. it works if i open a different sheet after
sheet1 and go back to sheet1.
I used F8 on the sub and it works.
I made a macro using the recorder and put it ahead of "movebox" and took out the other two ("). and still it doesn't run on opening. I made a macro box on a different workbook and put this in it

Sub test()
Workbooks.Open Filename:="C:\excel\test.xlsm"
Sheets("sheet1").Select
End Sub

It opens sheet in the the proper workbook. but macro1 doesn't
It does work if I go from sheet1 to a different sheet and come back


mike

 

[MARK858]

Don't use .Select use .Activate in the Workbook.Open as per post 2

 

[still learning]

Hi MARH858
I'm messing something.
...open() does have Activate
if you meant in my 2nd post, I changed it and still got the same results.

mike

 

[MARK858]

Try
In ThisWorkbook

Private Sub Workbook_open()
Sheets("sheet1").Activate
End Sub

In your Worksheet module

Private Sub Worksheet_activate()
 Application.Run "movebox"
 Application.Run "opening"
End Sub

where movebox and opening are in a regular module

 

[still learning]

Tried it and same results.
first. yes both subs are in a regular module.
I copied and pasted your codes in where you said to try.
when that didn't didn't work.
I made a simple code and took out the other two

Sub Macro1()
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveCell.FormulaR1C1 = "mike"
End Sub

it didn't run...untill i left sheet 1 and came back to it (in the same workbook)
I also save all this and closed out excel and reloaded it.

mike

 

[MARK858]

I'm running it with a msgbox in the sub in the regular module and with a different sheet to sheet1 selected when I save and close the workbook.
It is producing the msgbox fine in those circumstances when opening the workbook.
It isn't producing the msgbox if Sheet1 is the selected sheet when the workbook is closed when opening the workbook

Is the above what is happening for you?

 

[still learning]

YES
if I save and close the workbook when i'm in a different sheet it works
if i'm in sheet1 and i save and close , it doesn't.
if i go from sheet1 to sheet2 and come back, it works.

mike

 

[MARK858]

The easiest way around it is to test for the activesheet in the workbook_open i.e.

Private Sub Workbook_open()
    If ActiveSheet.Name <> "Sheet1" Then
        Sheets("sheet1").Activate
    Else
        Application.Run "movebox"
        Application.Run "opening"
    End If
End Sub