How to get out of loop ????

  • Thread starter Thread starter Ray Batig
  • Start date Start date
R

Ray Batig

I wrote the following code to get a new name . Everything works except my
confirmation check. I have tried several attempts and have not met with
sucess. In all cases, I end up completing the sub when I click the No button
to BMbResponse. What am I missing? Is there a better way?

Thanks for your help and Merry Christmas.

Ray

Do Until NAreaName <> ""

NAreaName = InputBox("Please enter the New Area name. ", "Area
Name Input")
If Len(NAreaName) > 18 Then ' check if name exceeds length limit
MsgBox " Proposed name is too long, try again. "
NAreaName = ""
GoTo loop1:
End If
For Each cell In Range("AreaNames") ' check if name is in use
If NAreaName = Trim(cell.Value) Then
MsgBox "New name is already in use, try again."
NAreaName = ""
GoTo loop1:
End If
Next cell
' confirmation check before proceeding

bMbResponse = MsgBox("You have entered : " & NAreaName & " " &
vbCrLf & _
" Do you want to continue?", vbYesNo)

If bMbResponse = vbNo Then End ' decide you did not want to
proceed

loop1:
Loop
End If
Unload UserForm2
' write new name to worksheet
Worksheets("Area").Cells(7, RrC + 1).Value = NAreaName

End Sub
 
GetName:
NAreaName = InputBox("Please enter the New Area name. ", "Area Name
Input")
If Len(NAreaName) > 18 Then ' check if name exceeds length limit
MsgBox " Proposed name is too long, try again. "
GoTo GetName
End If
For Each cell In Range("AreaNames") ' check if name is in use
If NAreaName = Trim(cell.Value) Then
MsgBox "New name is already in use, try again."
GoTo GetName
End If
Next cell

' confirmation check before proceeding

bMbResponse = MsgBox("You have entered : " & NAreaName & " " & vbCrLf &
_
" Do you want to continue?", vbYesNo)

If bMbResponse = vbNo Then Exit Sub ' decide you did not want to
proceed

' write new name to worksheet
Worksheets("Area").Cells(7, RrC + 1).Value = NAreaName
 
I tend to stay away from GoTos. They make it harder to be structured.

Sub Tester()
Dim NAreaName As String, blnExit As Boolean
Dim bMbResponse As Long
Do
NAreaName = InputBox("Enter the new area name", "Enter Area Name")
If NAreaName = "" Then
blnExit = False
ElseIf Len(NAreaName) > 18 Then
MsgBox "Too Long"
blnExit = False
ElseIf Application.CountIf(Range("AreaNames"), _
NAreaName) > 0 Then
MsgBox "Name has already been used."
blnExit = False
Else
blnExit = True
End If
Loop While blnExit = False
bMbResponse = MsgBox("You have entered " & NAreaName & _
vbLf & vbLf & "Do you want to continue", vbYesNo)
If bMbResponse = vbYes Then
Worksheets("Area").Cells(7, RrC + 1).Value = NAreaName
End If
End Sub
 
Back
Top