help with protect sheet

[Guest]

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

 

[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

 

[Guest]

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

 

[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

 

[Guest]

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

 

[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

 

[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

 

[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

 

[Dave Peterson]

But your code unprotected/protected the activesheet.

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

 

[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

 

[Guest]

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.

 

[Dave Peterson]

I have that same ability in bowling.

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

 

[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

 

[Guest]

yuo must have realy big pockets dave. wyn

I have that same ability in bowling.

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

 

[Dave Peterson]

I deleted lots of responses. <vbg>

yuo must have realy big pockets dave. wyn