Thread: Changing Formulas to values in "protected workbook"

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

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?

Roundy wrote:
> 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

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@virgin.net" wrote:

> 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?
>
> Roundy wrote:
> > 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
>
>

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!


Roundy wrote:
> 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@virgin.net" wrote:
>
> > 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?
> >
> > Roundy wrote:
> > > 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
> >
> >

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@virgin.net" wrote:

> 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!
>
>
> Roundy wrote:
> > 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@virgin.net" wrote:
> >
> > > 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?
> > >
> > > Roundy wrote:
> > > > 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
> > >
> > >
>
>

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





Roundy wrote:
> 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@virgin.net" wrote:
>
> > 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!
> >
> >
> > Roundy wrote:
> > > 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@virgin.net" wrote:
> > >
> > > > 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?
> > > >
> > > > Roundy wrote:
> > > > > 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
> > > >
> > > >
> >
> >