+ Reply to Thread
Results 1 to 38 of 38

VBA Stopped Working

  1. #1
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    VBA Stopped Working

    Hi Guys,

    Been happily coding away with my VBA code when it errored out saying was too large,
    Have done a google search, found that my code was too large as 1 and needed to be separated into segments.
    I've separated the coding, but now this is not running/doing as intended,
    For some reason it's not doing nothing at all?

    Please can I have help in what I have done wrong?

    Many thanks.
    Attached Files Attached Files
    Last edited by JaSonic75; 03-31-2018 at 04:15 AM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    Confusing....

    What are
    Private Sub Worksheet_SelectionChange_DI_75T(ByVal Target As Range)
    Private Sub Worksheet_SelectionChange_DI_35T(ByVal Target As Range)
    Private Sub Worksheet_SelectionChange_DI_Mate(ByVal Target As Range)

    ?

  3. #3
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    I've tried separating into segments as failed as 1 long script, they are basically title names.
    I assume that is wrong lol ?

    many thanks
    Last edited by AliGW; 04-05-2018 at 02:03 AM. Reason: Unnecessary quotation removed.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    So, you want the whole code into one Selection_Change event?

    No, you can't do it so...
    Perhaps...
    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    Correction
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Many thanks, this seems to be working.

    However the numbers are a little over my head lol, (\ 2 - 4, \ 2 - 7)

    I have now added a new sheet (sheet9) with similar data to that of sheet8,

    Please may I kindly ask how I add that sheet to the equation?

    Many thanks
    Last edited by AliGW; 04-05-2018 at 02:03 AM. Reason: Unnecessary quotation removed.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    You have clear regularity between columns to loop and columns to hide.
    As your loop column index increases by 2 (A, C, E, G etc.), hide column increases by 58 columns.

    Backslash operator in calculation gives you round down integer.
    e.g
    5 \ 2 = 2, which you can also rewrite like Application.RoundDown(5/ 2, 0), or Fix(5 / 2)

    So, as loop column increases by 2, the result from that calculation will increase by 1.

    -4, -7 in calculation are the adjustment for the hide column index.

    I added the variable "myAdj", so you can do a step debug.
    Please Login or Register  to view this content.
    HTH

  8. #8
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    This was working fine, I've not got round to trying to edit for Sheet9 yet, but all of a sudden, this script has started erroring out?
    error.jpg

    When I press the 'DEBUG' button, I get this script but with part of it highlighted?
    Please Login or Register  to view this content.
    Any reason that this would start erroring out please?

    Many thanks
    Last edited by AliGW; 04-05-2018 at 02:03 AM. Reason: Unnecessary quotation removed.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    It runs fine here.
    Is any sheet protected?

  10. #10
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Aah.....
    They are now.

    Certain cells have been unprotected accordingly but others, like what this script refers to, are protected from editing.

    I've unprotected the sheet and the script now behaves.

    I assume that now throws a spanner in the works?

    Many thanks
    Last edited by AliGW; 04-05-2018 at 02:04 AM. Reason: Unnecessary quotation removed.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    If protected, add 2 lines
    Please Login or Register  to view this content.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,753

    Re: VBA Stopped Working

    JaSonic - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  13. #13
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Please Login or Register  to view this content.
    Still comes up with same error

    Many thanks

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    OK, try this one then
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Sorry,

    Now comes up with new error:
    Compile error
    For each control variable on arrays must be a variant

    Not sure what that means.

    Top line highlights in green
    ws highlights in blue

    Please Login or Register  to view this content.
    Many thanks
    Last edited by AliGW; 04-05-2018 at 02:58 AM. Reason: Illegible colour changed.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    OOps,of course...
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    I'm sorry,

    This is now working

    However,
    When selecting a cell, it now jumps to the selected worksheet and asks for the password.
    If I protect the sheet without a password, the screen flicks to the corresponding sheet then back again briefly.

    Many thanks

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    If only one password for all the sheet

    change to
    Please Login or Register  to view this content.
    Change password to suite

  19. #19
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Awesome, that solves the asking for password issue.

    However when selecting a cell, the screen is flickering to and from the worksheet accordingly
    If for example I press down arrow to move down 5 cells, the screen flicks to and from 5 times.

    Is there a way to stop this please?

    Many thanks

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    Then add 2 lines
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Cool, getting there

    Your help is really appreciated!

    Now when selecting cells, the sheet has to think for a second or 2 before I can do anything.

    Resolvable?

    Many thanks

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    I don't think so.
    If they are the rows to be hidden, autofilter could be applied.

    However, it can be narrowed the range of Selection.
    The current code will run for each one of the cell regardless of the cell selected, but if it limit to the change within the fixed range, it can be much faster.

  23. #23
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    I've found that if I leave the password blank to protect the sheet, it works so much faster and more acceptable.

    Many thanks for your assistance Jindon, very much appreciated
    +1 to your reputation

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    You are welcome and thanks for the rep.

  25. #25
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Sorry,
    Think I'm being dumb

    Can't get sheet9 to work

    Please can you tell me where I going wrong:
    Please Login or Register  to view this content.
    Many thanks

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    Hi
    Your range is up to col.S, which is column index of 19.
    Please Login or Register  to view this content.
    So it will never go in to Case Else
    Please Login or Register  to view this content.

  27. #27
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Even that took a moment to register an understanding lol.
    I was looking at it referring to sheet9 being the problem, not the source list on the main sheet in question.

    I have now edited the code accordingly and believe I now have it fully functional:
    Please Login or Register  to view this content.
    Beginning to make more sense.

    Thankyou Thankyou Thankyou

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    OK, one thing though.

    Subscript of Range must be less than 255 characters.
    Please Login or Register  to view this content.
    If you ever need to extend the range and exceed the limit, use Union method like
    Please Login or Register  to view this content.

  29. #29
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    That makes sense,

    In my case, I've split it up into categories, so to make things more simplified, I've applied the ranges into those categories:
    Please Login or Register  to view this content.
    Many thanks

  30. #30
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Something strange has happened

    This script has been working perfectly until all of a sudden recently

    All of a sudden I'm getting the following error and one of the lines when I go into debug highlights yellow:

    error.jpg

    I have confirmed the sheet is unprotected.

    Please Login or Register  to view this content.
    Any ideas please ?

    Many thanks

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    Can not replicate the issue,

    If the cell value possibly have Error then change to
    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Hmm,

    This has really baffled me...

    I was trying to delete parts of the spreadsheet so I could send the file to here for you to see, (still not succeeded in that lol), when I went though each sheet and deleted the comments.

    Once all comments were deleted, I noticed the script then working again, behaving as designed.

    After a bit of digging, I found that there is a comment on Sheet5 Cell GB18, which all it says is 'at Alsbury', delete this comment on its own, (leaving all the others in place), and the script is working.

    Strange thing is, if I delete the comment and re-insert it (even with using copy/paste), the script carries on working.

    The additional lines in the script above did not solve the problem unfortunately.

  33. #33
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    Still can not replicate the issue here.

  34. #34
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Have deleted as much as I can with error in place

    File attached.

    Many thanks.
    Attached Files Attached Files

  35. #35
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    See if this works,
    Please Login or Register  to view this content.

  36. #36
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    For some reason, this is taking ages when clicking in a different cell
    Circle busy icon goes round for quite some time before the cell becomes usable.

    Not so bad on the file attached here, but in the original, is taking 30-60secs.

    many thanks

  37. #37
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: VBA Stopped Working

    Try
    1) Replace the code with the one I posted in post #31
    2) add the following code onto a standard module
    Please Login or Register  to view this content.
    3) add following code onto "Setup" sheet code module
    Please Login or Register  to view this content.
    4) Select other sheet than "Setup" just once and select back "Setup" sheet.

    Edit: code
    Last edited by jindon; 05-07-2018 at 06:51 AM.

  38. #38
    Registered User
    Join Date
    07-01-2017
    Location
    UK
    MS-Off Ver
    365
    Posts
    80

    Re: VBA Stopped Working

    Cool, That's working

    When returning to Setup sheet, I see something going on in bottom left of screen but can't make out what its doing lol.

    Fault has now gone and script is working again.

    Many thanks for your assistance, Very much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-30-2017, 02:32 AM
  2. [SOLVED] VBA stopped working again
    By tsakta13ole in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-09-2015, 10:14 AM
  3. [SOLVED] VBA stopped working
    By tsakta13ole in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2015, 06:17 AM
  4. ocde working in one windows login but stopped working in another.
    By rama1209 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2011, 08:38 AM
  5. VBA stopped working!!
    By maxthebear in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2009, 09:51 PM
  6. VBA Stopped Working!
    By SamuelT in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-16-2007, 11:12 AM
  7. It's stopped working
    By mikhailia in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 01-22-2007, 10:12 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1