Qualifying Object Reference Help

[YellowLabPro]

I am working on concepts/lessons here. I want to qualify my code to reference a specific worksheet.
This code is erroring: Worksheets(1)


Option Explicit
Sub Lesson_Sum()
'Pg 16, 3/11/07
Workbooks("VBA Code Lesson_Examples Dummy.xls").Worksheets (1)
Dim Sum As Long
Sum = 1 + 1
MsgBox "The Answer is" & Chr(10) & Chr(34) & Sum & Chr(34)
End Sub

[mdmackillop]

The Workbooks line is not "doing" anything. I've added cell reference as your Sum need no references.
Your options here, depending upon the uses required are
Option Explicit

Sub Lesson_Sum1()
'Pg 16, 3/11/07
Dim Sum As Long
With Workbooks("VBA Code Lesson_Examples Dummy.xls").Worksheets(1)
Sum = .Range("A1") + .Range("A2")
MsgBox "The Answer is" & Chr(10) & Chr(34) & Sum & Chr(34)
End With
End Sub

Sub Lesson_Sum2()
'Pg 16, 3/11/07
Dim ws As Worksheet
Dim Sum As Long
Set ws = Workbooks("VBA Code Lesson_Examples Dummy.xls").Worksheets(1)
With ws
Sum = .Range("A1") + .Range("A2")
MsgBox "The Answer is" & Chr(10) & Chr(34) & Sum & Chr(34)
End With
End Sub

Sub Lesson_Sum3()
'Pg 16, 3/11/07
Dim WB As Workbook
Dim ws As Worksheet
Dim Sum As Long

Set WB = Workbooks("VBA Code Lesson_Examples Dummy.xls")
Set ws = WB.Worksheets(1)

With ws
Sum = .Range("A1") + .Range("A2")
End With

MsgBox "The Answer is" & Chr(10) & Chr(34) & Sum & Chr(34)
End Sub

[YellowLabPro]

Malcolm,
You use "With" to do the work here, which is a little different that what is shown in my book. The book does not use "With", it just uses a statement of: Workbooks("VBA Code Lesson_Examples Dummy.xls").Worksheets (1). How is this different, if this is the correct question?

In your second example you rid the "With" for the Worksheet and Dim it... How does this work? I have not seen where a Worksheet is dimensioned. That is very cool nevertheless.

[mdmackillop]

If you don't use With, you need to repeat the sheet name as in
Sum = Workbooks("VBA Code Lesson_Examples Dummy.xls").Worksheets(1).Range("A1") + Workbooks("VBA Code Lesson_Examples Dummy.xls").Worksheets(1).Range("A2")

This is a bit clumsy

Using Variables allows you to use meaningful/abbreviated names in your With statements e.g.
With wsSource
.Cells(1,1).Copy wsTarget.Cells(5,5)
End With

[YellowLabPro]

Ok. I will use this as a reference in a little bit. I am working on a dumbed down version still.

Here is where I am. In the three following procedures, the first two work, they are taken from the book. The third procedure is my attempt at qualifying the worksheet range.
I am getting an error to the effect: Object does not support this property or method.
I realize the difference between an active worksheet but why does this not work?

Sub Gridlines_False()
'Pg 43 3/11/07
ActiveWindow.DisplayGridlines = False
End Sub


Sub Gridlines_Toggle()
'Pg 44 3/11/07
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
End Sub


Sub Gridlines_Toggle2()
Workbooks("VBA Code Lesson_Examples Dummy.xls").Worksheets(1).DisplayGridlines = False
'Not Worksheets(1).DisplayGridlines
End Sub

[mdmackillop]

From the help file

DisplayGridlines Property

See AlsoApplies ToExample (http://www.vbaexpress.com/forum/#example)Specifics
True if gridlines are displayed. Read/write Boolean.
Remarks

This property applies only to worksheets and macro sheets.
This property affects only displayed gridlines. Use the PrintGridlines (http://www.vbaexpress.com/forum/xlproPrintGridlines1.htm) property to control the printing of gridlines.
Example

This example toggles the display of gridlines in the active window in Book1.xls.
Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate
ActiveWindow.DisplayGridlines = Not(ActiveWindow.DisplayGridlines

Basically, Display Gridlines is a Window property not a Worksheet property and Intellisense will not show this as an option when you compose your code.
Press F1 with the cursor in a property to see the Help relating to that item. "Applies to" shows the objects it can be used with.

[YellowLabPro]

Ah Haa,
Much learned from your reply post, thank you.

Mentioning Intellisense:
Sheet1. will bring up Intellisense
Sheet(1). will not bring up Inteillisense
Am I doing something incorrect?

[mdmackillop]

Sheet1. will bring up Intellisense
Sheet(1). will not bring up Inteillisense
Am I doing something incorrect?

Doesn't work for me either! Maybe just have to use Sheet1 to make use of it then edit to Sheets(1) once the line is complete.