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

On Thu, 17 Feb 2005 18:49:40 -0600, Dave Peterson
wrote:

But your code unprotected/protected the activesheet.

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



Gord Dibben wrote:

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

On Thu, 17 Feb 2005 18:28:44 -0600, Dave Peterson
wrote:

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



WYN wrote:

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

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

On Wed, 16 Feb 2005 18:19:04 -0800, WYN wrote:

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

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

On Wed, 16 Feb 2005 15:55:01 -0800, WYN wrote:

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