Private Sub ComboBox1_Change()
Dim arr As Variant, x As Long
With Sheets("Waiting list")
arr = .Range("A1").CurrentRegion ' load whole sheet Stock in array
End With
Me.TextBox1.Value = ""
With Me.ComboBox2
.Clear
.ColumnCount = 2
.ColumnWidths = ";0" 'hide column 2
If Me.ComboBox1.ListIndex > -1 Then
For x = 1 To UBound(arr)
If arr(x, 1) = ComboBox1.Value Then
.AddItem (arr(x, 3))
.List(.ListCount - 1, 1) = arr(x, 5) 'Value from column E in hidden column of combobox2
End If
Next
End If
End With
End Sub
Private Sub ComboBox2_Change()
With Me.TextBox1
.Text = ""
If Me.ComboBox2.ListIndex > -1 Then
.Text = Me.ComboBox2.List(Me.ComboBox2.ListIndex, 1)
End If
End With
End Sub
Private Sub UserForm_Initialize()
With Sheets("Waiting list")
UniqueValues .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row), ComboBox1
End With
With Sheets("Ref")
UniqueValues .Range("P2:P" & .Cells(Rows.Count, "P").End(xlUp).Row), ComboBox3
UniqueValues .Range("Q2:Q" & .Cells(Rows.Count, "Q").End(xlUp).Row), ComboBox4
End With
End Sub
Public Sub UniqueValues(rng As Range, cbx As ComboBox)
Dim v As Variant
Dim e As Variant
v = rng.Value
With CreateObject("scripting.dictionary")
.CompareMode = 1
For Each e In v
If Not .Exists(e) And Not IsEmpty(e) Then .Add e, ""
Next e
If .Count Then cbx.List = .Keys
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
If Not ExitAsk = vbYes Then Cancel = True
End If
End Sub
Private Function ExitAsk() As VbMsgBoxResult
Dim Smsg As String
Smsg = "Are you really want to exit? Click Yes to terminate or No to Continue."
ExitAsk = MsgBox(Smsg, vbYesNo + vbDefaultButton2 + vbQuestion, "Exit!")
End Function
Bookmarks