View original (French)

Dropdown list in a cell not showing

Alexsimps Posted messages 215 Status Member -  
redaiwa Posted messages 370 Status Member -
Hello everyone,

I have a little problem that is bothering me somewhat...

I am creating data validation lists in several specific cells via a VBA macro to enable assisted data entry for a row in the Excel sheet (the other rows are locked). The first time I call it, there is no problem, everything works well. I validate my row with a button that then re-locks the cells (and removes the validation list).

When I try to recreate a row (and thus call my validation list creation), the buttons only generate after a random delay (several minutes). Between creation and display, I can navigate through my cells and attempt entries that are rejected because they are not part of the allowed data list! This means that the validation list is present, but the drop-down containing it does not appear...

Does anyone have an idea?

I tried the workbook's refresh.all, activating the sheet, nothing works.

This problem occurs on all sheets of my workbook (over 90 tabs)...

Thank you in advance.

Alexsimps.

Configuration: Windows XP / Chrome 38.0.2125.111

1 answer

  1.
    redaiwa Posted messages 370 Status Member 119
     
    Hello Alexsimps.

    Apparently, the size of your file is a bit heavy, which can cause slowdowns in macro execution. I found this online, it might be useful:

    If when you run a macro that automatically makes changes to cells, you notice a strange slowness, it is likely because your workbook contains many formulas, and every time any cell changes, Excel recalculates everything. Therefore, it is wise to place the instruction to disable automatic recalculation before any macro:
    Application.Calculation = xlCalculationManual
    Make sure to set it back to automatic just before the end of the macro:
    Application.Calculation = xlCalculationAutomatic

    Also, for speed reasons, it is advisable to disable screen updating before the macro, and reactivate it afterward:
    Application.ScreenUpdating = False
    ' Do plenty of things that affect the content of the cells
    Application.ScreenUpdating = True

    :)

    "Repel evil with good; and behold, he with whom you had animosity becomes like a warm friend" (Quran)
    "He who does not know how to share is crippled in his emotions." (Marc Levy)
    0