Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Make text box invisible when print to pdf
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Make text box invisible when print to pdf

    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
    Code:
    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,804
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Make text box invisible when print to pdf

    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text box invisible when print to pdf

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

    Code:
    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.

    Code:
    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
        
        Call NoText
        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

  4. #4
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text box invisible when print to pdf

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

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,804
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Make text box invisible when print to pdf

    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 !!
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text box invisible when print to pdf

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

  7. #7
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text box invisible when print to pdf

    See #4 .

    Code:
    NoText True
    'or
    NoText False
    It makes no difference since you did not use Cancel in the routine.

  8. #8
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text box invisible when print to pdf

    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.

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

  9. #9
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text box invisible when print to pdf

    Quote Originally Posted by Kenneth Hobson View Post
    You did not send the input value of True or False for Cancel.
    What do you mean Kenneth?

  10. #10
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text box invisible when print to pdf

    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:
    Code:
    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:
    Code:
    Sheet1.TextBox1 = "Please type notes here" Then _
            Sheet1.TextBox1.Visible = False
    'export to pdf code here
    Sheet1.TextBox1.Visible = True
    Last edited by Kenneth Hobson; Sep 5th, 2019 at 09:42 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •