Problems re-loading a listbox after returning to a form

[tfurnivall]

I have a small system with three forms. Form can call Form2 (via a listbox) or Form 3 (via a command button); form 2 can call form 3(via a command button).

When I load the listbox in form1, and click on one of the entries, I pass that entry to form 2 for a more detailed display. Form 2 has an exit button which returns me to form 1 (via Me.hide)

The initial load of Form1 does what I want - loads values into the listbox, and when I clock on one, loads the entry into Form2. When I click the Exit button in Form2, I return to Form1, go through the load procedure, but the listbox contents do not display. They ARE being loaded, but are not visible (the control itself IS visible - just no data values).

I'm stumped - there must be a way to use multiple forms calling each other like this. How do I make it work?

Tony

============
\
The (very abstracted code) is like this:

sub form1_activate

LoadListBoxWith Values
me.show

end sub

sub form1.lstListBox_*******

Load Form2
ThisWorkbook LoadForm2Data Form1SelectedEntry
Form2.show

' Now we are returning from Form2

Unload Form2
me.show
end sub

sub form2.cmdExit_click

Me.hide

end sub

I can post the real code if that would be more useful, but there's several nundred lines over three forms and about three extra modules...

 

[Norie]

Tony

Sorry to go on abou it, but are you having trouble getting the code tags to work properly?

That code, well a bit of it anyway, should kind of look like this.

Option Explicit
 
Private Sub cmdSubmit_Click()
Dim ValidPassword As Boolean
 
    ValidPassword = SDLUtilities.ValidateUserID(Me.txtUserID, Me.txtOldPassword)
    If ValidPassword Then
        Me.Hide
        Load newfrmEventList
        ThisWorkbook.LoadEventList newfrmEventList
        newfrmEventList.Show
    End If
End Sub

One thing I did notice in the code is that you don't unload the form that you are opening the events form from.

No idea if it would make a difference, but perhaps worth checking out.

 

[tfurnivall]

I guess it makes a difference if use square brackets or angle brackets (a la HTML). Oh - that's why there's that convenient little Preview Posting button. I'm going to blame newbie status and incipient senility

At any rate, back to your question. I don't unload the calling form for two reasons (you can tell I haven't tried this yet, can't you).

First, I want to preserve the state of the selection criteria, which can get a little complex, and
Second, because I've got a 30+year history of writing procedural code, if I unload the form whence I'm entering the new form, and then try to go back, it's like Oakland - there's no there, there to go back to!

Having said that I'll give it a try right now......


...and, as I feared, VBA doesn't like it:

Run-time error '-2147418105 (80010007)':
Automation error
The callee (server[not server application]) is not available and disappeared;
all connections are invalid.The call may have executed.

This little gem appears right before I show that I'm back in the (unloaded) calling form.

So, I'll stick with keeping forms loaded until I'm pretty darn sure I don't need them for a bit!

I do have a trace of the code in operation:

First a snippet of the code in the lstEventSelect procedure:

Private Sub lstEventList_Click()

Dim ThisEntry As Integer
Dim ThisEventID As String

'   Configure the form to show a single event
Debug.Print Now(), Me.Name, "lstEventList_Click"

'   Get the data we need to load the selected event

ThisEntry = Me.lstEventList.ListIndex
ThisEventID = Me.lstEventList.List(ThisEntry, 0)

Load newfrmEvents
newfrmEvents.Caption = "Event Detail Data"

Debug.Print Now(), Me.Name, "lstEventList_Click", "Load Event into form for display"
ThisWorkbook.LoadEventIntoForm newfrmEvents, ThisEventID

Debug.Print Now(), Me.Name, "lstEventList_Click", "Ready to show newfrmEvents"
Me.Hide
newfrmEvents.Show
Unload newfrmEvents

'   The list will get reconstituted as part of the form activation
Debug.Print Now(), Me.Name, "Back from newfrmEvents", "EventID=", ThisEventID; " at entry "; ThisEntry
ThisWorkbook.LoadEventList Me
Debug.Print Now(), Me.Name, "Ready to show newly loaded list"
Me.Show

End Sub

and now the trace itself:

