Solved

CALL Worksheet Change event that resides in xla Add-In

Posted on 2010-09-07
11
946 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

803 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