+ Reply to Thread
Results 1 to 25 of 25

Workbook with Form Controls has become REALLY slow

  1. #1
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Workbook with Form Controls has become REALLY slow

    Hi, I am brand new to this forum, driven here by frustration! I use several different workbooks for my business but only one of them has form controls; some columns have tick boxes and others have dropdown menus of about 3-4 choices. I have a tab for each month so 12 in total and each of them has 28 columns, with half this number having controls in them. Rows are somewhere between 30-40 each month. Document size is about 5mb. I'm giving you all this info because I don't know what part might be relevant. Basically it is not a huge file. It has been working fine for nearly 2 years but suddenly I have a problem where every entry or change that I make, be it in a control box or not, it hangs for anywhere between 20 seconds and 2-3 minutes. It doesn't crash but just takes ages. My other Excel files are fine and unaffected but every tab on this workbook has the same issue. I have looked at AddIns in safe mode and none of them are ticked (should they be?). It is autosaved to OneDrive but I have tried turning autosave off and no difference. This is driving me mad now and taking up most of my day either using it and being patient or spending time trying to find an answer. Hopefully someone can help me.

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    upload a copy of your file - anonymise data but keep structure - see big yellow banner - how to upload your workbook.
    lot easier then to give an in-context solution, rather than multiple guessing.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  3. #3
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    Thanks Torochan, I have uploaded it.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    I'm pretty sure that the problems started when I inserted extra rows (24-28). I often do this without it causing any problems but I have tried deleting these and the problem remains. I have also seperated this NOV tab from the rest of the workbook but all tabs act the same way as this NOV one with it in there or not. Also I went to OneDrive to download past versions of this file (from a time when there were no issues) but I still get these delays.

  5. #5
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    cursor in 'A41' - select 'Ctrl' + 'End' - does this go to 'AJ142' or somewhere down the sheet - if it does.
    Try cursor back in 'A41' now a four key press 'Ctrl' + 'Shift' + 'DownArrow' + 'RightArrow'
    now select 'Home' menu - select 'Delete' - select 'Delete Sheet Rows'
    with cursor back in 'A3' - 'Ctrl' + 'End' should not go below row 41 - could not figure out what was in all the blank cells, but possibly left overs from previous data ?????
    However why not use 'Data Validation' and 'structured tables' to eliminate the formatting of all those 'checkboxes'
    'structure tables' expand/contract dynamically as you add/delete data rows (so does the formula, formatting and validation)
    try the attached approach - a small macro in the sheet code module give you your tick via the 'data validation' choice - the data validation only expands in the table not the sheet range as you enter something in column 'A' on the immediate row under the table.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    That's awesome thanks so much. It works fine on the sample doc I uploaded so I am now in my actual workbook following the same formula for each of the 12 sheets as they all have the same number of blank cells. So it is still slow right now but I'm guessing when I have done them all it will be OK. You actually answered my next question as I presumed that I had not created this worksheet in the most effective way. It seemed that there must be a better way than to laboriously add rows and edit the attributes. I will follow your advice. Do I have to create a new worksheet from scratch or can I convert my existing one keeping any of the content?
    Thanks again for your help - really appreciate it.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Workbook with Form Controls has become REALLY slow

    … can I convert my existing one keeping any of the content?
    Select the data (including headings) and then, on the Insert tab, select Table. Accept the options, assuming they cover the correct range.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    on the second sheet I have put the bare minimum needed to start you off.
    I have copied just the top of your page (rows 1 to 3) - then selected range ("A3:AB4") - insert table - headers yes.
    now on row 4 place the different data validations - I have not done all of them - just sufficient to guide you - I have changed the range for the tick - you can format this larger by altering the macro.
    if you want individual sheets there will have to be a macro on each sheet (easier at this stage than single module and carrying variables)
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    that's really kind of you, thank you. I'm having fun playing around with it. On the tick lists I'm getting the tick or cross come up but am also getting an error warning come up.
    It says Data Type: List
    Restriction: Value must match one of the listed items.
    Any idea why this is happening?

  10. #10
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    ignore the error warning - it is expecting 'YES' or 'NO' - the VBA macro is intercepting this and placing a 'WingDings' symbol in its place.
    the error warning should only show its contents if you click on it.

  11. #11
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    OK. I'll try and leave you alone now, but before I go could you tell me these things:

    Where are the drop down options stored? If I wanted to change or add new options how do I do that?

    How do I format a cell to include the drop down menu? The timesheet column has dd menus but the other 5 columns don't have so I need to add them in.

    Can I have a 'Total' row at the bottom? I would still need to be able to add in new rows as required so the total row would have to keep floating downwards.

    Btw, in the end on my problem file it didn't make much difference deleting all those empty cells. It was quicker at first but still with lag, and then it seemed the more I altered the sheet the slower it got.. a couple of minutes for each action. But I prefer the sheet you have created so I will just use this one going forward.

  12. #12
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    1) go to 'P4' - in upper menu - select 'Data' - select 'Data Validation' - in dropdown 'Settings' - you select 'List' - then in source type in defaults separated by a comma.
    do the same in the missing columns - you only need to do this in the first row (the beauty of 'structured tables' they take care of the expansion)
    to have 'total row' in upper menu select design and then checkbox 'total row' this places a row at the bottom of the table that moves down as you insert rows (you will see dropdowns in this new row that enable you to select functions).
    as there is validation in column 'A' you may have to put an extra column in at the start to make processing more manageable.
    with any sort of form controls you generally find some slowing of actions (that is because in effect you have added a graphics layer to the sheet that adds to the overhead)

  13. #13
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    This is genius. You're a genius! Thanks so much for all your help.

  14. #14
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    Sorry Torachan, have got in a mess with the tick boxes as I tried entering other values. I get how it works with the Data Validation but I just want to have a drop-down list of Yes and No and to assign the Wingdings2 tick and cross to those answers. I can get the tick and the cross to appear but the dropdown menu says P and O - their relevant letters within Wingdings2. How do I get the YES and NO to appear, but once selected they should give a symbol in the cell? You had it set up this way but I have screwed it up. Everything else is great though and so much nicer to work with now

  15. #15
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    open up the sheet code module - you will see the VBA that intercepts the YES/NO that the data validation uses and the code converts that to the relevant 'WingDings'
    if you have not got the VBA Developer in your upper menu you can open it by right clicking the sheet tab and selecting 'View Code' from the dropdown menu.
    if all the sheets are the same layout just copy/paste the code into each sheet code module and alter any references that call the name of the sheet or possibly just reference 'ActiveSheet'

  16. #16
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    Hi Torochan,

    Hoping this is the last time I have to call on you….I have copied the sheet I set up for my November billing to create a similar sheet for December. Some of the data in November needs to be kept in it for December (and for any future monthly sheets that I create) so I have deleted the rows that I don’t need. All good there. The issue I’m faced with is that when I make any entry at all to the December sheet I get the message Run-Time error ‘1004’ Method ‘Intersect’ of Object’_Global’ failed…
    To avoid having to ‘end’ this message each time what do I need to change in the VB Debug screen?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Sheet1.ListObjects(1).Range.Columns("D:AA")) Is Nothing Then
    If Target = "YES" Then
    With Target.Font
    .Name = "wingdings 2"
    .Color = vbBlack
    End With
    Target = "P"
    End If
    If Target = "NO" Then
    With Target.Font
    .Name = "wingdings 2"
    .Color = vbBlack
    End With
    Target = "O"
    End If
    Else
    With ActiveCell.Font
    .Name = "Calibri"
    .Color = vbBlack
    End With
    End If

    End Sub

    Thanks in advance!

  17. #17
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    in the code line below - with the VBA editor open from your new sheet - look for Sheet name (note this is NOT the sheet tab name) ensuring you are in the new sheet code module change 'Sheet1' to whatever the new sheet name is.

    Please Login or Register  to view this content.
    please also look up forum rule (placing code between code tags) as per this post - (before moderators slap your knuckles)

  18. #18
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    That's great, thank you it works fine now. And thanks for the heads-up about code-tags!

  19. #19
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    Hey Torachan,
    Hope you're good.
    I am having a few problems when I copy a sheet... I did what you told me before and in the VB debug screen I just change the sheet number which is good as I don't get any error message up when I make an entry. The thing is that on my tick boxes, although nothing has changed in the debug screen (wingdings 2 is still the chosen font) it's bringing up different symbols instead of a cross or tick. Do you know what I would need to do to fix this? Also, for some of those boxes I'd like to change the tick/cross option for some text choices. Could you remind me how to do that? Many thanks in advance.

  20. #20
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    upload your latest file - I have replaced my old computer and archived all my old files over the Christmas period and it will be simpler for me to look at what you have to date.

  21. #21
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    OK here it is columns P through U that I'm struggling with. Dropdowns with text seem to be OK but not with Wingdings 2
    Attached Files Attached Files

  22. #22
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    In your attached file all I have done is altered the code in the module to refer to the correct (new) sheet - regarding altering from wingdings to text with cursor on relative cell go to 'Data' > 'DataValidation' > entere in Source your text seperated by comma - do not use 'YES' or 'NO' as that will trigger wingdings in the code module.
    Also with 'structured tables' do not extend with blank rows - just allow the table to extend dynamically with the data entry in the first column.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    Thank you, but the tick boxes are still showing me the same incorrect symbols. Could you tell me what I should enter in the code to make it work? You told me before about changing the sheet number and this worked for most amendments that I make to the worksheet but I still have the symbol issue so am wondering what else needs to be changed.

    And how do I create a new line without inserting one manually (which is what I have been doing)? Should a new line pop up as soon as I enter something in the empty line?

    It's telling me that macros have been blocked by MS as a security measure and it advises against unblocking them which I guess is sensible advice. Not sure if these macros being blocked are the reason for my above issues. Obviously on my original workbook macros are enabled.

  24. #24
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Workbook with Form Controls has become REALLY slow

    Yes, macros have to be enabled for code to work that intercepts the YES/NO and converts this to 'tick' or 'X'.
    With a table totaliser row yes you have to insert a new row - without the totaliser row a 'structured table' will expand dynamically by entering a value in the row immediately under the table.

  25. #25
    Registered User
    Join Date
    11-22-2022
    Location
    Dorking, UK
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Workbook with Form Controls has become REALLY slow

    Fabulous thank you!

+ 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. Form Controls and ActiveX Controls - Option Button
    By mmchaley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2021, 05:10 PM
  2. Creating alot of controls at runtime is too slow!!
    By gbeats101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2016, 01:12 AM
  3. Form Controls v. ActiveX Controls
    By Richard Stringfellow in forum Excel General
    Replies: 2
    Last Post: 01-11-2016, 08:06 PM
  4. Form Controls / Active X controls to record and log information in a seperate sheet
    By marcbarnett in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-09-2014, 05:00 AM
  5. Replies: 1
    Last Post: 05-01-2014, 05:15 PM
  6. Form controls vs. ActiveX controls
    By wmorrison49 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2008, 10:59 AM
  7. ActiveX Controls vs Form Controls
    By Alex in forum Excel General
    Replies: 1
    Last Post: 01-11-2006, 04:50 AM

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