I need to update a public constant every time I add a sheet.

Is there a better alternative?

Greg,

You can't change the value of a constant; that's why they're
called constants. Create a public variable in a regular code
module:

Public NumSheets As Long

Then in the ThisWorkbook code module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
NumSheets = ThisWorkbook.Worksheets.Count
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Greg" wrote in message
om...
I need to update a public constant every time I add a sheet.

Is there a better alternative?

Or just use

ThisWorkbook.Worksheets.Count

directly. Just a thought.

--
Regards,
Tom Ogilvy

"Chip Pearson" wrote in message
...
Greg,

You can't change the value of a constant; that's why they're
called constants. Create a public variable in a regular code
module:

Public NumSheets As Long

Then in the ThisWorkbook code module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
NumSheets = ThisWorkbook.Worksheets.Count
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Greg" wrote in message
om...
I need to update a public constant every time I add a sheet.

Is there a better alternative?

"Chip Pearson" wrote in message ...
Greg,

You can't change the value of a constant; that's why they're
called constants. Create a public variable in a regular code
module:

Public NumSheets As Long

Then in the ThisWorkbook code module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
NumSheets = ThisWorkbook.Worksheets.Count
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Greg" wrote in message
om...
I need to update a public constant every time I add a sheet.

Is there a better alternative?

Thanks Chip,

I am curious to know why the body of the code needs to be in the
ThisWorkbook code module. Chip, what other circumstances would this
be required, and why would it used in the ThisWorkbook code module?

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

Basically the creation of a new sheet causes this procedure to run.
Workbook Level events are maintained in the ThisWorkbook module.

--
Regards,
Tom Ogilvy

"Greg" wrote in message
om...
"Chip Pearson" wrote in message
...
Greg,

You can't change the value of a constant; that's why they're
called constants. Create a public variable in a regular code
module:

Public NumSheets As Long

Then in the ThisWorkbook code module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
NumSheets = ThisWorkbook.Worksheets.Count
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Greg" wrote in message
om...
I need to update a public constant every time I add a sheet.

Is there a better alternative?

Thanks Chip,

I am curious to know why the body of the code needs to be in the
ThisWorkbook code module. Chip, what other circumstances would this
be required, and why would it used in the ThisWorkbook code module?