CODEWITHSUNDEEP
vbaexcel
Mike Mann
Mike Mann

Reputation: 546

I have to click okay for message box 4 times

I'm not sure why, but when my if statement is false and my msgbox is called I have to click okay 4 times for it to go away. How do I correct this?

My code:

Option Explicit
Private tskCol As String
Private unitCol As String

Private Sub txtTask_Change()

End Sub

Public Sub UserForm_Initialize()
    tskCol = Application.InputBox("Enter Column Letter for Task Names", Type:=2)
    unitCol = Application.InputBox("Enter Column Letter for Number of Units", Type:=2)
End Sub

Private Sub cmdAdd_Click()
    Dim LastRow As Long, i As Long
    LastRow = ActiveSheet.Range(tskCol & Rows.Count).End(xlUp).Row
    'Copy input values to sheet
    For i = 2 To LastRow
        If CStr(ActiveSheet.Range(tskCol & i).Value) = CStr(Me.txtTask.Value) Then
            ActiveSheet.Range(unitCol & i).Value = Me.txtQuantity.Value
        Else
        MsgBox "Task Not Found!"
        End If
    Next i
    'Clear input controls
    Me.txtTask.Value = ""
    Me.txtQuantity.Value = ""
End Sub

Upvotes: 1

Views: 50

Answers (1)

L42
L42

Reputation: 19727

To answer your specific question, try changing this line on the Else statement:

Msgbox "Task Not Found"

to

If i = LastRow Then Msgbox "Task Not Found"

You might also want to put Exit For if the task is found. Refactoring IF statement:

If CStr(ActiveSheet.Range(tskCol & i).Value) = CStr(Me.txtTask.Value) Then
    ActiveSheet.Range(unitCol & i).Value = Me.txtQuantity.Value
    Exit For '/* no need to continue the loop, task is found */
Else
    '/* only call msgbox if all rows are processed */
    If i = LastRow Then Msgbox "Task Not Found"
End If

Upvotes: 2

Related Questions