• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1200
  • Last Modified:

CALL Worksheet Change event that resides in xla Add-In

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
Fordraiders
Asked:
Fordraiders
1 Solution
 
gordontmCommented:
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
 
Patrick MatthewsCommented:
>>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
 
FordraidersAuthor Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Rory ArchibaldCommented:
Read the link Patrick posted. :)
0
 
Patrick MatthewsCommented:
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
 
FordraidersAuthor Commented:
matthew, so your latest post will be in the Add-in..."ThisWorkbook"
sorry just want to be sure..
0
 
Patrick MatthewsCommented:
That's the idea :)
0
 
FordraidersAuthor Commented:
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
 
Patrick MatthewsCommented:
I can see why you might have thought that, but no, your other procedures should not need name changes :)
0
 
FordraidersAuthor Commented:
OK...I did not have my references set in my add-inn..
Working great now !
0
 
FordraidersAuthor Commented:
Thanks to all for helping so much !
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now