Message box pops up twice

H

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,595
Office Version
  1. 2021
Platform
  1. Windows
I have a users form that contains a print macro. Included in the macro is a message box that allow the user to input the no. of copies to print. However after selecting the number of copies to print and selecting the OK button, the message box pops up again


Please amend my macro to prevent this from popping up once printed unless the button on the user form is selected again



Code: 
 Private Sub OKButton_Click()
 Dim iNum As Variant
Application.ScreenUpdating = False
    If OptionBr1 Then
        Sheets(1).Select
       
         Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintGridlines = True
            .PrintArea = "Branch1TAX"
            
            .PrintTitleRows = "$16:$16"
            .PrintTitleColumns = "$B:$D"
            .LeftHeader = "&D&T"
            .CenterHeader = ""
            .Orientation = xlLandscape
            
           
        End With
          End If
        Application.PrintCommunication = True
        
iNum = InputBox(Prompt:="Please enter number of copies to print.", _
    Title:="Number of Copies", _
    Default:=1)
    
Select Case iNum
    Case 0, ""
        MsgBox ("Printing aborted.")
    Case Else
        ActiveWindow.SelectedSheets.PrintOut Copies:=iNum, Collate:=True
End Select
    If OptionBr2 Then
        Sheets(1).Select
       Range("F:T").EntireColumn.Hidden = True
         Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintGridlines = True
            .PrintArea = "Branch2Tax"
            
            .PrintTitleRows = "$16:$16"
            .PrintTitleColumns = "$B:$D"
            .LeftHeader = "&D&T"
            .CenterHeader = ""
            .Orientation = xlLandscape
            
           
        End With
          End If
        Application.PrintCommunication = True
      iNum = InputBox(Prompt:="Please enter number of copies to print.", _
    Title:="Number of Copies", _
    Default:=1)
    
Select Case iNum
    Case 0, ""
        MsgBox ("Printing aborted.")
    Case Else
        ActiveWindow.SelectedSheets.PrintOut Copies:=iNum, Collate:=True
          
         Range("F:T").EntireColumn.Hidden = False
                  
    Columns("E:CX").EntireColumn.AutoFit
   
    
          End Select
          
        End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
That's because you have duplicate code (in blue):

Code: 
 Private Sub OKButton_Click()
 Dim iNum As Variant
Application.ScreenUpdating = False
    If OptionBr1 Then
        Sheets(1).Select
       
         Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintGridlines = True
            .PrintArea = "Branch1TAX"
            
            .PrintTitleRows = "$16:$16"
            .PrintTitleColumns = "$B:$D"
            .LeftHeader = "&D&T"
            .CenterHeader = ""
            .Orientation = xlLandscape
            
           
        End With
          End If
        Application.PrintCommunication = True
        
[COLOR=#0000ff]iNum = InputBox(Prompt:="Please enter number of copies to print.", _[/COLOR]
[COLOR=#0000ff]    Title:="Number of Copies", _[/COLOR]
[COLOR=#0000ff]    Default:=1)[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff]Select Case iNum[/COLOR]
[COLOR=#0000ff]    Case 0, ""[/COLOR]
[COLOR=#0000ff]        MsgBox ("Printing aborted.")[/COLOR]
[COLOR=#0000ff]    Case Else[/COLOR]
[COLOR=#0000ff]        ActiveWindow.SelectedSheets.PrintOut Copies:=iNum, Collate:=True[/COLOR]
[COLOR=#0000ff]End Select[/COLOR]
    If OptionBr2 Then
        Sheets(1).Select
       Range("F:T").EntireColumn.Hidden = True
         Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintGridlines = True
            .PrintArea = "Branch2Tax"
            
            .PrintTitleRows = "$16:$16"
            .PrintTitleColumns = "$B:$D"
            .LeftHeader = "&D&T"
            .CenterHeader = ""
            .Orientation = xlLandscape
            
           
        End With
          End If
        Application.PrintCommunication = True
[COLOR=#0000ff]      iNum = InputBox(Prompt:="Please enter number of copies to print.", _[/COLOR]
[COLOR=#0000ff]    Title:="Number of Copies", _[/COLOR]
[COLOR=#0000ff]    Default:=1)[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff]Select Case iNum[/COLOR]
[COLOR=#0000ff]    Case 0, ""[/COLOR]
[COLOR=#0000ff]        MsgBox ("Printing aborted.")[/COLOR]
[COLOR=#0000ff]    Case Else[/COLOR]
[COLOR=#0000ff]        ActiveWindow.SelectedSheets.PrintOut Copies:=iNum, Collate:=True[/COLOR]
          
         Range("F:T").EntireColumn.Hidden = False
                  
    Columns("E:CX").EntireColumn.AutoFit
   
    
[COLOR=#0000ff]          End Select[/COLOR]
          
        End Sub
 
Upvote 0
You have the input box twice in your code with out anything to stop the second input box from being run.

Code: 
Private Sub OKButton_Click()
 Dim iNum As Variant
Application.ScreenUpdating = False
    If OptionBr1 Then
        Sheets(1).Select
       
         Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintGridlines = True
            .PrintArea = "Branch1TAX"
            
            .PrintTitleRows = "$16:$16"
            .PrintTitleColumns = "$B:$D"
            .LeftHeader = "&D&T"
            .CenterHeader = ""
            .Orientation = xlLandscape
            
           
        End With
          End If
        Application.PrintCommunication = True
        
iNum = [COLOR=#FF0000]InputBox(Prompt:="Please enter number of copies to print.", _
    Title:="Number of Copies", _
    Default:=1)[/COLOR]
    
Select Case iNum
    Case 0, ""
        MsgBox ("Printing aborted.")
    Case Else
        ActiveWindow.SelectedSheets.PrintOut Copies:=iNum, Collate:=True
End Select
    If OptionBr2 Then
        Sheets(1).Select
       Range("F:T").EntireColumn.Hidden = True
         Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintGridlines = True
            .PrintArea = "Branch2Tax"
            
            .PrintTitleRows = "$16:$16"
            .PrintTitleColumns = "$B:$D"
            .LeftHeader = "&D&T"
            .CenterHeader = ""
            .Orientation = xlLandscape
            
           
        End With
          End If
        Application.PrintCommunication = True
      iNum[COLOR=#FF0000] = InputBox(Prompt:="Please enter number of copies to print.", _[/COLOR]
    Title:="Number of Copies", _
    Default:=1)
    
Select Case iNum
    Case 0, ""
        MsgBox ("Printing aborted.")
    Case Else
        ActiveWindow.SelectedSheets.PrintOut Copies:=iNum, Collate:=True
          
         Range("F:T").EntireColumn.Hidden = False
                  
    Columns("E:CX").EntireColumn.AutoFit
   
    
          End Select
          
        End Sub
 
Upvote 0
Thanks for letting me know where the problem was
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Question Question
Print size change using VBA.
Replies
4
Views
330
imback2nite
imback2nite
willow1985
  • Solved
PrintArea = "TableName" but visible cells only
Replies
2
Views
586
willow1985
willow1985
J
  • Question Question
Excel VBA error 400 while link to another sheet
Replies
3
Views
604
rollis13
rollis13
JohnZ1156
  • Solved
VBA Macro to print table rows from a start date to an end date.
Replies
4
Views
318
Joe4
Joe4
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
100
brentcole
B

Forum statistics

Threads
1,223,959
Messages
6,175,647
Members
452,663
Latest member
MEMEH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top