Solved

CALL Worksheet Change event that resides in xla Add-In

Posted on 2010-09-07
11
975 Views
Last Modified: 2012-05-10
excel 2003 vba..

What I need:
When a person selects  "MyXref_Add-In" from the tools menu...

My Form open event works great.

However,

I need To Call :
Private Sub Worksheet_Change(ByVal Target As Range)   from the excel add-in

and other functions that reside in the xla workbook ?

Is this possible ?

Thanks
fordraiders
0
Comment
Question by:fordraiders
11 Comments
 
LVL 2

Expert Comment

by:gordontm
ID: 33617601
If I understand the problem correctly, the following may help.
1. To call a procedure called Hello which is stored in a sheet (e.g. Sheet1) and not in the vba modules then you can it using Sheet1.hello
2. procedures which are marked Private can not be called from outside the module. They need to be changed to public.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33617609
>>I need To Call :
>>Private Sub Worksheet_Change(ByVal Target As Range)   from the excel add-in

Please explain why you want to do that.  A Worksheet_Change event only fires for changes on the worksheet itself.

If what you really mean is, "I need some sort of universal Worksheet_Change handler", then what you really need is to instantiate the Application class using WithEvents to enable the Application's SheetChange event.

Excel MVP Chip Pearson has a very good introduction on this topic at: http://www.cpearson.com/excel/AppEvent.aspx
0
 
LVL 3

Author Comment

by:fordraiders
ID: 33617684
matthewspatrick..

I have workbooks that I will be getting in.

I have code that works right now for a  a single workbook.

I want to be able to use this code for any workbook via Add-In

My first time trying to call code from an add-in

Am I going to have to use command buttons ?







This code is in a workbook..that works without an add-in
Private Sub Worksheet_Change(ByVal Target As Range)

'========================================================================
Dim str As String

str = wRange
str = Left(wRange, 1)
str = "$" & str & "$"

If Target.Count = 1 Then
   If InStr(1, Target.Address, str) > 0 And Target.Value <> "" Then
      'we are in the entry column
      If GetData(Target) <> "" Then
        MsgBox GetData(Target)
      End If
    PrivateLabelsearch
    ParentSearch
    GreenAltSearch
    End If
End If

End Sub

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33617741
Read the link Patrick posted. :)
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33617809
Assuming that your original code will work as is, then your ThisWorkbook modules should look like this:


Option Explicit
 
Public WithEvents xlApp As Excel.Application
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Set xlApp = Nothing
    
End Sub
 
Private Sub Workbook_Open()
    
    Set xlApp = Excel.Application
    
End Sub
 
Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim str As String
    
    str = wRange
    str = Left(wRange, 1)
    str = "$" & str & "$"
    
    If Target.Count = 1 Then
        If InStr(1, Target.Address, str) > 0 And Target.Value <> "" Then
            'we are in the entry column
            If GetData(Target) <> "" Then
                MsgBox GetData(Target)
            End If
            PrivateLabelsearch
            ParentSearch
            GreenAltSearch
        End If
    End If
    
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 33618129
matthew, so your latest post will be in the Add-in..."ThisWorkbook"
sorry just want to be sure..
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33618199
That's the idea :)
0
 
LVL 3

Author Comment

by:fordraiders
ID: 33618234
ok..so If I have a module in the add-in...

it is currently..Module1



Public Function GetData(rng As Range) As String
to this ?
Public Function Xl_App_GetData(rng As Range) As String


or any other functions I may have ?

Appreciate the help very much !




0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33618307
I can see why you might have thought that, but no, your other procedures should not need name changes :)
0
 
LVL 3

Author Comment

by:fordraiders
ID: 33618986
OK...I did not have my references set in my add-inn..
Working great now !
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 33619000
Thanks to all for helping so much !
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question