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
--
wynb

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

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

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

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

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")
ActiveSheet.Unprotect Password:="justme" 'or whatever pword is
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
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord

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





--

Dave Peterson