Private Sub Worksheet_SelectionChange vs other options

  • Namastey and greetings experts!


    I am writing a SelectionChange event macro to trigger it on each selection change. This has several changes in a range and mostly to hide lines and update formula in col D cells.


    My learning point here is that this is basis the need I have, written it using online expert resources like these, and built it in bits and pieces. The code runs fine, but causes the screen update a lot and jump a lot.


    When I tried changing this to Change from SelectionChange event, the functionality doesn't work.


    So, requesting suggestions from you all experts to guide me to make it work better without making the screen jump and take time as it does on my code.


    Here are the code lines:-

    Note: this code is inside the Sheet, and not in a module, for it to trigger on each change.




    If there is any scope of performance improvement in this code, please suggest. Your guidance is highly appreciated.




  • Place this line at the beginning of your code

    Code
    1. Application.ScreenUpdating = False

    and this line at the end

    Code
    1. Application.ScreenUpdating = True

    Such as:

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Hey there dangelor

    Glad I could help!

    Hey there dangelor,

    Greetings!


    Further in this series, I need your expert advice and more help needed.


    I am trying to improve the runtime of this event based macro, as the performance is slow and it takes a few second on each cell click to be able to enter the values.


    Code as below:-

    Your expert advice and any suggestion will be highly appreciated to enhance the speed of this macro!


    Thank you!

  • One thing that might help is using the Worksheet Change event instead of the Worksheet Selection event, but the hiding and unhiding is your problem.

    Namaste dangelor,


    Thank you for your time and effort.

    You are right, I can use the Change Event, but it would not perform the Hiding and Un-hiding.


    Currently it takes about 3-4 secs on each cell when clicked before it would allow to enter a value.

  • Adding formulas and content by code could be avoided and possibly speed up the code.


    An example workbook would be helpful

    Thank you royUK and dangelor for your revert and effort :). I have attached a sample file herewith. This sample file works a little faster then the actual file.


    If you can suggest any improvement in this, it would be great.


    :thumbup:

  • Might be a bit faster...

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Might be a bit faster...

    Namastey dangelor!


    I wanted to take a moment to thank you for all your help and efforts here, and I know how time consuming it could be, specially for a novice like me, this is all a big deal, and I am learning so much in this process from you.


    I tried this in my actual file, and seems to be working fine and faster then the previous version I attached. There are a couple of places which are not responding, which I am working on, and will update you soon.


    If you are in US, then wishing you a Happy 4th of July!

    Otherwise too, stay safe and awesome!


    Also, if you can please suggest a course which you feel is good for freshers in VBA, or your/other blog where I can learn VBA from scratch, it would be great.

    I work on Excel all the time, but VBA is a new domain for me, even though I have recorded a few macros here and there, but understanding it and writing codes like you did, is something I am working towards.


    Learner.Here

  • Might be a bit faster...


    Namastey dangelor,


    As I was trying the run the given sequence of codes, I ran into an issue.


    I noticed you had not set the PW Lock at the end of Change event, and when I set the PW Lock On, it would run into a bug.


    When I hit the Refresh macro to reset the fields, the code to hide rows won't run as the Change Event would lock it, and it would throw Run time error "1004" Unable to set the Hidden property of the Range class


    When I run it without locking on the Change Event, it runs the procedures without an error. But this keeps the file Unlocked, as Refresh does lock it, but once clicked on the selection area, the file remains open until Refresh is hit again.


    I am trying to figure a way out of this. Any suggestions will certainly help.


    Thank you!

  • Try this...

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Try this...

    Namastey dangelor,


    Thank you for your valuable inputs. This works wonderfully with the setup. You gave me one more dimension to try with.


    I am adding some more features, and will test some more, will update you.


    Take care!

  • Namastey dangelor,


    This is working just fine. Thanking you for all your help, will share a sample of the working file later with you, as I have added few more steps to it.


    There is one thing though that I am trying to overcome currently. I am getting a Circular Reference Error prompt, error of which I can't find:-

    "Microsoft Excel cannot calculate a formula there is a circular reference in an open workbook, but the references that cause it cannot be listed for you."


    Will update you on this as well!


    Also, the WiseOwl Tutorials are just perfect, thank you!


    Take care!

    Learner.Here:saint: