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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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,137,201
Messages
5,680,136
Members
419,885
Latest member
Murugesha

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