Avatar of Bernard Thouin
Bernard Thouin
Flag for Switzerland

asked on 

Setting an Excel VBA variable from an Access VBA code which starts the Excel


I have a fairly VBA-code-heavy Excel template (xlsm created from starting an xltm) which is used interactively by many users to capture data row by row and within a row, cell by cell. The VBA reacts in various ways to each change done by the users. Because sometimes the users want to copy whole rows from old Excels to the template, I have added radio buttons that disable the VBA processing on each cell by setting a boolean variable which is checked in the On Workook Change event. That all works fine since a long time.

Now I had to develop an interactive Access app which also has a fair bit of VBA and which needs to insert the data input in it into the Excel template. Virtually everything works fine, EXCEPT one thing, which is unfortunately essential: I don't know how to, from the VBA code in Access which handles the Excel (opening the template, and starting to set various cells/ranges), disable the processing done in the On Workook Change event. In other words, I don't know how my Access VBA should:
a) either trigger (click on) the radio button on the main Excel sheet which sets the global boolean variable which is checked in the On Workook Change event VBA code in Excel
b) or directly set that global boolean variable
so that my Access code can fill the Excel sheet with the required data without the Excel VBA code "reacting" to the changes.

So my questions are:
1) is it possible to achieve a) or b) above ?
2) If yes, how ?

Thanks for your help.

Microsoft AccessMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Bernard Thouin

8/22/2022 - Mon