none
VBA fails when more than one document is open RRS feed

  • Question

  • I developed a complex program for manipulating a multitab workbook and distributing it within the company. I did all the development work with only the one workbook open. When users open the workbook and have other workbooks open, the focus appears to change among the workbooks. As a result, some of the macros try to run against the other workbooks and not the intended workbook.

    Most of the programming uses ActiveWorkbook or ActiveSheet. How do I ensure the macros run against the desired workbook?

    Thursday, January 15, 2015 5:19 PM

Answers

  • Re:  regain control over when code runs

    Some Options...
    1.  Put a button on the sheet and let the user decide when to run the code
    2.  Change ActiveWorkbook to ThisWorkbook
    3.  Change ActiveSheet to ThisWorkbook.ActiveSheet
    4.  Use the actual workbook and sheet names... Workbooks("Sludge").Worksheets("Mud")
    '---
    Also suggest you find somebody in the  company to try your code before doing a wide distribution.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Thursday, January 15, 2015 6:33 PM