VBA Userform textbox second combobox doesn't fill

[Tumtum]

Hello,
I'm pretty new at programming with VBA.
I'v got an excel document with a sheet "errorabortlijst" and a second sheet "kassasystemmenu".
Now i want to make one userform with a combobox that let me select from all the errorabort codes from the excel sheet "errorabortlijst" and 1 textboxe that show me the description of that error abort and the second textbox that show me the solution belonging to the selected errorabort code.
So far i got this working.
My problem comes with the second combobox. I also manage to get able to let me select a kassa from all the kassatypes (the are in the second worksheet called kassasysteemmenu) but the textbox that belongs to this combobox and is rigth below this combobox doesn't fill up when i select a kassatype.
This is what i have programmed so far:

Private Sub UserForm_Initialize()

lr = Sheets("Errorabortlijst").Cells(Sheets("Errorabortlijst").Rows.Count, 1).End(xlUp).Row
Set Range2 = Sheets("Errorabortlijst").Range("A2:A" & lr)
For Each cel In Range2
With UserForm.Cmbxerrorabort
.AddItem Worksheets("Errorabortlijst").Range("A" & cel.Row)
End With
Next

lr = Sheets("Kassasysteemmenu").Cells(Sheets("Kassasysteemmenu").Rows.Count, 1).End(xlUp).Row
Set Range3 = Sheets("Kassasysteemmenu").Range("A2:A" & lr)
For Each cel In Range3
With UserForm.Cmbxkassa
.AddItem Worksheets("Kassasysteemmenu").Range("A" & cel.Row)
End With
Next

End Sub

Private Sub Cmbxerrorabort_Change()

Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Errorabortlijst")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Val(Me.Cmbxerrorabort.Value) = ws.Cells(i, "A") Then
Me.Txtbxfoutomschrijving = ws.Cells(i, "B").Value
Me.Txtbxoplossing = ws.Cells(i, "C").Value
End If
Next i

End Sub
Private Sub Cmbxkassa_Change()

Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Kassasysteemmenu")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Val(Me.Cmbxkassa.Value) = ws.Cells(i, "A") Then
Me.Txtbxkassa = ws.Cells(i, "B").Value
End If
Next i

End Sub

Can someone explane to me what i'm doing wrong heren?

 

[Tumtum]

what i'm trying to do is to create a knowledge document for my collegues to solve all kind off problems with a cashregister. So when the document is complete you got one page where you can select between all kind off problems en when you select a problem you get a next page where you can specify your problem and get some solutions displayed which will help you to solve the problem.
These are the worksheets that goes allong with the vba programming in the threat.

Vectron_probleem_oplostool1.xlsm
	A	B	C
1	Error_abort	Foutmelding	Oplossing
2	10	Configuration cannot be copied	new version
3	11	Configuration does not apply to the requested table	new version
4	15	MailboxMessageSize	new version
5	21	The attempt to increase/decrease the stack size has failed. It was not possible to switch back to the old state.	new version
6	22	The attempt to change the table according to the new configuration has failed. It was not possible to switch back to the old state.	new version
7	24	Table conversion failed	new version
8	25	TabCopyRecSizeError	new version
9	30	Increase/decrease has failed. It was not possible to switch back to the old state.	new version
10	31	The attempt to change the table according to the new configuration, has failed. It was not possible to switch back to the old state.	new version
11	41	A function that is not possible for indexed table was executed.	new version
12	43	Increase/decrease has failed. It was not possible to switch back to the old state.	new version
13	44	An index element cannot be stored	new version
14	50-60	Invalid data to adjust the tables during start or reconfiguration.	new version
15	61	Invalid data	start menu - delete invalid data
16	62	Locked memory block	start menu - delete invalid data
17	63	Old and new table type do not match	start menu - delete invalid data
18	64	Invalid link between tables	start menu - delete invalid data
19	65	Not enough memory reserved	new version
20	66	Wrong commands for the change of tables	new version
21	68	Invalid update sequence	start menu - delete invalid data
Errorabortlijst

Vectron_probleem_oplostool1.xlsm
	A	B