1/16/2012 9:53:27 AM        frmSecurity   Load newfrmEventList
1/16/2012 9:53:27 AM        enter LoadEventList
1/16/2012 9:53:27 AM        LoadEventList Consider event: E120001
1/16/2012 9:53:27 AM        LoadEventList Select event: E120001
1/16/2012 9:53:27 AM        LoadEventList Insert Event: E120001 into List slot: 0
1/16/2012 9:53:27 AM        exit LoadEventList
1/16/2012 9:53:27 AM        frmSecurity   Ready to show newfrmEventList
1/16/2012 9:53:27 AM        newfrmEventList             Activate
1/16/2012 9:53:31 AM        newfrmEventList             lstEventList_Click
1/16/2012 9:53:31 AM        newfrmEventList             lstEventList_Click          Load Event into form for display
1/16/2012 9:53:31 AM        LoadEventIntoForm                         E120001                     
1/16/2012 9:53:31 AM        LoadEventCallList                         E120001                     
1/16/2012 9:53:31 AM        CountCallsInEvent                         E120001        2            
1/16/2012 9:53:31 AM        LoadEventCallList                         CallCount:     2            
1/16/2012 9:53:31 AM        LoadEventCallList                         C120001                     
1/16/2012 9:53:31 AM        LoadEventCallList                         C120002                     
1/16/2012 9:53:31 AM        LoadEventCallList                         Repaint Form                E120001
1/16/2012 9:53:31 AM        newfrmEventList             lstEventList_Click          Ready to show newfrmEvents
1/16/2012 9:53:31 AM        newfrmEvents  Activate
1/16/2012 9:53:34 AM        newfrmEvents  cmdExit_Click Heading back to newfrmEventList?
1/16/2012 9:53:34 AM        newfrmEventList             Back from newfrmEvents      EventID=      E120001 at entry  0 
1/16/2012 9:53:34 AM        enter LoadEventList
1/16/2012 9:53:34 AM        LoadEventList Consider event: E120001
1/16/2012 9:53:34 AM        LoadEventList Select event: E120001
1/16/2012 9:53:34 AM        LoadEventList Insert Event: E120001 into List slot: 0
1/16/2012 9:53:34 AM        exit LoadEventList
1/16/2012 9:53:34 AM        newfrmEventList             Ready to show newly loaded list
1/16/2012 9:53:34 AM        newfrmEventList             Activate

<code>

Hmmm. One thing I do notice is that there is an activate event right after I call me.show

I can't find any sufficiently detailed (yet comprehensible) documentation of the sequence of events firing, and what exactly each one does...

Not happy - but glad I learned to use <code tags>

Tony
</code>

 

[Norie]

Tony

That activate might be the problem.

When you show a form the activate event happens.

What's in the activate event?

Is it the code to 'initialise' the form?

If it is perhaps you could put it in the form's Initialize event.

 

[tfurnivall]

Nothing actually happens there, except the trace:

Private Sub UserForm_Activate()

Debug.Print Now(), Me.Name, "Activate"

End Sub

I'll try removing it and see if somehow something happens miraculously....

..except it doesn't
The only thing we lose is the ability to see that the event has actually fired. What intrigued me was the idea that the form was committed to being shown and as part of that process it activates itself. Of course, it was hidden before we went off to play with the Event Record, so it does need to do something to wake itself up.

Summary of present status is that if I don't reload the list I can still see the old list, but I can't work with the selection buttons. If I do load the list I can't see the contents (and I still can't work with the selection buttons.

I'm running out of ideas...

T

 

[Norie]

Did you try the Initialize event?

What I was suggesting was to move the code from the Activate event to that event.

The Initialize event is fired when the form is opened/loaded for the first time, it won't get activate again until the form is opened again.

 

[tfurnivall]

I set up a similar (but smaller) system and I think I can more clearly define the situation:
After returning from Form2 in response to a Listbox_Click event, the listbox is unable to accept any more click events until something magic happens.

Sub DebugListStatus(Optional Comment As String = "============")

Dim i As Integer

Debug.Print Now(), Me.Name, "<" + Comment + ">"
Debug.Print Now(), "ListBox status"
Debug.Print Now(), , "Visible=" + Format(Me.ListBox1.Visible)
Debug.Print Now(), , , "Top=" + Format(Me.ListBox1.Top), "Height=" + Format(Me.ListBox1.Height)
Debug.Print Now(), , , "Left=" + Format(Me.ListBox1.Left), "Width=" + Format(Me.ListBox1.Width)

Debug.Print Now(), , "Enabled=" + Format(Me.ListBox1.Enabled)
Debug.Print Now(), , "ListCount=" + Format(Me.ListBox1.ListCount)
Debug.Print Now(), , "ListIndex=" + Format(Me.ListBox1.ListIndex)

For i = 1 To Me.ListBox1.ListCount
    Debug.Print Now(), , , Format(i - 1), Me.ListBox1.List(i - 1, 0), Me.ListBox1.List(i - 1, 1)
Next i

End Sub

Sub LoadEntries(FirstRow As Integer)
FirstRow = 1
Me.ListBox1.AddItem "A" + Format(FirstRow)
Me.ListBox1.List(0, 1) = ActiveSheet.Range("A" + Format(FirstRow))
FirstRow = FirstRow + 1
Me.ListBox1.AddItem "A" + Format(FirstRow)
Me.ListBox1.List(1, 1) = ActiveSheet.Range("A" + Format(FirstRow))
FirstRow = FirstRow + 1
Me.ListBox1.AddItem "A" + Format(FirstRow)
Me.ListBox1.List(2, 1) = ActiveSheet.Range("A" + Format(FirstRow))
DebugListStatus ("After Loading Data")
End Sub

