Make text box invisible when print to pdf

[dpaton05]

I have a textbox that has the text "Please type notes here" and a button that prints to pdf. If no notes are written in the textbox, a blank text box with the text "Please type notes here" will still appear when I print to pdf. How do I make the text box invisible if it contains the text "Please type notes here"?

This is my attempt

Sub NoText()
    With ThisWorkbook.Worksheets("NPSS_quote_sheet").Shapes("TestBox1")
        If .Value = "Please type notes here" Then
            .Visible = "false"
        End If
    End With
End Sub

 

[Michael M]

See post #8

https://www.mrexcel.com/forum/excel...isible-when-printing.html?highlight=invisible

 

[dpaton05]

Thanks for reminding me about that answer Michael. In the same module as cmdSend, I enter this procedure

Sub NoText(Cancel As Boolean)
If TextBox1 = "Please type notes here" Then
    TextBox1.Visible = False
End Sub

...and I try and call it from cmdSend and I get the error of argument not optional.

Here is cmdSend, I have also underlined where it is called from.

Sub cmdSend()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim desWS As Worksheet, srcWS As Worksheet
        Set srcWS = ThisWorkbook.Sheets("NPSS_quote_sheet")
        Set desWS = ThisWorkbook.Sheets("Costing_tool")
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, x As Long, header As Range
        lastRow1 = srcWS.Range("B" & srcWS.Rows.Count).End(xlUp).Row
        lastRow2 = desWS.Range("A:A").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        
    With srcWS.Range("A:A,B:B,H:H")
        If lastRow2 < 5 Then
            lastRow2 = 5
            For i = 1 To .Areas.Count
                x = .Areas(i).Column
                Set header = desWS.Rows(4).Find(.Areas(i).Cells(10), LookIn:=xlValues, lookat:=xlWhole)
                If Not header Is Nothing Then
                    srcWS.Range(srcWS.Cells(11, x), srcWS.Cells(lastRow1, x)).Copy
                    desWS.Cells(lastRow2, header.Column).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                End If
            Next i
            With desWS
                If .Range("A" & .Rows.Count).End(xlUp).Row > 5 Then
                    desWS.ListObjects.Item("tblCosting").ListRows.Add
                    .ListObjects.Item("tblCosting").DataBodyRange.Columns(1).NumberFormat = "dd/mm/yyyy"
                End If
                .Range("D" & lastRow2 & ":D" & .Range("A:A").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row) = srcWS.Range("G7")
                .Range("F" & lastRow2 & ":F" & .Range("A:A").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row) = srcWS.Range("B7")
                .Range("G" & lastRow2 & ":G" & .Range("A:A").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row) = srcWS.Range("B6")
            End With
        Else
            lastRow2 = desWS.Range("A:A").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            desWS.ListObjects.Item("tblCosting").ListRows.Add
            For i = 1 To .Areas.Count
                x = .Areas(i).Column
                Set header = desWS.Rows(4).Find(.Areas(i).Cells(10), LookIn:=xlValues, lookat:=xlWhole)
                If Not header Is Nothing Then
                    srcWS.Range(srcWS.Cells(11, x), srcWS.Cells(lastRow1, x)).Copy
                    desWS.Cells(lastRow2 + 1, header.Column).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                End If
            Next i
            With desWS
                .Range("D" & lastRow2 + 1 & ":D" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("G7")
                .Range("F" & lastRow2 + 1 & ":F" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("B7")
                .Range("G" & lastRow2 + 1 & ":G" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("B6")
            End With
        End If
    End With
    desWS.ListObjects("tblCosting").Sort.SortFields.Clear
    desWS.ListObjects("tblCosting").Sort.SortFields. _
        Add Key:=desWS.Cells(, 1), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With desWS.ListObjects("tblCosting").Sort
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
[U]    Call NoText[/U]
    Call AddName
    
    With Application
        .CutCopyMode = False
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    Dim oLst As ListObject
        Dim lr As Long, rng As Range
        lr = desWS.Cells(Rows.Count, "A").End(xlUp).Row
        For i = lr To 4 Step -1
            Set rng = desWS.Cells(i, 1)
            If WorksheetFunction.CountBlank(rng) = 1 Then
                desWS.Rows(i).Delete
            End If
        Next i
End Sub

 

[Kenneth Hobson]

You did not send the input value of True or False for Cancel.

 

[Michael M]

Did you note the name of the sub in Post # 8 of the thread I reminded you of ??
It is specifically a before print code !!

 

[dpaton05]

The underlined text is what is highlighted when I get the error.

 

[Kenneth Hobson]

See #4 .

NoText True
'or
NoText False

It makes no difference since you did not use Cancel in the routine.

 

[dpaton05]

I put this code in the thisworkbook sheet in the vbe and it still didn't make the text box invisible when it was printed.

Sub Workbook_BeforePrint(Cancel As Boolean)
If TextBox1 = "Please type notes here" Then
    TextBox1.visible=false
End Sub

 

[dpaton05]

You did not send the input value of True or False for Cancel.

What do you mean Kenneth?

 

[Kenneth Hobson]

ThisWorkbook is the workbook object, not a sheet object. Why add a Cancel input when not used.

Private is usually used for routines in Sheet and ThisWorkbook object, If you did not say Private then your sub is considered Public. Public in a sheet or worksheet object is not that useful. If you are using it as a Public sub, put it in a Module. You could remove the input too. Or, simply directly use the one line as Micahel M used. Or, put his routine in ThisWorkbook object and not call or use your routine.

If TextBox1 is activex control on codename Sheet1 object then in ThisWorkbook:

Sub Workbook_BeforePrint(Cancel As Boolean)
    If Sheet1.TextBox1 = "Please type notes here" Then _
        Sheet1.TextBox1.Visible = False
End Sub

When you say print to pdf, I am not sure what that means. If you mean using the SaveAs a pdf file, then the routine above works or do it like this:

Sheet1.TextBox1 = "Please type notes here" Then _
        Sheet1.TextBox1.Visible = False
'export to pdf code here
Sheet1.TextBox1.Visible = True