Auto_Open macro, Run-time error '91'

Question

Hi, I record a macro as Auto_Open to set sheet as right-to-left and select specific cells as print area etc., but when I open Excel I got Run-time error '91':

"object variable or with block variable not set"

Here is the code:

---

Sub Auto_Open()

'

' Auto_Open Macro

'

' Keyboard Shortcut: Ctrl+m

'

ActiveSheet.DisplayRightToLeft = True

Range("E1:U6").Select

ActiveSheet.PageSetup.PrintArea = "$E$1:$U$6"

Application.PrintCommunication = False

With ActiveSheet.PageSetup

.PrintTitleRows = ""

.PrintTitleColumns = ""

End With

Application.PrintCommunication = True

ActiveSheet.PageSetup.PrintArea = "$E$1:$U$6"

Application.PrintCommunication = False

With ActiveSheet.PageSetup

.LeftHeader = ""

.CenterHeader = ""

.RightHeader = ""

.LeftFooter = ""

.CenterFooter = ""

.RightFooter = ""

.LeftMargin = Application.InchesToPoints(0.7)

.RightMargin = Application.InchesToPoints(0.7)

.TopMargin = Application.InchesToPoints(0.75)

.BottomMargin = Application.InchesToPoints(0.75)

.HeaderMargin = Application.InchesToPoints(0.3)

.FooterMargin = Application.InchesToPoints(0.3)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.PrintQuality = 600

.CenterHorizontally = False

.CenterVertically = False

.Orientation = xlPortrait

.Draft = False

.PaperSize = xlPaperA4

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = 100

.PrintErrors = xlPrintErrorsDisplayed

.OddAndEvenPagesHeaderFooter = False

.DifferentFirstPageHeaderFooter = False

.ScaleWithDocHeaderFooter = True

.AlignMarginsHeaderFooter = True

.EvenPage.LeftHeader.Text = ""

.EvenPage.CenterHeader.Text = ""

.EvenPage.RightHeader.Text = ""

.EvenPage.LeftFooter.Text = ""

.EvenPage.CenterFooter.Text = ""

.EvenPage.RightFooter.Text = ""

.FirstPage.LeftHeader.Text = ""

.FirstPage.CenterHeader.Text = ""

.FirstPage.RightHeader.Text = ""

.FirstPage.LeftFooter.Text = ""

.FirstPage.CenterFooter.Text = ""

.FirstPage.RightFooter.Text = ""

End With

Application.PrintCommunication = True

Application.PrintCommunication = False

With ActiveSheet.PageSetup

.PrintTitleRows = ""

.PrintTitleColumns = ""

End With

Application.PrintCommunication = True

ActiveSheet.PageSetup.PrintArea = "$E$1:$U$6"

Application.PrintCommunication = False

With ActiveSheet.PageSetup

.LeftHeader = ""

.CenterHeader = ""

.RightHeader = ""

.LeftFooter = ""

.CenterFooter = ""

.RightFooter = ""

.LeftMargin = Application.InchesToPoints(0.7)

.RightMargin = Application.InchesToPoints(0.7)

.TopMargin = Application.InchesToPoints(0.75)

.BottomMargin = Application.InchesToPoints(0.75)

.HeaderMargin = Application.InchesToPoints(0.3)

.FooterMargin = Application.InchesToPoints(0.3)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.PrintQuality = 600

.CenterHorizontally = False

.CenterVertically = False

.Orientation = xlPortrait

.Draft = False

.PaperSize = xlPaperA4

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = False

.FitToPagesWide = 1

.FitToPagesTall = 0

.PrintErrors = xlPrintErrorsDisplayed

.OddAndEvenPagesHeaderFooter = False

.DifferentFirstPageHeaderFooter = False

.ScaleWithDocHeaderFooter = True

.AlignMarginsHeaderFooter = True

.EvenPage.LeftHeader.Text = ""

.EvenPage.CenterHeader.Text = ""

.EvenPage.RightHeader.Text = ""

.EvenPage.LeftFooter.Text = ""

.EvenPage.CenterFooter.Text = ""

.EvenPage.RightFooter.Text = ""

.FirstPage.LeftHeader.Text = ""

.FirstPage.CenterHeader.Text = ""

.FirstPage.RightHeader.Text = ""

.FirstPage.LeftFooter.Text = ""

.FirstPage.CenterFooter.Text = ""

.FirstPage.RightFooter.Text = ""

End With

Application.PrintCommunication = True

End Sub

---

Also, I used this code as Wrokbook Open event, but it doesn't run correctly, actually it dosen't set sheet as right-to left !! It just select the cells!! Any help would be appreciated.

Kind Regards

Answer 1

Hi Mahab,

I tried these codes as Module and assigned it a shortcut, this time it worked. 

Unfortunately this module doesn't work in Excel 2003. I get following error at "Application.PrintCommunication = False" line:

Run-time error '438':

Object doesn't support this property or method

What's wrong?

 The Application.PrintCommunication property was introduced as part of VBA7 with the launch of Excel 2010. Consequently,  the instruction will fail with Excel 2003 and VBA6. In this connection, see:

http://msdn.microsoft.com/en-us/library/office/ff835544(v=office.15).aspx

To avoid the error and enable your code to operate independently of the version, use conditional compilation and replace the problematic line with:

    #If VBA7 Then

         Application.PrintCommunication = False

    #End If

===

Regards,

Norman

Answer 2

Hi Bernie

Thanks again.

No, the Workbook is not structure protected.

It does not worked even as Workbook_Open event. I don't know why, because in Open even, we have a workbook.

I tried these codes as Module and assigned it a shortcut, this time it worked.

Unfortunately this module doesn't work in Excel 2003. I get following error at "Application.PrintCommunication = False" line:

Run-time error '438':

Object doesn't support this property or method

What's wrong?

Kind Regards

Answer 3

That line works fine for me, as long as there is a workbook - maybe try

ActiveWorkbook.Worksheets(1).DisplayRightToLeft = True

Is your workbook structure protected?

Answer 4

Dear Bernie,

Thanks for your reply.

I did as you said, but I got another error. ( Method Range of Object _Global failed)

I think it doesn't work, because when excel run Auto_Open, it has not opened any workbook yet.

Anyway, I used this module as Workbook Open event; I did not get error, all part of codes run except following line:

"ThisWorkbook.Worksheets(1).DisplayRightToLeft = True"

It doesn't set the sheet as Right-to-Left!!

Any help would be appreciated.

Kind Regards

Answer 5

Instead of 

ActiveSheet

try using the sheet index:

ThisWorkbook.Worksheets(1)

or the sheet name

ThisWorkbook.Worksheets("SheetName")

or the sheet code name

Sheet1

of the specific sheet you want to change.  Replace Activesheet wherever it appears