Worksheet.activate question

[Tim Coddington]

Stupid question time ...

How can I get the Worksheet_Activatecode (for sheet1) to activate from
Workbook_Open() (even if sheet1 is already active)?
I tried just sticking the Worksheet_Activate command in Workbook_Open(), but
that doesn't seem to be accessable from Workbook_Open().

 

[Bob Phillips]

Why not just activate the sheet from Workbook_Open?

Worksheets("Sheet1").Activate

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tim Coddington]

Because if the sheet is already active when the workbook opens,
Worksheet_Activate won't fire. Otherwise, Sheet(1).activate would be the
way to go.

 

[Tim Coddington]

Uh. I mean Worksheets("Sheet1").Activate would be the way to go.

 

[Bob Phillips]

Worksheets("Sheet2").Activate
Worksheets("Sheet1").Activate

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Another way:

In the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()
Call Sheet1.Worksheet_Activate
End Sub

in the Sheet1 module:
Option Explicit
Sub Worksheet_Activate()
MsgBox "hi from activate"
End Sub

Notice that I removed the word "Private" from the Sub line:
Private Sub Worksheet_Activate()

so the sub can be found.

 

[Tim Coddington]

I get a "Compiler Error:

Method or data member not found" error on that one (Call
Sheet1.Worksheet_Activate).

But makes me think. Would Worksheet_Activate for sheet1 be found in the VBA
object? I couldn't find it. Or perhaps in the VBA.IDE (none such exists)
object?

 

[Guest]

Try This

In module# insert
sub any_name()
Include all the statements in your Worksheet_Activate code
end sub

then call this procedure both when the workbook is opened and when
worksheet1 is activated.

I get a "Compiler Error:

Method or data member not found" error on that one (Call
Sheet1.Worksheet_Activate).

But makes me think. Would Worksheet_Activate for sheet1 be found in the VBA
object? I couldn't find it. Or perhaps in the VBA.IDE (none such exists)
object?

 

[Dave Peterson]

When you're in the VBE, select your project.
hit * (on the number keypad) to expand all the branches.

Under Microsoft Objects branch, you'll see the worksheet names.

sheet1(MySheetNameHere)
sheet2(MyotherSheet)
....

The names in parentheses are the worksheet names that you can see when you're in
excel.

The name before the worksheet name is called the CodeName.

The code I suggested uses that CodeName.

So find the name of your worksheet and use that sheet's codename in this line:

Call Sheet1.Worksheet_Activate

(Remember to remove that "Private" portion in that sub, too.)

Those were two ways I could get the same error as you--wrong codename and
failing to remove Private.