Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Placement of Code (XP)

    If you want code to apply to every worksheet in a workbook, where should the code be placed? I don't want the code in personal.xls as I only use it for one particular workbook. Under VBAProject it lists the number of sheets and also ThisWorkbook. I tried placing in ThisWorkbook but the code doesn't function. I have to place the code on every worksheet (12) in order to get the code to run. What is the purpose of the ThisWorkbook?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Placement of Code (XP)

    The ThisWorkbook module is intended for event procedures for the workbook as a whole, such as the Workbook_Open event and the Workbook_BeforeClose event. It is not intended to store general code.

    Each worksheet in the workbook has an associated module. This is intended for event procedures that pertain to that worksheet, such as the Worksheet_Change event. It us not intended to store general code either.

    You can create a so-called standard module by selecting Insert | Module in the Visual Basic Editor. Standard modules are intended to hold general code, that is macros, other procedures and functions. You can create multiple standard modules in a workbook if you want to organize your code in groups of related procedures etc.

    A procedure or function stored in a standard module is public by default, i.e. you can call it from other modules, including the ThisWorkbook module and the worksheet modules. If you want a procedure or function to be known only in its own module, prefix the definition with the keyword Private:

    Private Sub MyCode()

    If you want to create worksheet event code for all workbooks, you might put the code they have in common in a standard module, and call this code from the event procedures in each worksheet module.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Placement of Code (XP)

    Thank you. Stillnot sure that I completely understand, but I am going to play around.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Placement of Code (XP)

    What kind of code do you have? Macros, user-defined functions, code to run when the user clicks a button, code to run when the user enters or edits data, ...?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •