Solved: Print, Save As, Exit Sheet & close Current workbook

[Rob342]

Hi
I have a main workbook with 4 buttons on it 3 of which clear data from sheet 1.
The 4th button prints & saves sheet 1 to another location, which is working ok
All the sheets are protected and the vba coding is password protected.
1st problem
When the print & save button is pressed, i would like the main workbook to close, after it has printed & saved sheet 1.
2nd problem
When you look at the copy sheet the buttons are still active and you can see the vba code when right clicking on the tab.
Password for vba & sheet = password
How can i protect the copy sheet so nobody can do anthing to it ?

I have added a test version if it helps ?

Rob342

[GTO]

Greetings Rob,

Nothing wrong with setting a reference to the ActiveWorkbook after creating it through .Copy; I usually Set a reference earlier by adding a workbook - so here is just one way:

Rather than trying to delete a bunch of code that resides under the copied sheet, just move the effective code to a Standard Module. That way, the code never goes with the sheet.

In the Worksheet Module for Calc Sheet:

Option Explicit
    
Private Sub CommandButton1_Click()
    Call Module1.CommandButton1_Click
End Sub

ADD a Standard Module, named Module1.

In the Standard Module:

Option Explicit
    
Sub CommandButton1_Click()
Dim _
wksCopy         As Worksheet, _
wkscmdBttn      As OLEObject, _
wbNew           As Workbook, _
wksNew          As Worksheet, _
strFullName     As String
    
Const PWD As String = "password"
    
    '// Copy the worksheet to past the last sheet and set a reference to the copy.      //
    With ThisWorkbook
        .Worksheets("Calc Sheet").Copy After:=.Worksheets(.Worksheets.Count)
        Set wksCopy = .Worksheets(.Worksheets.Count)
    End With
    
    With wksCopy
        '// Change path to suit...                                                      //
        strFullName = ThisWorkbook.Path & "\Calc Sheet." & .Range("J5").Value & ".xls"
        
        '// Unprotect the new copy, kill any commandbuttons and re-protect.             //
        .Unprotect Password:=PWD
        For Each wkscmdBttn In .OLEObjects
            If TypeName(wkscmdBttn.Object) = "CommandButton" Then
                wkscmdBttn.Delete
            End If
        Next
        .Protect Password:=PWD
        
        '// Set a reference to a new, one-sheet wb; move the copied sheet to it, kill   //
        '// the blank sheet.                                                            //
        Set wbNew = Workbooks.Add(xlWBATWorksheet)
        .Move After:=wbNew.Worksheets(1)
        Application.DisplayAlerts = False
        wbNew.Worksheets(1).Delete
        Application.DisplayAlerts = True
    End With
    
    '// Set a reference to the copied sheet in the new wb, rename the sheet (to rid the //
    '// " (2)" ), and save the new wb.                                                  //
    With wbNew
        Set wksNew = .Worksheets(1)
        wksNew.Name = "Calc Sheet"
        MsgBox "Print here?"
        'wksNew.PrintOut
        .SaveAs Filename:=strFullName
    End With
    
    ThisWorkbook.Close False
End Sub

Hope that helps,

Mark

[Rob342]

Hi GTO

The code you supplied works a treat, you can still see the vba calling Module 1 on the copy but nothing else.
Any user going into vba now cannot do anthing with it anyway, so i not bothered about them seeing this bit.

Once again thanks for your time its much appeciated. "A great forum"
Regards
Rob