How to call a function after a worksheet change

[JoeSmith3003]

Hello Everyone,

Since I had such a great response for my last question, I figured I'd try again with another one!

My question is the following:

How do I call a function after a worksheet change WITHOUT embedding the UDF call inside the worksheet itself?

I know of the Worksheet_Change property that exists in the worksheet itself, but I want to avoid embedding my UDF within this object.

Any help is appreciated!!

Joe

 

[PaddyD]

"...but I want to avoid embedding my UDF within this object. "

why?

"How do I call a function after a worksheet change WITHOUT embedding the UDF call inside the worksheet itself?"

Care to be more specific? what function, after what changes to the sheet?

 

[JoeSmith3003]

>>"...but I want to avoid embedding my UDF within this object. "

>why?

I guess I'll explain the situation. I want to create a library of worksheet functions where someone just drags the .bas file into their Excel file and then uses a simple well defined interface.

For the most part, most of the people using these functions will have no programming experience.


>>"How do I call a function after a worksheet change WITHOUT embedding the UDF call inside the worksheet itself?"

>Care to be more specific? what function, after what changes to the sheet?

Any UDF which returns a something. After changing entries in any cell on the sheet.

If you need anymore information, please reply.

Thanks again!

Joe

 

[sbendbuckeye]

Hello Joe,

Check out the AltStartupPath property. It allows you to specify a path (either network or local, not sure about UNC). Excel will attempt to open all files in that path every time in loads.

1. Create your VBA library Excel file (eg JoesMultiUseFiles.xls)

2. Save the file created in Step 1 above in a separate, special folder

3. In the workbook, click Tools, Options, General and set the value for Alt Startup in the lower left hand corner of the tab to the separate, special folder used in Step 2 above. NOTE: Excel will try to open every file in the specified folder so make sure nothing else is in that folder, especially any NON Excel files

4. Close and Reopen Excel - you will see your file from Step 1 above open already in Excel

5. In the workbook, Click Windows, Hide and hide the file created in Step 1 above - At this point it can be treated like the global macro file Personal.xls

6. Let's say you had code like this defined in JoesMultiUseFiles:

Option Explicit

Public Sub Sub1()
    MsgBox "Sub1 in " & ThisWorkbook.FullName
End Sub

Public Function Function1() As String
    Function1 = "Function1 in " & ThisWorkbook.FullName
End Function

Public Function Function2(FirstName As String, _
                          LastName As String, _
                          Optional LastNameFirst As Boolean = False) As String
    If LastNameFirst Then
        Function2 = LastName & ", " & FirstName
    Else
        Function2 = FirstName & " " & LastName
    End If
End Function

7. Reference your code like so from within another workbook:

Sub Test()
    Call Application.Run("JoesMultiUseFiles.xls.xls!Sub1")
    MsgBox Application.Run("JoesMultiUseFiles.xls.xls!Function1")
    MsgBox Application.Run("JoesMultiUseFiles.xls.xls!Function2", "FirstName", "LastName")
    MsgBox Application.Run("JoesMultiUseFiles.xls.xls!Function2", "FirstName", "LastName", True)
End Sub

Good Luck!