Call a subroutine from ThisWorkBook

[Philosophaie]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call SortYearMonthDayAscending
End Sub

Call SortYearMonthDayAscending does not work. Is there a way to access this
subroutine in "Sheet1" from BeforeClose?

 

[ozgrid.com]

Try

Run "SortYearMonthDayAscending"

 

[Chip Pearson]

In what module is SortYearMonthDayAscending declared? All else being
equal, it should be in a normal code module, not in one of the sheet
modules. As a general rule, any code that is called from more than one
location should not be in an object module (class module,
ThisWorkbook, a userform's code module, or a Sheet module). It should
be in a regular code module.

That said, you can call a procedure in a Sheet module by doing two
things. First, make sure the procedure in the Sheet module is declared
as Public. E.g.,

Public Sub SortYearMonthDayAscending()
' your code here
End Sub

By default, methods in a Sheet module are Private, so you must use the
Public keyword to expose the method to the world outside the module.

Then, prefix the call with the code name of the sheet module. The code
name of the sheet is the name that appears in the project window that
is not enclosed in parentheses. For example, the list of worksheet
modules is displayed in the project window as something like

Sheet1 (Sheet One)
Sheet2 (Some Sheet)
Sheet3 (OtherSheet)

The names within the parentheses are the names that show on the
worksheet tabs and are used with the Worksheets collection. The name
that is not enclosed in parentheses is the code name, which is how the
sheet is known to VBA. Changing a sheet name does not change the code
name, so if you change "Sheet One" to "My Sheet One", the code name
will remain "Sheet1". Thus, in the example list above, "Sheet1" is
the code name and "Sheet One" is the sheet tab name. Call your code
using the code name with code like

Sheet1.SortYearMonthDayAscending

It is also possible to use CallByName to do this.

CallByName Sheet1, "SortYearMonthDayAscending", VbMethod



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

First, I think this kind of thing should be done when the workbook opens--not
before it closes.

If your code makes a change, then the user will be prompted to save or discard
the changes.

If they discard the changes, then your work is lost. And there could be valid
reasons why the user wants to discard the changes -- maybe they deleted 87
sheets in error!

That's one of the reasons I don't think the code should decide, too. I don't
think any developer knows what the user intends to do with the changed workbook.

In stead, I'd use the workbook_open event.

And you could call a procedure in a worksheet module with code that uses the
Codename of the sheet:

Call Sheet4.SortYearMonthDayAscending

or code that uses the name of the sheet on the tab (visible in excel):

Call Worksheets("Sales Data for Our Dept").SortYearMonthDayAscending

====
Make sure you make the SortYearMonthDayAscending procedure public, not private:

Public Sub SortYearMonthDayAscending

===========
All that said, I think Chip's suggestion to put the code into a General module
makes the most sense.