how to set hidden sheet as object

[rehanemis]

Hello,

The code below works fine for me. Now I just want to get output in PDF of "PDFVersion" Sheet which is now "very hidden". So without activating or opening it I want to get PDF of the said sheet.

Note: I don't want user can see the said sheet or able to unhide it so I made it very hidden through vba ID window and password protected.

Any idea how to set hidden sheet without this code etc. "Worksheets("PDFVersion").Activate

Sub PDFActiveSheet()



Worksheets("PDFVersion").Activate
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"


'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")


'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile


'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
      Worksheets("InputSheet").Activate
End If


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

 

[mikerickson]

Try this

If myFile <> "False" Then
    [COLOR="#FF0000"]wsA.Visisble = xlSheetVisible[/COLOR]
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    [COLOR="#FF0000"]wsA.Visible = xlSheetVeryHidden[/COLOR]
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
    
      Worksheets("InputSheet").Activate
End If

 

[rehanemis]

Thanks but I would like to know that is there a way to get pdf output without un-hiding or activating the hidden sheet?

Try this

If myFile <> "False" Then
    [COLOR=#FF0000]wsA.Visisble = xlSheetVisible[/COLOR]
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    [COLOR=#FF0000]wsA.Visible = xlSheetVeryHidden[/COLOR]
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
    
      Worksheets("InputSheet").Activate
End If

 

[mikerickson]

This code does not activate the sheet. The sheet remains unseen by the user. And AFAIK, to print a sheet (even to PDF) the .Visible property has to be xlSheetVisible.

 

[rehanemis]

It is because someone can stop the macro and able to view the hidden sheet before it goes to veryhidden code.

 

[mikerickson]

If you have users who behave like that, in order to see confidential information, that is not a VBA problem, it is an HR problem. VBA has no solution for that problem.

 

[rehanemis]

Actually going to post this sheet on website so don't want any user to copy my sheet. Is there any way to encrypt it so no one can edit or copy the contents? is there any way?

 

[mikerickson]

Excel is not a secure platform.
If you want to keep something secret, do not post in on the web in an Excel spreadsheet.

I'm not sure what you mean by "user can't edit". Would posting a screen shot rather than worksheet work?