How to call a function after a worksheet change

JoeSmith3003

New Member
Joined
Sep 10, 2006
Messages
5
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"...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

New Member
Joined
Sep 10, 2006
Messages
5
>>"...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

Active Member
Joined
Nov 26, 2003
Messages
440
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:
Code:
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:
Code:
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!
 

Forum statistics

Threads
1,171,686
Messages
5,876,890
Members
433,217
Latest member
Muhammad Tanzeel Ur Rehma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top