Solved

CALL Worksheet Change event that resides in xla Add-In

Posted on 2010-09-07
11
918 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now