Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from Integrating Excel and Access

Keeping the Query Updated with VBA

This is an ideal time to introduce you to VBA. In the example above, you might not want to have to continue pressing refresh for your data. You may want the Query to automatically refresh itself with the new parameter each time you change cell B1. If you use VBA for programming Excel, you probably know that worksheets in Microsoft Excel have events that can have code associated with them. Events, as the name indicates, are triggered when certain events happen within Excel. Generally, these events are triggered from some type of user action. The event that you want here is the Worksheet_Change event. To set up Excel to automatically refresh your query each time you change the criteria, from your worksheet, press Alt-F11 to open the Visual Basic Programming Environment. You will see several sections; focus on the Project Explorer (Figure 2-15). If you do not see it, press Ctrl-R. Next, double-click on your current worksheet in the Microsoft Excel Objects list. There should be no code in the code window on the right side of the screen. Going from left to right on the top of the code window, go to the first drop-down box and select Worksheet, and on the second drop-down box, select Change. Excel fills in the opening and closing lines of the procedure; although the opening and closing lines are shown in the following code excerpt, they should not be typed in. Refer to the code below and type it into your procedure (Figure 2-16).

 Private Sub Worksheet_Change(ByVal ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page