PDA

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



Rob342
05-09-2010, 03:51 AM
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
05-09-2010, 05:13 AM
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
05-09-2010, 09:45 AM
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