Pause and resume a macro

[pincivma]

Hi all
I have searched the internet on this problem but so far I have not found the answer that suits my needs. I want to create a macro with a message box that prompts the user to select a range on the worksheet to print after the user selects all of his printing options. Below is my unfinished code

Sub PrintsSelection()

Msg = "Do you want to print worksheet?"
Config = vbYesNo
Ans = MsgBox(Msg, Config)
If Ans = vbYes Then
MsgBox "Please select your printing options and the area to print" 'Here is where I get lost. I want the macro to pause so that the user can select a range
'and printing options. Once the user does that, I want the macro to resume and print the worksheet plus do other tasks.
If Ans = vbNo Then
Exit Sub
End If
End If
End Sub

 

[Trebor76]

Hi pincivma,

Try this:

Option Explicit
Sub PrintsSelection()

    Dim rngUserRange As Range
    
    Application.ScreenUpdating = False

    If MsgBox("Do you want to print worksheet?", vbQuestion + vbYesNo) = vbYes Then
        On Error Resume Next 'Account for <Cancel> button being clicked
        'Select the range to be printed (it will appear in the InputBox)
        Set rngUserRange = Application.InputBox(Prompt:="Please select the print range", Title:="Range Select", Type:=8)
        If Not rngUserRange Is Nothing Then
            rngUserRange.PrintOut
        End If
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[pincivma]

Hi Robert

Thanks for the code. I will give it a try and see how it goes. I will get back to you sometime today.

 

[pincivma]

Hi Again Robert

I tried out your macro and this is what I found out. I ran the Macro and the dialogue box opens to enter the print range. If I put my cursor in cell A1 and drag it down and to the right, the cells are not highlighting and I cannot move my cursor past what is displayed on the screen since the page does not scroll. It will select the range that you move your cursor across but it is not clear to me what cells I have selected and I could not necessarily select everything that I would want to print. That was my experience anyway. So is there a way to highlight the cell when I select them and also to move the cursor past what is displayed on the computer screen?? If so that would be awesome.

 

[mikerickson]

You can use an InputBox to select a range for printing

Sub Foo()
    Dim uiRange as Range
    ' stuff before

    On Error Resume Next
    Set uiRange = Application.InputBox("select a range", type:=8)
    On Error Goto 0
    If uiRange Is Nothing then Exit Sub: Rem cancel pressed

    uiRange.PrintOut
End Sub

 

[Fluff]

Simply remove this line

    Application.ScreenUpdating = False

 

[pincivma]

Hi Fluff
That was an easy fix. I did not think that Application.ScreenUpdating = True was causing my problem.

Hi mikerickson
I will also try your macro and see how it compares to Trebor76's macro.

Thank you all for your support. My mind is not as sharp as it used to be in my younger days.

 

[Fluff]

Glad we could help & thanks for the feedback