1	type:	actie:
2
3	vpos32	Zet de kassa uit. Houdt de Q-toets (bovenste rij meest linker toets) ingedrukt terwijl je de kassa aanzet. Als je een piep hoort of een stuk of 16 vakjes ziet laat je los
4	mini handel	Zet de kassa uit. Houdt de Q-toets (bovenste rij meest linker toets) ingedrukt terwijl je de kassa aanzet. Als je een piep hoort of een stuk of 16 vakjes ziet laat je los
5	mini horeca	Zet de kassa uit. Houdt de Q-toets (bovenste rij meest linker toets) ingedrukt terwijl je de kassa aanzet. Als je een piep hoort of een stuk of 16 vakjes ziet laat je los
6	vario touch	Zet de kassa uit. Houdt de Q-toets (bovenste rij meest linker toets) ingedrukt terwijl je de kassa aanzet. Zodra je in het scherm ziet "Callibration cleared in 5,4,…" (dit telt af van 5 naar 0) moet je direct loslaten nog voordat dit bij 0 is anders kom je in de callibratie
7	vario toets	Zet de kassa uit. Houdt de Q-toets (bovenste rij meest linker toets) ingedrukt terwijl je de kassa aanzet. Zodra je in het scherm ziet "Callibration cleared in 5,4,…" (dit telt af van 5 naar 0) moet je direct loslaten nog voordat dit bij 0 is anders kom je in de callibratie
8	vario 2	Zet de kassa uit. Houdt je vinger in het midden op het scherm gedrukt terwijl je de kassa aanzet. Zodra je in het scherm ziet "Callibration cleared in 5,4,…" (dit telt af van 5 naar 0) moet je direct loslaten nog voordat dit bij 0 is anders kom je in de callibratie
9	Color Touch	Zet de kassa uit. Houdt je vinger in het midden op het scherm gedrukt terwijl je de kassa aanzet. Zodra je in het scherm ziet "Callibration cleared in 5,4,…" (dit telt af van 5 naar 0) moet je direct loslaten nog voordat dit bij 0 is anders kom je in de callibratie
10	Color Touch 64	Zet de kassa uit. Houdt je vinger in het midden op het scherm gedrukt terwijl je de kassa aanzet. Zodra je in het scherm ziet "Callibration cleared in 5,4,…" (dit telt af van 5 naar 0) moet je direct loslaten nog voordat dit bij 0 is anders kom je in de callibratie
Kassasysteemmenu

 

[dmt32]

Hi,
try replacing your existing codes with following & see if this will do what you want

Dim wsErrorabortlijst   As Worksheet
Dim wsKassasysteemmenu  As Worksheet

Private Sub UserForm_Initialize()
    Dim lr          As Long
    
    Set wsErrorabortlijst = ThisWorkbook.Worksheets("Errorabortlijst")
    
    Set wsKassasysteemmenu = ThisWorkbook.Worksheets("Kassasysteemmenu")
    
    With wsErrorabortlijst
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Me.Cmbxerrorabort.List = .Range("A2:A" & lr).Value
    End With
    
    With wsKassasysteemmenu
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Me.Cmbxkassa.List = .Range("A2:A" & lr).Value
    End With
    
End Sub

Private Sub Cmbxerrorabort_Change()
    Dim r As Long
    
    r = Me.Cmbxerrorabort.ListIndex + 2

    With wsErrorabortlijst
        Me.Txtbxfoutomschrijving = IIf(r = 1, "", .Cells(r, "B").Value)
        Me.Txtbxoplossing = IIf(r = 1, "", .Cells(r, "C").Value)
    End With
End Sub
Private Sub Cmbxkassa_Change()
    Dim r As Long
    
    r = Me.Cmbxkassa.ListIndex + 2
    
    Me.Txtbxkassa = IIf(r = 1, "", wsKassasysteemmenu.Cells(r, "B").Value)
    
End Sub

Note the variables at top - these must be placed at TOP of your forms code page OUTSIDE any procedure.

Hope helpful

Dave

 

[Tumtum]

Hi,
try replacing your existing codes with following & see if this will do what you want

Dim wsErrorabortlijst   As Worksheet
Dim wsKassasysteemmenu  As Worksheet

Private Sub UserForm_Initialize()
    Dim lr          As Long
   
    Set wsErrorabortlijst = ThisWorkbook.Worksheets("Errorabortlijst")
   
    Set wsKassasysteemmenu = ThisWorkbook.Worksheets("Kassasysteemmenu")
   
    With wsErrorabortlijst
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Me.Cmbxerrorabort.List = .Range("A2:A" & lr).Value
    End With
   
    With wsKassasysteemmenu
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Me.Cmbxkassa.List = .Range("A2:A" & lr).Value
    End With
   
End Sub

Private Sub Cmbxerrorabort_Change()
    Dim r As Long
   
    r = Me.Cmbxerrorabort.ListIndex + 2

    With wsErrorabortlijst
        Me.Txtbxfoutomschrijving = IIf(r = 1, "", .Cells(r, "B").Value)
        Me.Txtbxoplossing = IIf(r = 1, "", .Cells(r, "C").Value)
    End With
End Sub
Private Sub Cmbxkassa_Change()
    Dim r As Long
   
    r = Me.Cmbxkassa.ListIndex + 2
   
    Me.Txtbxkassa = IIf(r = 1, "", wsKassasysteemmenu.Cells(r, "B").Value)
   
End Sub

Note the variables at top - these must be placed at TOP of your forms code page OUTSIDE any procedure.

Hope helpful

Dave

Dave,

thank you for your help.
This is exactly what i needed. It works just fine

kind regards

Michel

 

[dmt32]

thank you for your help.
This is exactly what i needed. It works just fine

Glad solution resolves your issue & appreciate feedback

Dave