Prev: How to use "notify" callback with winmm.dll
Next: how to use modules so that values calculated in one of them can be used by another?
From: Graham H on 17 Sep 2009 16:33
Many thanks for that Pascal and apologies for being so long in responding, I was actually
away. I apprecitae the time and effort you put into this and of course the successful
outcome!

Graham
papou wrote:
> Ok got it.
> Just pasted the whole code and tested, I have the same error.
> Once you have selected the whole cells in your sheet (Cells.Select), the
> Selection.Address returns "$1:$1048576"
> This is what causes the error.
> If your goal is actually to print all sheets of your workbook then I would
> recommend you define the print area for each sheet.
> Something like for instance:
> Dim ws As Worksheet
> For Each ws In ActiveWorkbook.Worksheets
> ws.PageSetup.PrintArea = ws.UsedRange.Address
> ws.PrintOut copies:=1
> Next ws
>
> HTH
> Cordially
> Pascal
>
>
>
> "papou" <cpapounospamthanx(a)lapostenospamthanks.net> a �crit dans le message
> de news: ONrQk72NKHA.3192(a)TK2MSFTNGP05.phx.gbl...
>> Graham
>> Your code (ActiveSheet.PageSetup.PrintArea = Selection.Address) works fine
>> for me in Excel 2007, so there should not be any need for modification at
>> this point.
>> Are there other sheets selected?
>> Are there any calls to word application? (in which case the expression
>> "Selection" could be misinterprated)
>> Try and check with :
>> MsgBox TypeName(Selection) = "Range"
>> If it returns True then it means the selection is correct.
>> One alternative could be to fully qualify objects, ie:
>> Dim Ws As Worksheet
>> Set Ws = ActiveWorkbook.Worksheets("Sheet1")
>> Ws.PageSetup.PrintArea = Selection.Address
>>
>> Another point in MHO is that you are using too many selections in your
>> code.
>> Selection of cells should not be necessary, and you should consider
>> avoiding it.
>>
>> HTH
>> Cordially
>> Pascal
>>
>> "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de
>> news: uFD4NHtNKHA.3192(a)TK2MSFTNGP05.phx.gbl...
>>> Hi Pascal,
>>> Thanks for reply. This occurs when the procedure with a de-bug
>>> window which says 'PrintArea' of object 'PageSetup' failed. As I said it
>>> works perfectly fine in Exccel 2002, I just wondered what modification
>>> was required for 2007.The code it is included in is
>>> ActiveWindow.SelectedSheets.PrintOut Copies:=1
>>> Cells.Select
>>> ActiveSheet.PageSetup.PrintArea = Selection.Address
>>> Range("A1").Select
>>> Sheets("Commands").Select
>>>
>>>
>>> Thanks for your comments.
>>>
>>> Graham
>>>
>>> papou wrote:
>>>> Hello Graham
>>>>
>>>> There's obviously some hiccup with Selection.Address in your code.
>>>> What is the error message?
>>>> Does your selection contain any object?
>>>> Try and get what Selection.Address actually returns, for instance:
>>>> Debug.Print Selection.address
>>>> or
>>>> Msgbox Selection.Address
>>>>
>>>> Cordially
>>>> Pascal
>>>>
>>>> "Graham H" <graham(a)haughs.orangehome.co.uk> a �crit dans le message de
>>>> news: %23Z8MP0pNKHA.3384(a)TK2MSFTNGP04.phx.gbl...
>>>>> Using a procedure which was originally created in Excel 2002 I am
>>>>> getting an error at the following line when running the same procedure
>>>>> on Excel 2007.
>>>>>
>>>>> ActiveSheet.PageSetup.PrintArea = Selection.Address
>>>>>
>>>>> I am at a loss as to why it comes out at this point and would value any
>>>>> guidance.
>>>>>
>>>>> Graham
>
>
First  |  Prev  | 
Pages: 1 2
Prev: How to use "notify" callback with winmm.dll
Next: how to use modules so that values calculated in one of them can be used by another?