VBA msgBox does not appear when ran

hajiali

Active Member
Joined
Sep 8, 2018
Messages
381
Office Version
  1. 2016
Platform
  1. Windows
Hello excel experts need help with the below code.
There is way more to the beginning of the code let me know if it is required to help. but for some reason the "Ans = MsgBox("DID YOUR BID PRINT?", vbYesNo)" never appears when the code is ran not sure what I'm doing wrong
any help is greatly appreciated.

Thanks

VBA Code:
  Next
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
    Ans = MsgBox("DID YOUR BID PRINT?", vbYesNo)
    If Ans = vbNo Then
    Exit Sub
    Else
    End If
End With
    Sheets("Form").Visible = xlVeryHidden
    Sheets("Bid").Range("A3:A503,U3:U503,S1").ClearContents
    Sheets("Bid").Select
    ActiveSheet.Protect Password:="password", AllowFiltering:=True
ActiveWorkbook.Save
MsgBox "YOUR BID HAS BEEN SUBMITTED AND PRINTED. PLEASE SIGN PRINTED COPY AND DROP IN THE BID BOX", vbExclamation
Unload Me
Sheets("Bid").Range("S1").Select
End Sub
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
Did the worksheet get printed?
Anyway I'd suggest to show the whole code, I am interested in understanding which event it's linked to.

Bye
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
381
Office Version
  1. 2016
Platform
  1. Windows
it does print and it skips over the Msgbox and proceeds with the rest of the code after End with somehow it never appears

VBA Code:
Private Sub CommandButton1_Click()
Dim Ans As Integer
Dim EmpID$, dDate$, Pin$, Rg As Range, I&, x&
EmpID = TextBox1.Value
Pin = TextBox3.Value
If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Then
   MsgBox "PLEASE FILL IN ALL FIELDS TO PRINT AND SUBMIT BID ... ", vbExclamation: Exit Sub
End If

If Not IsNumeric(EmpID) Then MsgBox "INVALID EMPLOYEE NUMBER ENTER NUMBER WITHOUT THE E", vbExclamation: Exit Sub

With Sheets("BID RESULTS")
   For I = 1 To 2
      dDate = IIf(I = 1, TextBox1.Value, TextBox2.Value)
      If Len(dDate) > 0 Then
         Set Rg = .Columns(1).Find(EmpID, LookAt:=xlWhole)
         If Rg Is Nothing Then
            MsgBox "EMPLOYEE NUMBER DOES NOT EXIST PLEASE TRY AGAIN", vbExclamation:
            TextBox1.Value = ""
            TextBox1.SetFocus
            Exit Sub
         Else
         Set Rg = .Columns(1).Find(EmpID, LookAt:=xlWhole)
    Set Rg = .Cells(Rg.Row, "KW")
If Pin <> Rg Then
        MsgBox "INCORRECT PIN PLEASE TRY AGAIN OR RESET PIN", vbExclamation
        TextBox3.Value = ""
        TextBox3.SetFocus
        Exit Sub
        Else
            Set Rg = IIf(I = 1, ThisWorkbook.Sheets("Bid Data").Cells(2, "E").Resize(1500), ThisWorkbook.Sheets("BID DATA").Range("F" & ThisWorkbook.Sheets("BID DATA").Range("F" & Rows.Count).End(xlUp).Row + 1).Resize(, 1500))
            If InStr(dDate, ".") Then
               For x = 0 To UBound(Split(dDate, "."))
               Next
               For x = 0 To UBound(Split(dDate, "."))
                  Rg.Find("", After:=Rg.Cells(Rg.Count)) = Split(dDate, ".")(x)
               Next
            Else
               Rg.Find("", After:=Rg.Cells(Rg.Count)) = dDate
            End If
         End If
         End If
    ActiveSheet.Unprotect Password:="BNA262"
      Set Rg = IIf(I = 1, ThisWorkbook.Sheets("BID").Cells(1, "S").Resize(1500), ThisWorkbook.Sheets("BID").Cells(3, "U").Resize(1500))
            If InStr(dDate, ".") Then
               For x = 0 To UBound(Split(dDate, "."))
               Next
               For x = 0 To UBound(Split(dDate, "."))
                  Rg.Find("", After:=Rg.Cells(Rg.Count)) = Split(dDate, ".")(x)
               Next
            Else
               Rg.Find("", After:=Rg.Cells(Rg.Count)) = dDate
            End If
            End If
    Sheets("Form").Visible = True
    Sheets("FORM").Select
   Next
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
    Ans = MsgBox("DID YOUR BID PRINT?", vbYesNo)
    If Ans = vbNo Then
    Exit Sub
    Else
    End If
End With
    Sheets("Form").Visible = xlVeryHidden
    Sheets("Bid").Range("A3:A503,U3:U503,S1").ClearContents
    Sheets("Bid").Select
    ActiveSheet.Protect Password:="Password", AllowFiltering:=True
ActiveWorkbook.Save
MsgBox "YOUR BID HAS BEEN SUBMITTED AND PRINTED. PLEASE SIGN PRINTED COPY AND DROP IN THE BID BOX", vbExclamation
Unload Me
Sheets("Bid").Range("S1").Select
End Sub
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
381
Office Version
  1. 2016
Platform
  1. Windows
Never mind I was modify this code which is similar to another code i have and was running the other macro
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,135
Glad to know you fixed it by yourself, it was going to be (for me) a little bit complex without a sample worksheet
Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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
Top