CODEWITHSUNDEEP
excelvbafor-loopnested-loops
radeg
radeg

Reputation: 61

Is there a way to have a "FOR" statement within a nested-if loop?

I am attempting to delete a sheet within a workbook if it matches a user inputted value. If the value does not exist, show an error message. Is there a better way to do this than what I currently have?

Initially, I tried a nested-if loop with several if statements to get my achieved result but that did not work. The code below is what I've come to but it still has some bugs in it. For example, when the first FOR condition is met, the message box will appear for both FOR statements (which is incorrect).

Dim ws As Worksheet

Private Sub cmdDeleteProj_Click()
'
'Remove project sheet after project has been completed
'

'Show error message if user input field is blank
If txtRemProjNum.Value = "" Then
    MsgBox "Input valid project number to continue.", vbExclamation, "Required Field Left Blank"
    uf_RemProj.txtRemProjNum.SetFocus
End If

'Delete user defined project sheet if it exists
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = txtRemProjNum Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
        MsgBox "Project removed from inventory tracker."
    End If
Next ws

'Show error message if project does not exist
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    If txtRemProjNum.Value <> "" And ws.Name <> txtRemProjNum Then
        MsgBox "Project number not found." & vbNewLine & "" & vbNewLine & "Input valid project number.", vbCritical, "Out of Range"
        uf_RemProj.txtRemProjNum.Text = ""
        uf_RemProj.txtRemProjNum.SetFocus
    End If
Next ws

End Sub

I expect a sheet to be deleted if it exists, but cannot get the if statements and FOR loops to work.

Upvotes: 0

Views: 36

Answers (2)

Matt Cremeens
Matt Cremeens

Reputation: 5151

I think you are seeing both messages because the worksheet is there in the first loop and not in the second. Why not just loop through once and use an elseif statement

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = txtRemProjNum Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
        MsgBox "Project removed from inventory tracker."
    ElseIf
        txtRemProjNum.Value <> "" And ws.Name <> txtRemProjNum Then
        MsgBox "Project number not found." & vbNewLine & "" & vbNewLine & "Input 
                valid project number.", vbCritical, "Out of Range"
        uf_RemProj.txtRemProjNum.Text = ""
        uf_RemProj.txtRemProjNum.SetFocus
    End If
Next ws

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27259

Give this a go and let me know any questions. I didn't comment to much because the code flow should be self-explanatory.

Option Explicit

Dim ws As Worksheet

Private Sub cmdDeleteProj_Click()

    'Remove project sheet after project has been completed

    'Show error message if user input field is blank
    If txtRemProjNum.Value = "" Then
        MsgBox "Input valid project number to continue.", vbExclamation, "Required Field Left Blank"
        uf_RemProj.txtRemProjNum.SetFocus
        Exit Sub
    End If

    Dim projectFound As Boolean

    'Delete user defined project sheet if it exists
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = txtRemProjNum Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            MsgBox "Project removed from inventory tracker."
            projectFound = True
            Exit For
        End If
    Next ws

    If Not projectFound Then
        MsgBox "Project number not found." & vbNewLine & "" & vbNewLine & "Input valid project number.", vbCritical, "Out of Range"
        uf_RemProj.txtRemProjNum.Text = ""
        uf_RemProj.txtRemProjNum.SetFocus
    End If

End Sub

Upvotes: 1

Related Questions