Excel VBA Form is not refreshing properly when a subform is closed

Question

Good Afternoon. I've running into a situation I hope someone here can help with. I'm running Office 2010 on Windows XP and have an Excel worksheet that contains a button to show a modal form (Form01). Form01 contains 3 Listboxes of data. Double-Clicking a item in listbox2 will open another modal form (Form02) so the item can be modified. Unloading Form02 will save the data and call a couple of macros to adjust a named range on the host worksheet. This code is stored in a Module, not on the form.

This is where the problem occurs. When Form02 is unloaded and Form01 is accessable, I cannot select anything in listbox1 or listbox3 or buttons on Form01. I have to first select something in listbox2, then I have access to the other controls on the form. I've tried to .SetFocus on the other controls and add DoEvents after the Form02.Show 1 statement with no luck. The only workaround I've found is to hide and reshow Form01 which causes the screen to flicker. Application.ScreenUpdating = False and back to True doesn't seem to help either. I really need to figure out what the other controls are not accessible when Form02 is shown and then closed. Has anyone else experienced this behaviour or might have a suggestion?

I tested and tried it (xl2010 + Win7) and was unable to reproduce the issue. So my best guess (since you have not shown any code) is based on what you said `Unloading Form02 will save the data and call a couple of macros to adjust a named range on the host worksheet.` Is it possible to see your workbook? — Siddharth Rout, Mar 22 '13 at 19:01
Check to see if any of the other form events (on *either* form) have set `Form01.ListBox1.Enabled = False` or `Form01.Listbox3.Enabled = False`. You could also debug in the immediate window with `?Form01.Listbox1.Enabled` and that should display True or False. If this is the case, it's simple to re-enable them.. — David Zemens, Mar 22 '13 at 19:01
@Siddharth. I do have a sample file I would like to provide. My apologizes, I'm new to this forum and don't know how to get an attachment loaded and don't have a personal website to post it on. Any suggestions on how to post this sample file? — Terry Clark, Mar 22 '13 at 20:20
@user2200422: You can upload it to say www.wikisend.com or any other file sharing site and then share the link here :) — Siddharth Rout, Mar 22 '13 at 20:21
@Siddharth: I've placed a copy of the Excel file at this location. Please let me know if you have any questions. https://www.dropbox.com/s/uq0i3b0z92j7hqj/MyFile.xlsb — Terry Clark, Mar 22 '13 at 21:06

score 0 · Answer 1 · answered Mar 23 '13 at 05:16

Change this

Private Sub lstSiteMaster_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If lstSiteMaster.ListCount = 0 Then Exit Sub
    LoadFrmEditDataset Me.Controls("lstSiteMaster"), "SITE MASTER"
    frmEditDataset.Show 1
    Unload frmEditDataset
    DoEvents
    Me.lstSiteMaster.ListIndex = -1
    Me.lstSiteList.ListIndex = -1
    Me.lstMiniPOR.ListIndex = -1
    '''BounceTheForm
End Sub

to

Private Sub lstSiteMaster_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If lstSiteMaster.ListCount = 0 Then Exit Sub
    LoadFrmEditDataset Me.Controls("lstSiteMaster"), "SITE MASTER"
    frmEditDataset.Show 1
    DoEvents
    Me.lstSiteMaster.ListIndex = -1
    Me.lstSiteList.ListIndex = -1
    Me.lstMiniPOR.ListIndex = -1
    '''BounceTheForm
End Sub

AND

this

Private Sub cmdApply_Click()
    With Me
        UpdateDataset .lblDataset, .lblECR, .lblFA, .lblFieldId, .txtFieldValue, .cboAction, .lblIndex + 2
    End With

    Me.Hide
End Sub

to

Private Sub cmdApply_Click()
    With Me
        UpdateDataset .lblDataset, .lblECR, .lblFA, .lblFieldId, .txtFieldValue, .cboAction, .lblIndex + 2
    End With

    Unload Me
End Sub

Excel VBA Form is not refreshing properly when a subform is closed

1 Answers1