Modeless Userform event

[jofo]

I have a modeless userform and I wish to determine when I move off of it to access the worksheet and when I go back onto it from the sheet.

I have tried a number of events but none seem to work.

I have a listbox on the userform which is linked to the spreadsheet. I want to know if the user leaves the userform because if they do and then sort the sheet the listbox contents will loose the reference that they have to the rows on the sheet and the data on the userform, which is linked to the listbox will be incorrect.

[GTO]

If not confidential, could you post the workbook? It would be easier to see where you're at.

[jofo]

It's confidential, I'm afraid.

[GTO]

It's late on Friday night for me, so am heading out. Will look later and see if this is answered. I do feel confident that you need to be resetting ControlSource someplace; but ControlSource is set by a string, and there are some rules as to brackets and such in the string, depending on if you have the sheet the list is on is active etc...

Mark

[mikerickson]

I heard that the Userform_MouseMove can be used for this.

[Norie]

jofo

Have you considered using a RefEdit control?

Mark

I'm really not sure what you mean about using ControlSource.

For a start a userform doesn't have such a property, it's the controls on it that do.

[GTO]

Norie -My bad, I meant RowSource. I was referencing the ListBox however, as keeping it updated appeared to be the issue.

jofo -

With no example, I am presuming you'd want the ListBox's RowSource to be dynamically set. That is, the user is adding or deleting entries. That said, the attached is an awfully simple example of a one-column listbox that uses the selection change event to reset the RowSource to the ListBox... You'll notice that the second list box doesn't reset RoSource, but updates fine. The advantage to the resetting though is that the RowSOure grows/shrinks as necessary.

The string variable 'strSelection' is not used, just forgot to remove.

You could add the same code to the change event, though I don't think likely to be necessary.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
'Dim strSelection As String
'// See user form code for setting Boolean.//
If bolForm_Loaded Then
'// We'll reset the RowSoure ea time the selection changes IF the form is up. //
UserForm1.lstPersonnel.RowSource = CStr("[" & ThisWorkbook.Name & "]Sheet1!A2:A" & _
shtRoster.Cells(shtRoster.Range("A1").End(xlDown).Row, 1).Row)
End If
End Sub
[/VBA]

Hope this helps and a good/safe weekend to all,

Mark