Protect Workbook with macro-code

[Mötley]

Hello, I'm using macro-code to protect workbook.

Sub UusiSivu()
ActiveWorkbook.Unprotect
Dim CurrentDay As Integer
Dim NewName As String
Dim WS As Worksheet
Set WS = ActiveSheet
If IsNumeric(Right(WS.Name, 2)) Then
   CurrentDay = Right(WS.Name, 2)
ElseIf IsNumeric(Right(WS.Name, 1)) Then
   CurrentDay = Right(WS.Name, 1)
Else
   Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = Format(Date, "dd.mm.yyyy")
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
'Copies the current sheet to the end of the workbook
    Sheets("Default").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = NewName
  Dim oleObj As OLEObject
Else
   Set checkWs = Nothing
   MsgBox "Uusi taulukko voidaan lisätä huomenna."
   ActiveWorkbook.Protect
End If
End Sub

It works like this --> I protect workbook. I need to add sheet (you can't add if you have protected workbook), so I use code that allows me to add more sheets.

Problem is that every time I protect workbook and then run the code, it doesn't stay protected. Workbook IS protected but when I add one more sheet, protection goes off. Could you please help me what's wrong with my code, so that it stays protected when I add sheets etc etc.

 

[bensonsearch]

Hi,
from what i can see you are only re protecting in the if statement. try putting at the end ie:

Else
   Set checkWs = Nothing
   MsgBox "Uusi taulukko voidaan lisätä huomenna."
   End If
[B]ActiveWorkbook.Protect[/B]
End Sub

 

[Mötley]

Thanks! I got it working

 

[bensonsearch]

Your welcome

 

[Mötley]

Hmm.. I still need help with one thing, in this case I wanna protect only ONE cell, prevent users to modify it anyhow. How can I do this?

 

[bensonsearch]

so you dont want to protect everything? only a single cell right?

 

[bensonsearch]

as far as i know you can ctrl A to select all of the cells, right click and go to format cells, then on go to protection and untick protected. on the cell you wish to protect do the same but tick protected. then protect sheet in code all done

 

[Mötley]

I'm just looking different options =) Thanks I'll try that