vba Deselect Sheet

[Tornado1981]

Hi,

I have a workbook with x worksheets.

And a button (commandbutton1) in Sheet1 which opens this userform to choose between 2 sheets to preview in PDF.

Here’s my Preview button code.

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long[/FONT][/COLOR]

Private Sub CommandButton1_Click()
     Dim i As Integer
Dim CheckBoxName As String

For i = 1 To 2
CheckBoxName = "CheckBox"& i
If Me.Controls(CheckBoxName).Value = True Then
Sheets(Me.Controls(CheckBoxName).Caption).Select (False)
End If
Next i
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="D:\Preview.pdf"
Unload Me

Dim File As String
     File = "D:\Preview.pdf"
    ShellExecute 0, "Open", File, "", "", vbNormalNoFocus
End Sub

Now, the problem is that Sheet1 is always included in the preview file since it’s the sheet containing commandbutton1.


So is there a way to deselect sheet1 before exporting sheets 6 & 21 ??


I also tried another way but it always stuck at this line (Sheets(Array(SheetNames)).Select)

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long[/FONT][/COLOR]

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim CheckBoxName As String
    Dim SheetNames As String
    Dim PreviewSheets As Variant

    For i = 1 To 2
        CheckBoxName = "CheckBox"&  i
        If Me.Controls(CheckBoxName).Value = True Then
            SheetNames = SheetNames &Me.Controls(CheckBoxName).Caption & ","
        End If
    Next i    

    SheetNames = Mid(SheetNames, 1, Len(SheetNames) - 1)    
    Sheets(Array(SheetNames)).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="D:\Preview.pdf"  
    Unload Me

    Dim File As String

    File = "D:\Preview.pdf" 
    ShellExecute 0, "Open", File, "", "", vbNormalNoFocus
End Sub

Any help with any of these please ??

Thank u in advance.

 

[Jerry Sullivan]

Hi Tornado1981,

You can add a flag to help you Replace the current selection of sheet(s) when the first print sheet is selected then Add to the selected sheet(s) when subsequent sheets are selected.

Private Sub CommandButton1_Click()
 Dim bNoneSelected As Boolean
 Dim i As Integer
 Dim CheckBoxName As String
 Dim wksStart As Worksheet
 
 Const sFILENAME As String = "C:\Test\Preview.pdf"
 
 '--flag if at least one checked sheet selected
 bNoneSelected = True
 Set wksStart = ActiveSheet
 
 For i = 1 To 2
    CheckBoxName = "CheckBox" & i
    If Me.Controls(CheckBoxName).Value = True Then
      Sheets(Me.Controls(CheckBoxName).Caption).Select _
         Replace:=bNoneSelected
      bNoneSelected = False
    End If
 Next i
   
 If bNoneSelected Then
   MsgBox "No sheets to were selected to preview"
 Else
   Unload Me
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=sFILENAME
   
   ShellExecute 0, "Open", sFILENAME, "", "", vbNormalNoFocus
   '--select starting sheet; deselect other sheets
   wksStart.Select Replace:=True
 End If
End Sub

 

[Norie]

Instead of using ActiveSheet why not explicitly reference the sheets you want to print to PDF?

 

[Jerry Sullivan]

Instead of using ActiveSheet why not explicitly reference the sheets you want to print to PDF?

Hi Norie, Yes, that would work nicely.