Private Sub ListBox1_Click()
Dim i As Integer

i = Me.ListBox1.ListIndex
DebugListStatus ("Start of Listbox1_Click")
Load Form2
If i > -1 Then
   Form2.TextBox1 = Me.ListBox1.List(i, 0)
   Form2.TextBox2 = Me.ListBox1.List(i, 1)
Else
   MsgBox "Nothing to select!"
End If
DebugListStatus ("Ready to hide Form1")
Me.Hide
DebugListStatus ("Ready to show Form2")
Form2.Show
DebugListStatus ("After return from Form2")
Unload Form2
DebugListStatus ("After unloading form2")

End Sub

Private Sub UserForm_Initialize()
StartPoint=1
LoadEntries (StartPoint)
End Sub

As you can see, I've put a breakpoint at the head of the ListBox_Click handler. It fires the first time, but doesn't fire again.
Here is the trace when I run it:

1/16/2012 11:50:24 AM       Form1         <after loading="" data="">
1/16/2012 11:50:24 AM       ListBox status
1/16/2012 11:50:24 AM                     Visible=True
1/16/2012 11:50:24 AM                                   Top=24        Height=168.75
1/16/2012 11:50:24 AM                                   Left=24       Width=390
1/16/2012 11:50:24 AM                     Enabled=True
1/16/2012 11:50:24 AM                     ListCount=3
1/16/2012 11:50:24 AM                     ListIndex=-1
1/16/2012 11:50:24 AM                                   0             A1            Baseline list data
1/16/2012 11:50:24 AM                                   1             A2            Entry 2
1/16/2012 11:50:24 AM                                   2             A3            This is the 3rd entry
1/16/2012 11:50:26 AM       Form1         <start of="" listbox1_click="">
1/16/2012 11:50:26 AM       ListBox status
1/16/2012 11:50:26 AM                     Visible=True
1/16/2012 11:50:26 AM                                   Top=24        Height=168.75
1/16/2012 11:50:26 AM                                   Left=24       Width=390
1/16/2012 11:50:26 AM                     Enabled=True
1/16/2012 11:50:26 AM                     ListCount=3
1/16/2012 11:50:26 AM                     ListIndex=1
1/16/2012 11:50:26 AM                                   0             A1            Baseline list data
1/16/2012 11:50:26 AM                                   1             A2            Entry 2
1/16/2012 11:50:26 AM                                   2             A3            This is the 3rd entry
1/16/2012 11:50:26 AM       Form1         <ready to="" hide="" form1="">
1/16/2012 11:50:26 AM       ListBox status
1/16/2012 11:50:26 AM                     Visible=True
1/16/2012 11:50:26 AM                                   Top=24        Height=168.75
1/16/2012 11:50:26 AM                                   Left=24       Width=390
1/16/2012 11:50:26 AM                     Enabled=True
1/16/2012 11:50:26 AM                     ListCount=3
1/16/2012 11:50:26 AM                     ListIndex=1
1/16/2012 11:50:26 AM                                   0             A1            Baseline list data
1/16/2012 11:50:26 AM                                   1             A2            Entry 2
1/16/2012 11:50:26 AM                                   2             A3            This is the 3rd entry
1/16/2012 11:50:26 AM       Form1         <ready to="" show="" form2="">
1/16/2012 11:50:26 AM       ListBox status
1/16/2012 11:50:26 AM                     Visible=True
1/16/2012 11:50:26 AM                                   Top=24        Height=168.75
1/16/2012 11:50:26 AM                                   Left=24       Width=390
1/16/2012 11:50:26 AM                     Enabled=True
1/16/2012 11:50:26 AM                     ListCount=3
1/16/2012 11:50:26 AM                     ListIndex=1
1/16/2012 11:50:26 AM                                   0             A1            Baseline list data
1/16/2012 11:50:26 AM                                   1             A2            Entry 2
1/16/2012 11:50:26 AM                                   2             A3            This is the 3rd entry

So.....

(i) I need to find a way to reactivate the listbox (the form is being reactivated just fine - but not the listbox.
(ii) The data in the listbox remains invariate on return from form2, and indeed the visual appearance of the listbox matches the mouse clicks (lines become selected and unselected). Just that nothing happens
(ii) I need to understand exactly what the sequence of events (pun intended) is when hiding one form and returning to another.

Tony</ready></ready></start></after>

 

[Norie]

Could it be the order you are unloading showing?

You show the original userform then you unload the other userform.

When you show the original form program control passes to it, so the unload code is never executed.

I think.

 

[RoryA]

In your listbox_click code you never reshow the form as far as I can see.