DeanP
02-01-2019, 02:02 AM
I cannot resolve a Type mismatch error that I have with a userform.
I have 2 textboxes in my userform. One for the year to be entered and the second for the month in mmm format. The 1st
textbox stores year value which will be copied to a cell in the worksheet when the form is closed. The value entered in
the 2nd box, is the lookup for a 3rd textbox. The lookup event happens when an OK button is clicked.
In my worksheet in Z1:Y6 I have a list X: Jan Y: 01 Z: 12/01/19. I get they type mismatch error when, after the month is
entered, the OK button is clicked.
Debug highlights this line of code:
For x = 1 To wsLR
Here is the rest of the code
Private Sub CommandButton1_Click()
Dim wsJournal As Worksheet
Set wsJournal = Worksheets("Journal")
With wsJournal
.Cells(4, 5).Value = Me.TextBox6.Value
.Cells(4, 6).Value = Me.TextBox8.Value
.Cells(4, 3).Value = Me.TextBox9.Value
.Cells(4, 8).Value = Me.TextBox10.Value
End With
Call FuncAllocJnl
Unload (UserForm1)
End Sub
Private Sub CommandButton2_Click()
Unload (UserForm1)
Set UserForm1 = Nothing
End Sub
Private Sub CommandButton3_Click()
' This sub routine autofills the boxes in the userform frame based on what was entered
Call TextBox8_Change
End Sub
Private Sub TextBox8_Change()
'Lookups from the worksheet to fill the boxes: month name into period number
Dim wsJournal As Worksheet
Dim wsLR As Range
Set wsJournal = Worksheets("Journal")
Set wsLR = wsJournal.Range("X1:Z6")
x = 1
For x = 1 To wsLR
If wsJournal.Cells(x, 1) = Me.TextBox7 Then
Me.TextBox8 = wsLR.Cells(x, 2)
End If
Next x
End Sub
Private Sub TextBox9_Change()
'Period number to date range
Dim wsJournal As Worksheet
Dim wsLR As Range
Set wsJournal = Worksheets("Journal")
Set wsLR = wsJournal.Range("X1:Z6")
x = 1
For x = 1 To wsLR
If wsJournal.Cells(x, 2) = Me.TextBox8 Then
Me.TextBox9 = wsLR.Cells(x, 3)
End If
Next x
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevent users from closing form with red cancel
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!"
End If
End Sub
I don't know what I'm doing wrong here. Any advice would be appreciated.
I have 2 textboxes in my userform. One for the year to be entered and the second for the month in mmm format. The 1st
textbox stores year value which will be copied to a cell in the worksheet when the form is closed. The value entered in
the 2nd box, is the lookup for a 3rd textbox. The lookup event happens when an OK button is clicked.
In my worksheet in Z1:Y6 I have a list X: Jan Y: 01 Z: 12/01/19. I get they type mismatch error when, after the month is
entered, the OK button is clicked.
Debug highlights this line of code:
For x = 1 To wsLR
Here is the rest of the code
Private Sub CommandButton1_Click()
Dim wsJournal As Worksheet
Set wsJournal = Worksheets("Journal")
With wsJournal
.Cells(4, 5).Value = Me.TextBox6.Value
.Cells(4, 6).Value = Me.TextBox8.Value
.Cells(4, 3).Value = Me.TextBox9.Value
.Cells(4, 8).Value = Me.TextBox10.Value
End With
Call FuncAllocJnl
Unload (UserForm1)
End Sub
Private Sub CommandButton2_Click()
Unload (UserForm1)
Set UserForm1 = Nothing
End Sub
Private Sub CommandButton3_Click()
' This sub routine autofills the boxes in the userform frame based on what was entered
Call TextBox8_Change
End Sub
Private Sub TextBox8_Change()
'Lookups from the worksheet to fill the boxes: month name into period number
Dim wsJournal As Worksheet
Dim wsLR As Range
Set wsJournal = Worksheets("Journal")
Set wsLR = wsJournal.Range("X1:Z6")
x = 1
For x = 1 To wsLR
If wsJournal.Cells(x, 1) = Me.TextBox7 Then
Me.TextBox8 = wsLR.Cells(x, 2)
End If
Next x
End Sub
Private Sub TextBox9_Change()
'Period number to date range
Dim wsJournal As Worksheet
Dim wsLR As Range
Set wsJournal = Worksheets("Journal")
Set wsLR = wsJournal.Range("X1:Z6")
x = 1
For x = 1 To wsLR
If wsJournal.Cells(x, 2) = Me.TextBox8 Then
Me.TextBox9 = wsLR.Cells(x, 3)
End If
Next x
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevent users from closing form with red cancel
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!"
End If
End Sub
I don't know what I'm doing wrong here. Any advice would be appreciated.