Solved: Message Box Not Loading

[coliervile]

The following macro is suppose to see if employee initials are in column "A" and if they are then columns A (1), B (2), C (3), D (4), and E (5) are to load and display in a message (text) box. If the employee's initial are listed five (5) then there would be 5 lines od information in the message ?(text) box. I think my error is in the evaluation portion (colored in red) of the macro??? Please take a look and see where my mistake is.

Best regards,

Charlie

(original VB was provided by XLD for another purpose)

Private Sub TextBox2_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpTestName As Name
Dim mpMessage As String
Dim i As Long

With Worksheets("Leave Request")

mpTestName = CName(Me.TextBox1.Text)
mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set mpNames = .Range("A1").Resize(mpLastRow)

mpRows = .Evaluate("IF((" & mpNames.Address & "<=--""" & """)," & _
"ROW(" & mpNames.Address & "))")

For i = LBound(mpRows) To UBound(mpRows)

If mpRows(i, 1) <> False Then

mpMessage = mpMessage & mpNames.Cells(i, 1).Value & _
" (Requested on: " & mpNames.Cells(i, 2).Text & ", Leave type: " & mpNames.Cells(i, 3).Value & _
", Start Date on: " & mpNames.Cells(i, 4).Text & ", End Date on: " & mpNames.Cells(i, 5).Text & ")" & vbNewLine & vbNewLine

End If
Next i

If mpMessage <> "" Then

MsgBox mpMessage, vbOKOnly + vbInformation
Else

MsgBox "You Have Not Requested Any Leave", vbOKOnly + vbInformation
End If
End With
End Sub

[coliervile]

Here's a copy of the file. On the Leave Request worksheet is the command button labeled "Search For Leave By Name" should fire the "msg box", but doesn't.

Best regards,

Charlie

[Norie]

What's CName?

When I try the code I immediately get told it's not defined.

[coliervile]

CName should identify the name or initials in the textbox in the userform???

The macro is similar tothe one used in the date search userform in my file.

Best regards,

Charlie

[Bob Phillips]

Private Sub CloseName_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

Private Sub TextBox1_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpRequestedStart As Range
Dim mpTypes As Range
Dim mpDatesStart As Range
Dim mpEndStart As Range
Dim mpTestName As String
Dim mpMessage As String
Dim i As Long

With Worksheets("Leave Request")

mpTestName = Me.TextBox1.Text
mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set mpNames = .Range("A1").Resize(mpLastRow)

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

If .Cells(i, "A").Value = mpTestName Then

mpMessage = mpMessage & mpNames.Cells(i, 1).Value & _
" (Requested on: " & mpNames.Cells(i, 2).Text & ", Leave type: " & mpNames.Cells(i, 3).Value & _
", Start Date on: " & mpNames.Cells(i, 4).Text & ", End Date on: " & mpNames.Cells(i, 5).Text & ")" & vbNewLine & vbNewLine

End If
Next i

If mpMessage <> "" Then

MsgBox mpMessage, vbOKOnly + vbInformation
Else

MsgBox "You Have Not Requested Any Leave", vbOKOnly + vbInformation
End If
End With
End Sub


You should have a combobox with the possible names though, and let them choose the name from it.

[coliervile]

Thanks XLD...don't you ever sleep? LOL...Have a great day.

Best regards,

Charlie

[Norie]

Charlie

Well there is no CName function/sub anywhere in your code and it's not a native VBA function/method/whatever.

And I agree with xld - use a combobox, or perhaps a listbox then you probably wouldn't need to faff about with code to search.:)

[Bob Phillips]

Thanks XLD...don't you ever sleep? LOL...Have a great day.

Best regards,

Charlie

It's only 3pm here.

[Bob Phillips]

Charlie

Well there is no CName function/sub anywhere in your code and it's not a native VBA function/method/whatever.

And I agree with xld - use a combobox, or perhaps a listbox then you probably wouldn't need to faff about with code to search.:)

That is because he tried to adapt some other code which was checking dates. That code had a CDate to cast the textbox value to a date, and as Charlie now wanted to check names, he assumed he had to CName the textbox value. Just a lack of knowing, but a reasonable adaptation to try.

[coliervile]

Okay I've taken your suggestion from bot XLD and Norie, thank you. I've place this coding into the userform "frmName" and it's not loading up the names in the cbo??? Where am I messing up?

Best regards,

Charlie

Private Sub cboName_Change()
cboName.Value = ""
With cboName
.AddItem "AA"
.AddItem "BB"
.AddItem "CC"
.AddItem "DD"
.AddItem "EE"
.AddItem "FF"
.AddItem "GG"
End With
End Sub

[Bob Phillips]

You have only added it to frmRequest, you have got to do the same to frmName.

[coliervile]

I have this coding that will put the combo box into the userform and it works. Where do I need to change the coding in "Private Sub TextBox1_AfterUpdate()" to look into the combo box "cboName"?

Best regards,

Charlie

Private Sub UserForm_Initialize()
cboName.Value = ""
With cboName
.AddItem "AA"
.AddItem "BB"
.AddItem "CC"
.AddItem "DD"
.AddItem "EE"
.AddItem "FF"
.AddItem "GG"
End With
End Sub

Private Sub TextBox1_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpRequestedStart As Range
Dim mpTypes As Range
Dim mpDatesStart As Range
Dim mpEndStart As Range
Dim mpTestName As String
Dim mpMessage As String
Dim i As Long

With Worksheets("Leave Request")

mpTestName = Me.TextBox1.Text
mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set mpNames = .Range("A1").Resize(mpLastRow)

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

If .Cells(i, "A").Value = mpTestName Then

mpMessage = mpMessage & mpNames.Cells(i, 1).Value & _
" (Requested on: " & mpNames.Cells(i, 2).Text & ", Leave type: " & mpNames.Cells(i, 3).Value & _
", Start Date on: " & mpNames.Cells(i, 4).Text & ", End Date on: " & mpNames.Cells(i, 5).Text & ")" & vbNewLine & vbNewLine

End If
Next i

If mpMessage <> "" Then

MsgBox mpMessage, vbOKOnly + vbInformation
Else

MsgBox "You Have Not Requested Any Leave", vbOKOnly + vbInformation
End If
End With
End Sub

[coliervile]

Here's the file...

Charlie

[Bob Phillips]

You don't, as you don't have a textbox to look at



Private Sub cboName_Change()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpRequestedStart As Range
Dim mpTypes As Range
Dim mpDatesStart As Range
Dim mpEndStart As Range
Dim mpTestName As String
Dim mpMessage As String
Dim i As Long

With Worksheets("Leave Request")

mpTestName = Me.cboName.Value
mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set mpNames = .Range("A1").Resize(mpLastRow)

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

If .Cells(i, "A").Value = mpTestName Then

mpMessage = mpMessage & mpNames.Cells(i, 1).Value & _
" (Requested on: " & mpNames.Cells(i, 2).Text & ", Leave type: " & mpNames.Cells(i, 3).Value & _
", Start Date on: " & mpNames.Cells(i, 4).Text & ", End Date on: " & mpNames.Cells(i, 5).Text & ")" & vbNewLine & vbNewLine

End If
Next i

If mpMessage <> "" Then

MsgBox mpMessage, vbOKOnly + vbInformation
Else

MsgBox "You Have Not Requested Any Leave", vbOKOnly + vbInformation
End If
End With
End Sub

[coliervile]

Okay igot it figured out...

Best regards,

Charlie

Private Sub CloseName_Click()
Unload Me
End Sub

Private Sub ComboBox1_Change()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
Private Sub UserForm_Initialize()
ComboBox1.Value = ""
With ComboBox1
.AddItem "AA"
.AddItem "BB"
.AddItem "CC"
.AddItem "DD"
.AddItem "EE"
.AddItem "FF"
.AddItem "GG"
End With
End Sub

Private Sub ComboBox1_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpRequestedStart As Range
Dim mpTypes As Range
Dim mpDatesStart As Range
Dim mpEndStart As Range
Dim mpTestName As String
Dim mpMessage As String
Dim i As Long

With Worksheets("Leave Request")

mpTestName = Me.ComboBox1.Text
mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set mpNames = .Range("A1").Resize(mpLastRow)

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

If .Cells(i, "A").Value = mpTestName Then

mpMessage = mpMessage & mpNames.Cells(i, 1).Value & _
" (Requested on: " & mpNames.Cells(i, 2).Text & ", Leave type: " & mpNames.Cells(i, 3).Value & _
", Start Date on: " & mpNames.Cells(i, 4).Text & ", End Date on: " & mpNames.Cells(i, 5).Text & ")" & vbNewLine & vbNewLine

End If
Next i

If mpMessage <> "" Then

MsgBox mpMessage, vbOKOnly + vbInformation
Else

MsgBox "You Have Not Requested Any Leave", vbOKOnly + vbInformation
End If
End With
End Sub