How to get out of loop ????
 

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

How to get out of loop ????
 

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


"Ray Batig" wrote in message
link.net...
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

How to get out of loop ????
 

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




"Ray Batig" wrote in message
link.net...
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

GoTo
 

I tend not to use GoTos either (even though the example I supplied used
them -oops). I think programmers are generally divided on the issue.

For more info on the opposition to GoTo:
http://www.google.com/search?q=Dijkstra+GoTo


"Tim Zych" wrote in message
...
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




"Ray Batig" wrote in message
link.net...
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