How To Make Shape Disappear After Mouse Click, then reappear after switch sheet.

[Man_of_Sleep]

Hi to all Excel specialists and masters,

I have a vba code to convert the excel sheet (Sheet1) into pdf, then email the pdf.
I assigned that code to a rectangular shape, so user need to click the shape to execute the code.
The rectangular shape I've positioned in front of cells with information.
I want to make the shape disappear after user clicked it, then user can print the sheet without having the rectangular shape covered up the info.

But I also want to make the shape re-appear after user switching to the other sheet and come back the Sheet1.

Can anyone please guide me how to do that?
Sorry if my request is to much.

Thank you.

Below is my code.

Sub AttachActiveSheetPDF_01()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
  
  ' Define PDF filename
  Title = " Form for " & Range("A1").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"
 
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = "xxxxxxxxx" ' <-- Put email of the recipient here
    .CC = "lxxxxxxxxxxx" ' <-- Put email of 'copy to' recipient here
    .Body = "," & vbLf & vbLf _
          & "." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    Application.Visible = True
    .Send
  End With
 
  ' Quit Outlook if it was not already open
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[Georgiboy]

Maybe you could hide the button before the export and then unhide it after the export, you will not have to have any sheet level code then:

  ' Export activesheet as PDF
  With ActiveSheet
    .Shapes("Rectangle: Rounded Corners 1").Visible = False
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    .Shapes("Rectangle: Rounded Corners 1").Visible = True
  End With

 

[Man_of_Sleep]

Dear Georgiboy,

Thank you.
Your suggestion is also good.
User need to print from the pdf in order to get rid the rectangular shape.
So the flow:
sheet1(with shape) --> user convert to pdf and email --> open the pdf --> print the pdf (without shape)

Sorry,
if the user request to print from the excel, is it possible?
sheet1 (with shape) --> user click shape to run code (convert pdf and email) --> shape disappear --> user print Sheet1(without shape) --> move to another sheet --> shape in Sheet1 re-appear

Sheet1 is in protected mode, so user only can view and print.

Thank you again for the feedback.

 

[GTO]

Greetings,

Not well tested, but assuming no issues with the email part, how about making a temp copy of the sheet and deleting the shape there?

Sub AttachActiveSheetPDF_01()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
Dim WB As Workbook
Dim WS As Worksheet
Dim SHP As Shape
   
 
  ' Define PDF filename
  Title = " Form for " & Range("A1").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"
 
  ' Add a WB and copy the sheet to it.
  Set WB = Workbooks.Add(xlWBATWorksheet)
  Sheet1.Copy WB.Worksheets(1)
  'Set reference to the copied sheet...
  Set WS = WB.Worksheets(1)
  '...and delete shape(s) from it.
  For Each SHP In WS.Shapes
    SHP.Delete
  Next
 
  ' Then export our referenced sheet as PDF and finally...
  With WS
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  '...effectively kill the new WB by marking it saved and closing it.
  WB.Saved = True
  WB.Close False
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
  
    ' Prepare e-mail
    .Subject = Title
    .To = "xxxxxxxxx" ' <-- Put email of the recipient here
    .CC = "lxxxxxxxxxxx" ' <-- Put email of 'copy to' recipient here
    .Body = "," & vbLf & vbLf _
          & "." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
  
    ' Try to send
    'Application.Visible = True '<---I do not see where you had the Excel Application hidden?
    .Send
  End With
 
  ' Quit Outlook if it was not already open
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

Hope that helps,

Mark

 

[Georgiboy]

If you don't have any other hidden shapes on the sheet then you could use:

  ' Export activesheet as PDF
  With ActiveSheet
    .Shapes("Rectangle: Rounded Corners 1").Visible = False
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

Adn in the ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim shp As Shape
    
    For Each shp In Sh.Shapes
        shp.Visible = True
    Next
End Sub

 

[Man_of_Sleep]

Dear Georgiboy,
Dear GTO,

Thank you very much for your kind feedback and answer.
It's already solved my 3 days quest.
I am really admire with your knowledge.

Thank you.

 

[Artik]

I don't know, maybe I didn't understand the problem well. If all you care about is that a certain shape is not printed (whether it's a PDF or on paper), then all you need to do is set in the shape properties to not print. And then all the hide-and-seek fun is not needed.

Artik