Changing Formulas to values in "protected workbook"

[Guest]

I have a workbook with an order sheet in it. I created a macro so that when
the person is done with the order, they click a button and it creates a copy
of the correct sheets, deletes the buttons and hyperlinks from the worksheet,
and breaks any links to the original workbook. The last step is to change
the formulas to values in the worksheet. I get an error message when I run
the macro, and when I click help, it talks about macro security and trusting
VBA...but that does not resolve the issue. I have read suggestions about
protecting and unprotecting the worksheet, but I would rather not do that
because then the password is right in the VBA code and easily accessible to
the "user". Any help would be greatly appreciated. I will put *** around
the section that hangs up on me.

TIA

Range("A1").Select
If Range("NumberofColumns").Value = 1 Then
Sheets(Array("Form", "1 Piece Column")).Copy
Sheets("Form").Select
ElseIf Range("NumberofColumns").Value = 2 Then
Sheets(Array("Form", "2 Piece Column")).Copy
Sheets("Form").Select
ElseIf Range("NumberofColumns").Value = 3 Then
Sheets(Array("Form", "3 Piece Column")).Copy
Sheets("Form").Select
ElseIf Range("Selectionindex").Value < 3 Then
Sheets(Array("Form", "1 Piece Column")).Copy
Sheets("Form").Select
Else
Msg = "Please click on pricing and go through the wizard before clicking
Save a Copy of this Quote." ' Define message.
Style = vbOKOnly
Title = "Not Ready To save a copy" ' Define title.
Response = MsgBox(Msg, Style, Title)
Exit Sub
End If
ActiveSheet.Shapes("Button 1").Select
Selection.Delete
ActiveSheet.Shapes("Button 2").Select
Selection.Delete
ActiveSheet.Shapes("Button 5").Select
Selection.Delete
ActiveSheet.Shapes("Button 6").Select
Selection.Delete
Range("D1").Value = ""
Range("L19").Value = ""
*******************************************
'Removes ALL formulas and replaces them with valves,
'for each sheet in your workbook
Application.ScreenUpdating = False
Dim Sht As Worksheet
Dim rng As Range
For Each Sht In ActiveWorkbook.Worksheets
Sht.Select
Set rng = ActiveWindow.ActiveCell
Sht.Cells.Copy
Sht.Cells.PasteSpecial xlValues
rng.Select
Set rng = Nothing
Next Sht
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("Form").Select
*********************************************
Dim astrLinks As Variant

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks
'delete names

Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
' go back to main page of form

 

[aidan.heritage]

If you have a password protected sheet, you have to unprotect it before
you can update any locked cells - you could always password protect
the macro as well, which would hide the password away?

 

[Guest]

Is there a way to protect macros using a password in code? Currently I have
been protecting and unprotecting the sheets and workbook using code and then
deleting the macros before distributing it. So, I am wondering if there is
some code I could use for that same task for protecting macros.

TIA

 

[aidan.heritage]

I've just re-read the question - forget about passwords! The process
sounds like it should be

CREATE A NEW WORKBOOK
Set the number of sheets to be the same as the original workbook
Set the names of the sheets to be the same as the original workbook
Set the contents of the sheets to be the values and formats of the
original workbook

if so, then this would leave the original workbook alone, and therefore
not have password issues!

 

[Guest]

Could you get me headed in the right direction on this. As you can see in
the code below, depending on the situation I need different worksheets.
TIA

 

[aidan.heritage]

This code will give you two objects, both refering to workbooks, and
both with the same number of worksheet and same name, with the new
sheet having the values from Range A1:Z10 on each worksheet

Dim mybook As Workbook, curbook As Workbook
Set curbook = ActiveWorkbook
Set mybook = Workbooks.Add
While mybook.Sheets.Count <> curbook.Sheets.Count
mybook.Sheets.Add
Wend
counter = 1
For Each sht In curbook.Sheets
mybook.Sheets(counter).Name = sht.Name
mybook.Sheets(counter).Range("A1:z10").Value =
sht.Range("A1:Z10").Value
counter = counter + 1
Next