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...

 

[diddi]

hi and welcome to the board. why do you need to reload the listbox? does form2 affect the values in the listbox? otherwise there should not be any need.

 

[Norie]

Tony

You shouldn't need to reload the listbox.

All you've done is hide the userform, not unload it.

 

[tfurnivall]

Thanks, Norie and Diddi. IN true analyst questioning mode, you lead me to uncover the nature of Form1. It is a way of selecting a variety of items from a spreadsheet. There are about 10 selectable checkboxes for Type and Status of the items (each checkbox is logically ANDed with the others) an option to select all, and an option to select various values of a category in the items.

It is the repopulating of the listbox in response to the selection criteria that is not working.

So - the default selection when the app loads is to select all items. I can use the various checkboxes etc to present different lists of items. The problem starts once I click on one of the items. It is passed to a form to display that item in much greater detail. When I exit from the detail form, I can see the original list, but I can no longer modify it.

(I get a sense that I'm going to need to post a lot more code to explain how this works - or, indeed, doesn't work!)

Thanks for the responses.

BTW Excel2010 running under XP SP3.

PS If I call the LoadListItems routine immediately after returning from Form2, the contents of the list (which will be using the same cretieria as when I went to Form2) work, but the list does not display them - even though they are all being loaded exactly as before. I'm wondering if there is some way I need to fool Excel/VBA into redrawing the form. I can't just reload it, because I'd lose the previos selection criteria...

 

[Norie]

Have you had a look at the Activate event you appear to have for form1.

 

[tfurnivall]

It's always difficult to show multi-level code in a listformat, but here goes!

Level (0) is the Security sign-on form. In the Submit button, if everything passes muster, I call the EventList form:

<code>
(0) frmSecurity.cmdSubmit_Click

...stuff...

Load newfrmEventList
ThisWorkbook.LoadEventList newfrmEventList
newfrmEventList.Show
</code>

This loads the form, loads the contents of the EventListbox, and displays it.
Activation for the form contains the following:
<code>
(1) newfrmEventList.activate
ThisWorkbook.LoadSecurityGlobals
</code>

This allows me to track the user through the various levels for audit and control purposes.

At level(1) I can manipulate the selections for the listbox. There are only a few hundred events, so response to changes in selection is pretty well instantaneous. (This is the bit that doesn't work the second time around). Sooner or later though, I have to select an event, by clicking on the entries in the listbox:

<code>
(1) newfrmEventList.lstEventList.click
...stuff...
Load newfrmEvents
ThisWorkbook.LoadEventIntoForm newfrmEvents, ThisEventID
Me.hide
newfrmEvents.show
</code>

This allows me to determine which event has been selected, and to pass that on to the detailed display (newfrmEvents). I load the data for the event into the form and display it.

I can do all sorts of fun things with the event, but again, sooner or later, I'm done with it, and I click the Exit button:

<code>
(2) newfrmEvents.cmdExit.click
Me.hide
</code>

As I understand it, this returns control to the line in newfrmEventList that immediately follows the newfrmEvent.show statement. Indeed, VBA debug does give this impression.

At that point I have the following code (still within newfrmEventList.lstEventList.click):

<code>
(1)Unload newfrmEvents
Me.show
</code>

I don't mind carrying the burden of unloading the form that I'm no longer using. It makes it easier to repeat the process. Me.show should (i think) allow the EventList form (newfrmEventList) to recover all its functionality, including the present state of the selectors, and to allow me to continue.

No such luck. Either I get no display of the listbox, or the selection options do not work (which shows itself by not displaying the listbox - the load code works fine

Any ideas?

Thanks,

Tony (East Coast)

 

[Norie]

Tony

Why not post the code using code tags?

Then it won't lose formatting like identation, spacing etc.

As for the code, why are you calling events?

If you have code in an event and you need to call it perhaps the code shouldn't be in the event.

 

[tfurnivall]

Thanks for the response, Norie.

I did use the <code> </code> tags in the snippets above. Are you suggesting that I post the whole thing? (I don't think so, but will if that's what you are really suggesting)

re: "Calling events"

I don't think I'm calling the events, merely jumping out of the form class to do a major piece of sheet exploration and data formatting. I don't really understand your comment "As for the code, why are you calling events?
If you have code in an event and you need to call it perhaps the code shouldn't be in the event." All of the code in the event calls out from the event. I never actually try and call an event procedure. (Never thought about it, never tried it, and someday when this problem is solved it sounds like it might be fun

Don't want to sound snippy - I appreciate your interest and suggestions,

Tony

 

[Norie]

Tony

You haven't use the code tags as far as I can see.

If you had then it would look something like this.

sub form1_activate

      LoadListBoxWith Values
      me.show

end sub

I didn't mean to be critical in anyway, but it really isn't clear what you are doing here.

I'm pretty sure I'm missing something though.

If you aren't 'calling' events then what's happening here?

newfrmEvents.cmdExit.click

 

[tfurnivall]

Ahah! My bad. I omitted the word "sub" from the line. Here is the set of excerpt again, with the headers:

<code>
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
</code>

Then, within newfrmEventsList we have:

<code>
Private Sub lstEventList_Click()

Dim ThisEntry As Integer
Dim ThisEventID As String

' Configure the form to show a single event
' Get the data we need to load the selected event

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

Load newfrmEvents
ThisWorkbook.LoadEventIntoForm newfrmEvents, ThisEventID
Me.Hide
newfrmEvents.Show

' This is where we return to after newfrmEvents

Unload newfrmEvents
Me.Show

End Sub
</code>

Lastly, within newfrmEvents I have:

<code>
Private Sub cmdExit_Click()

Me.Hide

End Sub
</code>

Just to make things a little more interesting, there is a third level below Events (Calls - as in Artists Calls) which uses the same select-from-a-list-and-display-the-detail technique that I'm trying to use here. There is no ability to select what Calls for en Event are displayed, so need to reconstitute the list of Calls. This bit works just fine, which is what leads me to think that it's bound up in the selection code for the EventList. However, I trace through that code and it is working fine! Just that nothing displays in the EventListBox!

Grrrr!

Tony