Private Sub Worksheet Change - Multiple on same worksheet

  • What is the best way to incorporate more than one Private Sub Worksheet Change in one sheet?


    Is there a work around using modules?


    Here is the code, I can't have all 3 work at the same time. They all do different things.


    I might even add more Private Sub Worksheet Change and I am trying to find the easiest way to run them.


  • Re: Private Sub Worksheet Change - Multiple on same worksheet


  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    Thank you StephenR.


    Being pretty new to VB, could you show me the full code?


    I tried but I am getting errors. Maybe I have to remove something and not paste the entire code?


    I will copy this code in my worksheet named Input.
    Thank you.

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    I think this is right.

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    I am not getting any errors but it is not working.


    For example, one code is used to block a user from entering the same value twice in a column...however, I can now.


    The time stamp code is not present...



    Thanks again for taking the time to help me with VBA. I appreciate

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    The 3 different works perfectly if used once at a time, however, the are not working when they are all used at the same time in the same worksheet.


    Btw, I have 2 sheets, one call input will all my information and one called DD will all my various drop downs options

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    Would it be possible to have one module for each code and have them called in my Input worksheet? For example, have one module for each of my 3 codes?

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    Spotted some gremlins. Try this instead

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    You are a very smart man. Thanks for sharing your valuable time. It is almost perfect.


    The first code in the list was to have my drop down lists in sheet DD updated if a user saw the choice was not available then he could just write it in the drop down box, press enter and it would appear in the drop down menu options pulled from worksheet DD.


    This part is partially working. The issue I get now is multiple items are being repeated in my drop down options (duplications) pulled from worksheet DD.

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    If this doesn't work, can you post a workbook?

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    No needs. Works perfectly now. :)


    Can I copy this code right under code above? It is not a worksheet change but a selection change. Not sure if this makes a difference?



    StephenR, thanks again!!

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    OK, not sure I understand this one...


    I paste it in the worksheet file VBA right under the current code (see above)


    When I do, I get the following error : compile error only comments may appear after end sub, end function, or end property.


  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    This code is in my Microsoft Excel Objects Sheet 1 (input)


    When I remove the option explicit line, I get the following error : Run-time error 28 Out of Stack Space. If I click debug, the Set rngcmt = wsCmt.Range("comments") line is highlighted yellow.

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    Thanks.


    When I click on a cell in the first column of my input worksheet, the comment I wrote in the comments worksheet (if it match the exact PGRB code) is pulled and shown on my input page worksheet.
    Each time a cell in my table first column is selected, it will show the comment inputted in the comments worksheet. The comment will only appear when a cell in the first column is selected and has a comment or not (empty).


    Obtained from



    ' Developed by Contextures Inc.

    ' http://www.contextures.com http://www.contextures.com/excelfiles.html CM0002


    If not clear I'll try to post an empty template.


    Basically, I am just copying the Ozgrid code.

  • Re: Private Sub Worksheet Change - Multiple on same worksheet


    OK, I revised the last part to (see below), the good : no errors, the bad : not working.


    Full code in the Input worksheet is :


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!