Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
DO LOOP in VBA | Excel Discussion (Misc queries) | |||
Loop | Excel Discussion (Misc queries) | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
For Each Loop | Excel Programming |