How do you update a public constant

[Greg]

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

Is there a better alternative?

 

[Bob Phillips]

Greg,

That should work. There is a NewSheet event that you can use

Private Sub Workbook_NewSheet(ByVal Sh As Object)
mySheets = mySheets + 1
End Sub

You will need to initialise it every time you open the workbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

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

 

[Tom Ogilvy]

Or just use

ThisWorkbook.Worksheets.Count

directly. Just a thought.

 

[Greg]

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

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?

 

[Tom Ogilvy]

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.