Message box pops up twice

H

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,589
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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

willow1985
  • Solved
PrintArea = "TableName" but visible cells only
Replies
2
Views
582
willow1985
willow1985
J
  • Question Question
Excel VBA error 400 while link to another sheet
Replies
3
Views
602
rollis13
rollis13
JohnZ1156
  • Solved
VBA Macro to print table rows from a start date to an end date.
Replies
4
Views
306
Joe4
Joe4
H
  • Question Question
Macro to hide cols C:E is not hiddden
Replies
4
Views
417
mohadin
M
H
  • Solved
Print Preview Visible Columns
Replies
4
Views
516
howard
H

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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