Problem with Workbook_open

[Guest]

I'm trying to have my workbooks open with a specfic font and size, however
when I open an existing workbook I get a runtime error '1004'. If I create a
new workbook from scratch I do not receive the error. Here is the code I'm
using:

Private Sub Workbook_Open()
Cells.Select
With Selection.Font
.Name = "Arial Narrow"
.Size = 11
End With
End Sub

Any Help you can give would be greatly appreciated.

Thanks!!

Vick

 

[Dave Peterson]

Could it be that the activesheet is protected?

Or maybe the activesheet is not a worksheet -- maybe it's a chartsheet?

If the worksheet is protected, you'll have to unprotect it.

But if it's not the correct sheet, you can tell excel which one to process:

Option Explicit
Private Sub Workbook_Open()
With Worksheets("Sheet1")
With .Cells.Font
.Name = "Arial Narrow"
.Size = 11
End With
End With
End Sub

 

[Guest]

I've tried using the code you suggested, but I received the same error. I
created a new sheet and it worked fine again. So I then changed the code to
look at sheet 2 and saved that excel file that worked with. Then when I
reopened it I received the same error. So I know the sheet is not protected
and it is not a chartsheet. Any other ideas?

Thanks

Vick

 

[Dave Peterson]

Which line caused the error?

(I'm still guessing that the worksheet is protected???)

 

[Guest]

This line caused the error: With Cells.Font

Once getting the error, I goto VB and run the code again and it works. It
just does not work when I open the file.

I know the worksheet is not protected, I can send you a SS if you want.

 

[vezerid]

Is there a chance that you first explicitly activating the worksheet
will work?

Worksheets("Sheet1").Activate

HTH
Kostis Vezerides

 

[Dave Peterson]

You lost a dot:

This line caused the error: With Cells.Font

Once getting the error, I goto VB and run the code again and it works. It
just does not work when I open the file.

I know the worksheet is not protected, I can send you a SS if you want.

 

[Dave Peterson]

You lost a dot:

With .Cells.Font

You may want to post the complete code you're using.

This line caused the error: With Cells.Font

Once getting the error, I goto VB and run the code again and it works. It
just does not work when I open the file.

I know the worksheet is not protected, I can send you a SS if you want.

 

[Guest]

Yes I know I removed the dot, when you leave the dot in absolutly nothing
happens.

 

[Dave Peterson]

You may want to post the complete code you're using.

Yes I know I removed the dot, when you leave the dot in absolutly nothing
happens.

 

[Guest]

Option Explicit
Private Sub Workbook_Open()
With Worksheets("Sheet1")
With Cells.Font
.Name = "Arial Narrow"
.Size = 11
End With
End With
End Sub

 

[Dave Peterson]

Add the dot back.

Do you have this code in the ThisWorkbook module?

How do you know it's not doing anything?

Did you type something in that sheet to see the fontname and size?

Option Explicit
Private Sub Workbook_Open()
With Worksheets("Sheet1")
With Cells.Font
.Name = "Arial Narrow"
.Size = 11
End With
End With
End Sub

 

[Guest]

I've added the dot back, and it does not do anything. How do I know it
doesn't do anything? First off my toolbar's font and size does not change
from the default. Second I can right click on a cell, goto format cells and
the font and size are still the defaults.

The code is in This Workbook on my Personal.xls.

 

[Dave Peterson]

It worked fine for my worksheet named Sheet1.

I've added the dot back, and it does not do anything. How do I know it
doesn't do anything? First off my toolbar's font and size does not change
from the default. Second I can right click on a cell, goto format cells and
the font and size are still the defaults.

The code is in This Workbook on my Personal.xls.