I have a sheet containing this code and some other stuff too. But this section of code causes the macro to run slow. Is there any way to speed this up and make it run faster? Basically when a user enters something into one of the cells it will show the sheet associated with that cell. Hopefully there is a better way.
Please Login or Register to view this content.
So you want the macro to execute all of that when any cell in the sheet is changed? Most would just fire it for one cell or a range of cells. One uses Intersect() to limit execution in that case.
If that above is true, I suspect that you want to just reset sheets for that one cell changed. You can code it to each cell if in the Intersect() range.
To use the approach that I detailed, one would probably just want to execute a specific Case for one cell changed or Default (Case Else) to something else if that cell was in a range but not a specific Case. e.g.
Of course you can have as many "Case"s as you like.Please Login or Register to view this content.
Thanks for the reply Kenneth. I am not a VBA pro by any means, I'm still learning. Your comment reminded me of an important part I omitted from original posts code. I omitted parts to try and keep it clean and not take focus away from the problem area. The part that hides and unhides sheets is controlled by an intersect. If the users enter their initials in a cell within the range it enters the date in the column next to it, and also unhides the issue sheet that goes with it.
There is also another single cell that needs to run the macro every time the value changes. Cell B4 - the user enters the number of rows they need for data and the macro unhides only the rows needed.
The problem is every time anything is entered there is about a 3 second pause before the macro finishes and you are able to enter next cell value.
I don't need to run everything when any cell is changed but at the time I started it I didn't know any better. It worked fine until I added all the sheets to unhide.
Please Login or Register to view this content.
Last edited by jamfz; 08-24-2016 at 11:28 AM.
After your Toggle_Rows End If, you may want to try adding Application.EnableEvents=False. I like to set Application options before ON ERROR GOTO ENDNOW. I restore the options after EndNow:.
You could check the visible property value before setting. Multiple IF()s should make one consider using Select Case. Sometimes IF()s are required though.
Multiple IF()s can sometimes be reduced if one sees a pattern. In your case, one IF() is probably sufficient. The key is to use string concatenation. e.g.
Please Login or Register to view this content.
Last edited by Kenneth Hobson; 08-24-2016 at 12:30 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks