Access to Excel - Adding Change_Worksheet event to Sheet

[Paradigm]

Hi,

I am a newbie at VBA and have managed to piece together a project using snipits of codes from books and the internet.

The project involves using an access database to populate an excel file.
The excel file is generated by the database rather than opening and excel file already on the computer.

I have the need to add a Worksheet_Chang e event on a particular sheet in the excel file.
I know how to add a standard module with code but how do i get it to add an event into the file so thath everytime the worksheet is changed the macro starts up.

I hope i have explained it properly.

Any help would be greatly Appreciated.

Thanks
Paradigm

[nico5038]

Hmm, you're making this hard on yourself.
In general we do the data manipulation in Access and export the result to Excel.
Synchronizing data in two places is rather uncommon.

There is no real Worksheet OnChange event, but you could use the excel close of the worksheet as trigger.

Can you explain why you chose this solution ?

Nic;o)

[Paradigm]

Thank you for the quick response Nic,

Just to explain the reason im doing it is because most people are far more comfortable with excel but in terms of security it is essential for me to use access and i find it easier to manipulate data in there.

The only reason i need the event to trigger in excel is so that the end user has an option of manipulating their inputs. So fo example i give them 2 fields, either input a percantage or a full amount. and excel calculates the rest. The reason i needed the event was to lock on the cells if the other one had a value input in it.

Anyway i finally seemed to have figured out how to do it. So im happy :)

Thanks again
Paradigm

[FishVal]

Hello, Paradigm.

You can handle event of object within a scope of object variable declared with WithEvents predicate. Usually it is a global form module variable.

The following code (Access form module) will open blank Excel workbook on button click and listen to Change event of the first Worksheet.

Option Compare Database

Private WithEvents wks As Excel.Worksheet

Private Sub Command2_Click()
    
    Dim appExcel As Excel.Application
    
    Set appExcel = CreateObject("Excel.Application")
    With appExcel
        .Visible = True
        .UserControl = True
        With .Workbooks.Add
            Set wks = .Worksheets(1)
        End With
    End With
    
    
End Sub

Private Sub wks_Change(ByVal Target As Excel.Range)
    Debug.Print "Range " & Target.Address & " has been changed"
End Sub

Regards,
Fish

[NeoPa]

I only use up to 2003, but in my experience the method you are proposing only triggers successfully if the contained object has a handler for that particular event as well (I know as I use this concept in my databases when forms are closed).

This isn't a definitive statement, as there may be changes between applications (Excel; Access; Word etc) or even versions (2000; 2003; 2007 etc). I would check to be sure though before putting too much effort into it.