Originally Posted by
stranno
Yes it does makes sense. Keep in mind that the userform and listbox numbers do not refer to a synchronical order. Userform1 could also have been Userform2. It are just names. I know it's a bit confus (sic - confusing). But in my last sentence, the first userform refers to userform2. Hold on, i will upload an example.
Dear Stranno,
I do not oft gamble, but I'd put a nice sum on this: Most of us would have read it (I did) just the way Sam did. I would suggest that it is not a bit confusing. It is very confusing and counter-intuitive. While I am sure it was unintentional on your part, could I ask you to consider these things? In short - maybe take a moment of effort on your part, in order to help those willing to donate their time to you; or, at least, not to write the code (including naming) in a manner confusing to the vast majority. Does that make sense?
Now, I didn't really spot any big differences between the two workbooks posted, but I admit that I only glanced at the one attached to #15. Here is the workbook from #11, and what I am guessing at, as you did not answer my question at #13.
In UserForm2 (the 'parent' or FIRST form):
Option Explicit
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Sub CommandButton1_Click()
With UserForm1 '<---SECOND form
'// Make the SECOND form show up where it's not on top of the first. //
.StartUpPosition = 0
.Left = Me.Left + Me.Width
.Top = Me.Top + ((Me.Height - .Height) / 2)
'// Note: This form's caption needs to be unique, as the classname is not. //
.hWndForm1 = FindWindow("ThunderDFrame", Me.Caption)
.Show vbModeless
End With
End Sub
Private Sub UserForm_Initialize()
Dim rngLastCell As Range
'// Find the last cell with data... //
Set rngLastCell = RangeFound(Blad1.Range("B:B"))
'// ...and IF we find any cells with data in the appropriate column, set the //
'// range based on that.
If Not rngLastCell Is Nothing Then
Me.ListBox1.List = Blad1.Range(Blad1.Range("B1"), rngLastCell).Value
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'// Maybe add? Get rid of the SECOND form if the first form (USERFORM2) is //
'// dismissed first. //
On Error Resume Next
Unload UserForm1
On Error GoTo 0
End Sub
In UserForm1 (the 'child' or SECOND form):
Option Explicit
Private Declare Function SetFocusAPI Lib "user32" _
Alias "SetFocus" (ByVal hWnd As Long _
) As Long
Private Userform1Hwnd As Long
Public Property Let hWndForm1(h As Long)
Userform1Hwnd = h
End Property
Public Property Get hWndForm1() As Long
hWndForm1 = Userform1Hwnd
End Property
Private Sub UserForm_Initialize()
Dim rngLastCell As Range
Set rngLastCell = RangeFound(Blad1.Range("A:A"))
If Not rngLastCell Is Nothing Then
Me.ListBox1.List = Blad1.Range(Blad1.Range("A1"), rngLastCell).Value
End If
End Sub
Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'open het geselecteerde project
Dim Index As Long
Dim Ret As Long
Dim tmp
tmp = UserForm2.ListBox1.List
On Error Resume Next
Index = -1
'// MATCH is 1-based, so we minus 1 for ListIndex, which is 0-based. Due to //
'// On Error Resume Next, Index will remain -1 if no match occurs. //
Index = Application.Match(Me.ListBox1.Value, tmp, 0) - 1
On Error GoTo 0
If Index > -1 Then
UserForm2.ListBox1.ListIndex = Index
Ret = SetFocusAPI(hWndForm1)
DoEvents
UserForm2.ListBox1.SetFocus
End If
End Sub
In a Standard Module:
Option Explicit
Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range
If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function
General Notes:
I made the API calls private, as the example is small. I normally would place all the API Functions and any call backs in one Standard Module.
IMO, you should be using Option Explicit at all times. You are declaring your variables (good IMO), why not use Option Explicit as a "Spell Check" if nothing else?
If there is any way for the second form to be showing on its own (there is, just close the first form with the second still displayed), then I would think (not tested, eyes already bleary...) you need to handle the double-click event.
Hope that helps,
Mark
Reply With Quote
