Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - simple but doesn't work.
Hi,
I'm a very occasional (and not very good) VBA user in a company where nobody else seems to. I can usually muddle my way through using google and a big stack of books, but have come unstuck on something which really should be very simple. My intention was to produce some code which cycles through all worksheets in the active workbook (they're all iterations of the same thing) and gets it all formatted for printing. I've come up with the following code: Sub printy_thingy() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select ws.Activate ws.PageSetup.PrintArea = "$A$1:$T$141" With ws.PageSetup .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 3 End With ws.ResetAllPageBreaks Set ws.HPageBreaks(1).Location = Range("A65") Set ActiveSheet.HPageBreaks(2).Location = Range("A114") Next ws End Sub Unfortunately, the lines: Set ws.HPageBreaks(1).Location = Range("A65") Set ActiveSheet.HPageBreaks(2).Location = Range("A114") ....just don't work. I get error messages when I try to execute them (Excel 2002). Anybody know why this is happening and how to fix it? Cheers in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - simple but doesn't work.
For someone who is just muddling you are doing very well... Set only gets
used when you are trying to initialize an object (as opposed to a variable). So that being said the 2 lines of code in question should not have set at the beginning. I did not try your code but I think that should work. -- HTH... Jim Thomlinson "mr tom" wrote: Hi, I'm a very occasional (and not very good) VBA user in a company where nobody else seems to. I can usually muddle my way through using google and a big stack of books, but have come unstuck on something which really should be very simple. My intention was to produce some code which cycles through all worksheets in the active workbook (they're all iterations of the same thing) and gets it all formatted for printing. I've come up with the following code: Sub printy_thingy() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select ws.Activate ws.PageSetup.PrintArea = "$A$1:$T$141" With ws.PageSetup .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 3 End With ws.ResetAllPageBreaks Set ws.HPageBreaks(1).Location = Range("A65") Set ActiveSheet.HPageBreaks(2).Location = Range("A114") Next ws End Sub Unfortunately, the lines: Set ws.HPageBreaks(1).Location = Range("A65") Set ActiveSheet.HPageBreaks(2).Location = Range("A114") ...just don't work. I get error messages when I try to execute them (Excel 2002). Anybody know why this is happening and how to fix it? Cheers in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - simple but doesn't work.
dear mr. thomlinson (i've used your code in the past) and mr cone, is there any resource that explains the difference between an object and a variable, as well as difference between properties and methods in a quick and concise way. i know some of the bugs i run into deal with these differences. thanks, and have a great weekend! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - simple but doesn't work.
I'll try some quick definitions for you.
An OBJECT is a tangible thing, a building block or component of Excel. Such as Workbook, Worksheet, Range and many more. In the visual basic editor, type Object Model in the help box, you will find a listing of the Excel objects (and others). A PROPERTY is a characteristic of an object. A METHOD is an action that can be taken. You could consider a kitchen Stove as an object (one of the components of a kitchen). It would have properties such as color, burners, drawers. A method might be the On method for a burner... Stove.Burner.On = True It can get a little confusing when you consider that the stove burner is an object in its own right. (that is the burner property returns an object) A VARIABLE (according to John Walkenbach) "is simply a named storage location in your computers memory" (not much help?)... You could consider a variable as a custom name that you use to refer to something. I strongly suggest you buy a reference book. I like John Walkenbach's Power Programming books. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "mwam423" wrote in message dear mr. thomlinson (i've used your code in the past) and mr cone, is there any resource that explains the difference between an object and a variable, as well as difference between properties and methods in a quick and concise way. i know some of the bugs i run into deal with these differences. thanks, and have a great weekend! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - simple but doesn't work.
hi jim, appreciate your taking the time to reply. i've got walkenbach's "excel VBA programming", will have to take another look at those sections, thanks again! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - simple but doesn't work.
Contrary to what the help file says, my memory says that the "location" property reads but doesn't write. What seems to work best for me is... Rows(65).PageBreak = xlPageBreakManual -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message Hi, I'm a very occasional (and not very good) VBA user in a company where nobody else seems to. I can usually muddle my way through using google and a big stack of books, but have come unstuck on something which really should be very simple. My intention was to produce some code which cycles through all worksheets in the active workbook (they're all iterations of the same thing) and gets it all formatted for printing. I've come up with the following code: Sub printy_thingy() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Select ws.Activate ws.PageSetup.PrintArea = "$A$1:$T$141" With ws.PageSetup .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 3 End With ws.ResetAllPageBreaks Set ws.HPageBreaks(1).Location = Range("A65") Set ActiveSheet.HPageBreaks(2).Location = Range("A114") Next ws End Sub Unfortunately, the lines: Set ws.HPageBreaks(1).Location = Range("A65") Set ActiveSheet.HPageBreaks(2).Location = Range("A114") ....just don't work. I get error messages when I try to execute them (Excel 2002). Anybody know why this is happening and how to fix it? Cheers in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
So simple, but I can't get it to work | Excel Discussion (Misc queries) | |||
simple deletion won't work for me | Excel Discussion (Misc queries) | |||
Simple but I can't get it to work? PLEASE help!! | Excel Discussion (Misc queries) | |||
Its so simple! Why won't it work! | Excel Programming | |||
Why does this simple thing not work? | Excel Programming |