Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to Select & Print Selection (XP)

    Can someone help me write a macro that would:

    1. Do a GoTo so I can select the area of my worksheet to print
    2. Then print that selection

    In QuattroPro (which I am migrating from), I just enter the area in the print area section of the print menu. Excel doesn't seem to have this alternative.

    I'm a real newbie at actually writing macros (I have recorded a few that do work).

    Thank you!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Select & Print Selection (XP)

    Does this do what you want:

    <code>
    Public Sub SelectAndPrint()
    Dim oRng As Range
    Set oRng = Application.InputBox(Prompt:="Select range to Print", Type:=8)
    oRng.PrintOut
    End Sub
    </code>
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Select & Print Selection (XP)

    That works great! Thank you so much.

    Now I'm curious: What is the "Type:=8" mean? I assume it means Type is assigned 8 but what's 8? Are there other choices?

    Appreciate the extremely fast reply.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to Select & Print Selection (XP)

    Instead of macro, if you are using goto to goto a location, you could chose "selection" in the "Print what" part of the print dialog.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to Select & Print Selection (XP)

    From the help on Application.InputBox method

    <pre>Type Optional Variant. Specifies the return data type. If this argument is omitted,
    the dialog box returns text. Can be one or a sum of the following values.

    Value Meaning
    0 A formula
    1 A number
    2 Text (a string)
    4 A logical value (True or False)
    8 A cell reference, as a Range object
    16 An error value, such as #N/A
    64 An array of values

    You can use the sum of the allowable values for Type. For example, for an input box that
    can accept both text and numbers, set Type to 1 + 2.</pre>


    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Select & Print Selection (XP)

    From the Excel Help file:

    <hr>Type Optional Variant. Specifies the return data type. If this argument is omitted, the dialog box returns text. Can be one or a sum of the following values.

    Value Meaning
    0 A formula
    1 A number
    2 Text (a string)
    4 A logical value (True or False)
    8 A cell reference, as a Range object
    16 An error value, such as #N/A
    64 An array of values
    <hr>
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Select & Print Selection (XP)

    Steve, I had been doing that but it seemed to involve more steps than what I am used in QuattroPro. Legare's macro fills the bill perfectly. Thanks!

    Steve and Legare, thanks for the information. Someday I'll have to sit down and actually learn some basic VBA. I appreciate being able to pick your brains until I do this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •