Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Hi All ... in one of my last questions posted here, you guys taught me about the Private Sub Worksheet_Change procedure. Well, now the company wants more !!!

    I have a workbook with VBA that creates sheets based on another workbook's values/inputs. These sheets are not always created if they are not needed (based on the values/inputs in the other workbook).
    However, in the sheets that are created, I need to add a Change procedure. Unfortunately, I do not know how to add a Change procedure if I cannot see the sheet first.

    In the other post, where I learned about the Change procedure, I simply found the sheet under Microsoft Excel Objects and right-clicked and went from there.
    Now, I don't know where to go to place the code for a change event?

    Does this make sense?

    Thanks,
    --cat

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

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    One way to do it would be to copy a sheet that already has the Worksheet_Change event procedure, then modify the contents of the worksheet as needed. That way, you wouldn't have to mess with the code each time. Another way would be to create the code using VBA, but that is very technical, and rather tricky. If you're interested, take a look at Chip Pearson's Programming To The VBE.

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Thanks, Hans. I hadn't thought about putting it in the sheet I add first. Congrats on your 50,000 Post ! I know that I am personally glad that you are around when I have my questions!

    About the Change procedure and performance, what type of hit will I take?

    Thanks again,
    Cindy

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

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    The general rule is that you should only put code in the Worksheet_Change event that is strictly necessary, since the code is run each time the user enters, modifies or deletes a cell value. But with modern-day computers, you can probably put quite a bit of code in there before you would start noticing a decline in performance.

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    I'm back ...

    I've placed code in a Change procedure sheet named "Generic" in an Excel Template file. I have another workbook macro that adds the Generic Sheet to the workbook, then proceeds to change the name of the sheet just added. Then, when my program first modifies the sheet, I get this error ... "Compile Error - sub or function not defined". I'm pretty sure it is because the name has changed from "Generic" to the appropriate name.

    Is there a way that the Change Procedure can "know" the new name or a way to ... I don't know, get around this?

    Thanks,
    --cat

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

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Does the code refer to the sheet name? It might help if you posted the code (in an attachment if it is long)

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Hi,

    The Change procedure does not refer to the sheet's name (which starts out as "Generic" and gets changed later after it is added to another workbook).

    All the Change procedure code does (or should do) is hide column D if cell D7 = 0 and unhides column D if cell D7 <> 0.
    I really don't want to check for changes until the sheet has been added and set up corrected through my other macro. Hence the

    I will try to get something to attach ... the workbook that creates this file is fairly short, but it calls another workbook that is very large and, unfortunately, just about everything is confidential as I am working for a pharmaceutical company.

    Thanks,
    Cindy

  8. #8
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Hi, (so glad this is Friday!)

    It seems that the Change procedure doesn't even work ... so, I won't worry about adding it to a new workbook and modifying it, yet. :-)

    I have attached the template. Again, I really really appreciate this forum's help!

    Thanks,
    Cindy

    p.s. I had to change the file from a XLT to a XLS since I couldn't attach it.

  9. #9
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Hi All,

    I have re-attached another file.

    For the Change procedure to work, does the user need to "manually" change the cells? ... Or can the cells be changed by formulas?

    Eventually these cells will be formulas (vlookups, indirect, etc) looking at another file, the datafile. The user changes that file, the values change in this sheet. When the values change so that D7=0, I wish to have column D hidden.

    Thanks,
    --Cindy

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    The Worksheet Change event does not fire when a formula recalculates. It will fire when a user or a macro changes the cell. You can use the Calculate event to catch a change due to a formula recalculation. However, the calculate event does not tell you what cell is recalculating, so the way you code it may be different.
    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Woo Hoo !!! That worked. I learned about the Change and the Calculate Event all in one week!!! I think I'm going to like this "Event" vba stuff. (Now I need to tackle the rest of the problem ... ) :-)

    Thank you all!
    Cindy

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Newly created sheet needs a Change Event (Excel 2003 & VBA)

    Chip Pearson has a primer on Event Procedures that you may find interesting or useful.

    Steve

Posting Permissions

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