protect worksheet - but allow to insert new sheet

[ankita.sethi]

Hi,

I have a report to prepare where I am giving a command button. The code behind the command button inserts a sheet, edits that and deletes it finally (It is meant for printing something).

However, I want to protect the report and do not allow user to edit anything.
One way I thought was to run the code for removing protection before running the command button code, and finally put the protection back through code only. However, this is making the process really slow somehow.

Can someone please advice me a more efficient method?

 

[RichardMGreen]

Can you post your code?

 

[ankita.sethi]

Here is the code. If I run this in the protected mode, it doesn't run as then inserting the new sheet would not be allowed! Please helP!

Sub printout_financial()
'adds a new sheet, puts the information to be printed, prints, and then deletes the new sheet.
'add new sheet
Sheets.Add
ActiveWindow.DisplayGridlines = False
Dim NewSheet As String 'for new sheet's name
NewSheet = ActiveSheet.Name
'sheet 1 - MTD Graphs
'prepare the header
Sheets(NewSheet).Select
Range("A1").FormulaR1C1 = "North America Learning Dashboard"
Range("A1:I1").Select
Selection.Merge
With Selection.Font
.Name = "Copperplate Gothic Light"
.Size = 22
End With

Range("A2").FormulaR1C1 = "Expenditure by Cost Center - Planned Vs. Actual"
Range("A2:I2").Select
Selection.Merge
With Selection.Font
.Name = "Bookman Old Style"
.Size = 16
End With
Selection.Interior.ColorIndex = 15
Selection.HorizontalAlignment = xlCenter
'copy chart 5
Sheets("Financial").ChartObjects("Chart 5").Activate
ActiveChart.ChartArea.Copy
Sheets(NewSheet).Select
ActiveSheet.Paste
'adjust the positioning
ActiveSheet.Shapes("Chart 1").IncrementLeft 9#
ActiveSheet.Shapes("Chart 1").IncrementTop 58#
ActiveSheet.Shapes("Chart 1").ScaleWidth 0.85, msoFalse, msoScaleFromTopLeft
'copy chart 6
Sheets("Financial").Select
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False

Sheets(NewSheet).Select
Range("A29").Select
ActiveSheet.Paste
'adjust the positioning
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.Shapes("Chart 2").IncrementLeft 7.5
ActiveSheet.Shapes("Chart 2").IncrementTop 65#
ActiveSheet.Shapes("Chart 2").ScaleWidth 0.85, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 2").ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
'decrese row width
Range("A1").Select
Rows("3:52").RowHeight = 10.5
Range("A30").Value = "* The bars in the above graph depicts the actual expenditure"
Range("A57").Value = "* The bars in the above graph depicts Cost Efficiency (Difference of Actual from Planned Expenditure)."
ActiveSheet.printout
ActiveWindow.SelectedSheets.Delete

End Sub

 

[ankita.sethi]

apologies for the confusion! the above code is not the one creating the problem. There is another code that needs to calculate some numbers and change some numbers on the sheet -- so when the sheet is protected, it creates a problem. Can you help me deal with this?
(on cloder examination, inserting a sheet is not a problem when protection is on!!)

Currently, I am using these two codes (at the starting and ending, resp.)


Sub unsecure()
' used at the beginning of the code so that it would allow to edit
For Each Worksheet In Worksheets
If Worksheet.ProtectContents = True Then
Worksheet.Unprotect Password:="PWD"
End If
Next Worksheet
End Sub



Sub secure()
' used at the end of the code to protect the workbook again.
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.ProtectContents = False Then
Worksheet.Protect Password:="PWD", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
Next Worksheet
End Sub

 

[RichardMGreen]

You might be better off protecting the workbook and not individual sheets.
Protecting each sheet will take time.