help with protect sheet

W

WYN

good day all,i have a invoice template with a macro set to number them.when i
protect the sheet the macros stops working.any suggestions? thx wyn
 
G

Gord Dibben

WYN

Unprotect the sheet, do your stuff then reprotect.

Sub Numberit()
ActiveSheet.Unprotect Password:="justme"

'your code or macro name goes here

ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord Dibben Excel MVP
 
W

WYN

hi gord,my existing code is under this workbook.should i put the code in a
new module,worksheet or combine it under workbook? thx wyn
 
G

Gord Dibben

By "this workbook" do you mean you have Workbook_Open or Activate code that
runs when the workbook is opened or activated?

It could be inserted around the existing code in that case.

You could also combine it with your code and stick it in a worksheet_activate
routine.

If you have trouble, post your current code and I'm sure we can figure out
where to add it.


Gord
 
W

WYN

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")
With .Range("q5")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("n1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub
hi gord,here is the coe i am using.thx wynb
 
G

Gord Dibben

Try this.

Remove the >>

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")With .Range("q5")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("n1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, _
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End WithEnd Sub


Gord
 
D

Dave Peterson

This might get you closer...

Option Explicit
Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")
.Unprotect Password:="hi!"
With .Range("q5")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("n1")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
.Protect Password:="hi!"
End With
End Sub
 
G

Gord Dibben

So much more concise<g>

Just for interest. I stuck my re-protect after the last End With.

Without testing, does it make a difference?


Gord
 
D

Dave Peterson

But your code unprotected/protected the activesheet.

By using the with/end with structure, I was unprotecting/protecting the
"Invoice" sheet.
 
G

Gord Dibben

Ah so.

Never even noticed the "Invoice" sheet was not active.

Getting worser and worser.

Maybe find a different hobby to go along with the golfing which lends itself
perfectly to my poor memory and inattention to detail.

I can turn in some amazingly low scores.


Gord
 
W

WYN

gentlemen that works perfectly.i thank you for your help. hey gord i see that
you must have the same golf rule book as me.count only the straight drives
and no more thah two puts. thanks again.wyn.
 
D

Dave Peterson

I have that same ability in bowling.

(I'm not getting involved in this one <vbg>!)
 
G

Gord Dibben

WYN

Glad to see Dave got you straightened out.

Yep. Ball in pocket after max allowed. Funny how those other guys won't let
me do that in competition.

Gord
 
Top