Save As Copy with Password Protection 1

[shelby55]

Hello

I am using Excel 2003.

Thanks to Skip I've been able to use VBA to save a copy of an Excel Workbook with specific worksheets hidden and disconnect of the data source.

The new workbook has some VBA code related to the Admits worksheet which is a chart. How do I add code to the original so that the VBA code will be hidden? Thanks.

 

[PHV]

Password protect the VBAProject of the original workbook ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi PHV

No, on the copy. The current code is:

Option Explicit
Sub copyWorkbook()
Dim ws As Worksheet
Dim NewWB As Workbook
Dim qt As QueryTable

'this keeps the copy from being displayed
    Application.ScreenUpdating = False
    Sheets(Array("WSA", "WSB", "WSC", "WSD", "WSE", "WSF", "WSG","WSH")).Copy
    Set NewWB = ActiveWorkbook
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    With NewWB.Sheets("WSA")
'unlock ALL cells
       With .Cells
          .Locked = False
          .FormulaHidden = False
       End With
'lock these cells
       With .Range("F4:M40")
           .Locked = True
           .FormulaHidden = False
       End With
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  End With
  With NewWB.Sheets("WSE")
    For Each qt In .QueryTables
      qt.Delete
    Next
  NewWB.Sheets("WSE").Visible = xlSheetVeryHidden
  End With
     
  NewWB.Sheets("WSC").Visible = xlSheetVeryHidden
  NewWB.Sheets("WSG").Visible = xlSheetVeryHidden
  NewWB.Sheets("WSH").Visible = xlSheetVeryHidden
  
  
  NewWB.UpdateLinks = xlUpdateLinksNever
    
    NewWB.SaveCopyAs Filename:="E:\MyDocuments\MyData_" & Format(Now(), "YYYYMMDD") & ".xls"
    ActiveWindow.Close
    Application.ScreenUpdating = True
End Sub

Within each graph (now currently only one, WSF) there is code for the formatting of images and that is what I would like to be password protected. Thanks.

 

[PHV]

Unless using the not reliable SendKeys instruction I don't know any programmatic way to protect a VBA project.
In fact, the VBE object model has no provisions for this.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

That's not what I wanted to hear but thanks PHV.

 

[shelby55]

Hi

Just out of curiosity, how does one use the SendKeys version? Thanks.

 

[shelby55]

Hi

PHV or Skip or anyone? How to use the SendKeys version to protect the saved copy's code?

Thanks.

 

[fumei]

How do I add code to the original so that the VBA code will be hidden? "

Do you want to protect it, or get rid of it? And why? If you are saving a copy, why not simly remove the code?

Gerry

 

[shelby55]

Hi Gerry

Thanks for replying. Another piece of code to this workbook is a chart with images and the images appear depending on the results of the last timeframe. This code has to copy with the workbook in order for the graphs to work in the copy (and the original) and is based on Chart_Activate.

So I just need to protect it, not get rid of it. Thanks.

 

[SkipVought]

The simplest solution would be to 1) password protect your orginal VB Project, save the original workbook as the new name, strip or hide the stuff you do not want your user to see. The VB Project will continue to be password protected.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}