By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Access to Excel - Adding Change_Worksheet event to Sheet

P: 5

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_Change 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.

Jan 5 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,072
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 ?

Jan 5 '09 #2

P: 5
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
Jan 5 '09 #3

Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. Private WithEvents wks As Excel.Worksheet
  5. Private Sub Command2_Click()
  7.     Dim appExcel As Excel.Application
  9.     Set appExcel = CreateObject("Excel.Application")
  10.     With appExcel
  11.         .Visible = True
  12.         .UserControl = True
  13.         With .Workbooks.Add
  14.             Set wks = .Worksheets(1)
  15.         End With
  16.     End With
  19. End Sub
  21. Private Sub wks_Change(ByVal Target As Excel.Range)
  22.     Debug.Print "Range " & Target.Address & " has been changed"
  23. End Sub
Jan 5 '09 #4

Expert Mod 15k+
P: 31,419
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.
Jan 5 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.