none
Excel User Form Caption RRS feed

  • Question

  • Hi, Experts.

    1. Currently I am writing a new project, in which I process some values in excel, and display them in user form.

    2. Suppose we have Range from A1 to A10. When A1 calculation (simple addition of values) get over, its final value gets added in listbox.

    3. This loops goes on till Range A10.

    4. While this process is going on, frame in which this listbox is present, gets caption name changed to "Under process.".

    5. This code runs fine in debug mode, when I step in to code.

    6. But when I run this code (F5).

    a. Frames caption doesn't changes to "Under process"

    b. Items in list box are not getting added sequntially i.e first A1 value, then A2 value and so on, instead listbox shows items, when all A1 to A2 cells processing gets over.

    7. Why this is happening? Should I make it more slow, should I add few pauses, or change properties, of control. Kindly assist, what I am missing ?

    With Regards, Ajay Check

    Wednesday, May 9, 2012 8:06 AM

Answers

  • If you edit or format a worksheet in your code, try to avoid selecting cells where possible. The lines

    Worksheets("Sheet A").Select
    Range("A1:A3").Select
    Selection.Copy
    Worksheets("Sheet B").Select
    Range("C1").Select
    Selection.Paste

    can be replaced with the single line

    Worksheets("Sheet A").Range("A1:A3").Copy Destination:=Worksheets("Sheet B").Range("C1")

    And you can speed up execution by turning off screen updating:

    Application.ScreenUpdating = False
    ...
    ...
    ...
    Application.ScreenUpdating = True


    Regards, Hans Vogelaar

    • Marked as answer by Ajay Check Friday, May 11, 2012 7:47 AM
    Wednesday, May 9, 2012 3:04 PM

All replies

  • In code behind the userform, you can use

        Me.Repaint

    to force the userform to update its display. If you run the code from another module, use the following, where UserForm1 is the name of the userform:

        UserForm1.Repaint

    You can do this after setting the userform's caption to make sure that it is displayed.

    If desired, you can also insert the above line inside the loop, but that will slow down execution.


    Regards, Hans Vogelaar

    Wednesday, May 9, 2012 8:21 AM
  • Another way is doevents. This forces the VBA process to relinquish control to windows for a time slice so other processes can continue or complete their work. This can be very useful for inter-process code. However in this case Hans' repaint is simplest.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, May 9, 2012 9:03 AM
  • Hi, Hans Vogelaar & Rod Gill

    It worked, Thanks a lot.

    Doubt :Just like

    For

    userform - repaint, inter-process - do events, set object = nothing (at last)

    Are there any other memory management related methods or techniques, on which I have to pay attention, while dealing with large amount of data.?

    With Regards : Ajay Check

    Wednesday, May 9, 2012 2:52 PM
  • If you edit or format a worksheet in your code, try to avoid selecting cells where possible. The lines

    Worksheets("Sheet A").Select
    Range("A1:A3").Select
    Selection.Copy
    Worksheets("Sheet B").Select
    Range("C1").Select
    Selection.Paste

    can be replaced with the single line

    Worksheets("Sheet A").Range("A1:A3").Copy Destination:=Worksheets("Sheet B").Range("C1")

    And you can speed up execution by turning off screen updating:

    Application.ScreenUpdating = False
    ...
    ...
    ...
    Application.ScreenUpdating = True


    Regards, Hans Vogelaar

    • Marked as answer by Ajay Check Friday, May 11, 2012 7:47 AM
    Wednesday, May 9, 2012 3:04 PM