Code Issues: Print Code is to fast doesnt allow report to update

[JaredMcCullough]

I have the following code that populates a data validation in cell E12 of a worksheet and hides Active X Textboxes based upon a cell output:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim Rng As Range


If Target.Address(0, 0) = "E12" Then

With Sheets("Variance Database")
        Set Rng = .Range(.Range("A8"), .Range("A" & Rows.count).End(xlUp))
        Rng.Name = "NamedRng"
    End With


    
With Target.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=NamedRng"
    
End With


End If

If Target.Address(0, 0) = "E12" Then
    ' Call your macro that makes objects appear here
    If Range("O12").Value = "Emergency" Then
    TextBox21.Visible = True
    TextBox22.Visible = True
    TextBox23.Visible = True
    TextBox24.Visible = True
    TextBox25.Visible = True
    TextBox26.Visible = True
    CheckBox21.Visible = True
    CheckBox22.Visible = True
    CheckBox23.Visible = True
    CheckBox24.Visible = True
    CheckBox25.Visible = True
    CheckBox26.Visible = True
    CheckBox27.Visible = True
    CheckBox28.Visible = True
    TextBox26.Value = "*Print off variance and complete Field Form portion at jobsite along with contract company" & vbNewLine & "*Ensure lead Contract Representative signs below to verify that all contract employees have received the appropriate training" & vbNewLine & "*Receive all approvals prior to commishioning work. Verbal approvals are acceptable as long as live signatures are obtained within 24 hours of completion of the job." & vbNewLine & "*Send live hard copies back to the site Process Safety Engineer for recordkeeping. Ensure electronic approvals are completed to close out the pending variance in the database."
    Else
    TextBox21.Visible = False
    TextBox22.Visible = False
    TextBox23.Visible = False
    TextBox24.Visible = False
    TextBox25.Visible = False
    TextBox26.Visible = False
    CheckBox21.Visible = False
    CheckBox22.Visible = False
    CheckBox23.Visible = False
    CheckBox24.Visible = False
    CheckBox25.Visible = False
    CheckBox26.Visible = False
    CheckBox27.Visible = False
    CheckBox28.Visible = False
    End If
    
End If
End Sub

I then have the following code that provides a conditional Print of all open items in the database:

Sub HardCopy()
    Application.EnableEvents = False
    Dim TrackingNo  As Variant, _
        ValList     As Range, _
        ReportForm  As Worksheet
    
    Set ReportForm = Sheets("psm-07-03a")
    Set ValList = Sheets("variance database").Range("NamedRng")
    
    For Each TrackingNo In ValList
        If UCase(TrackingNo.Offset(0, 22).Value) = "PENDING" Then
            ReportForm.Range("E12").Value2 = TrackingNo
            ReportForm.PrintOut
        End If
    Next TrackingNo
    
 Application.EnableEvents = True

End Sub

My problem is the Print code is to fast and is not allowing the Textboxes to become visible and/or hide appropriately. (My suspicion is because this is you are disabling events running the case then enabling)

Any help would be appreciated.

Jared

 

[JaredMcCullough]

Bump for help.

 

[JaredMcCullough]

Bump for help