Improve on a Goto rountine

[tlawson1]

Hi all

I have been using an InputBox to collect data, doing a few simple checks using If statements and returning to the beginning of the rountine if my condition is not met.

My question....is it possible to do this without using the Goto rountine

The code example is

Sub Example()

Dim sName As String 'New name for the new worksheet
Dim wks As Worksheet 'Worksheet Object

Start: 'Use InputBox to get the new menu item name
sName = Application.InputBox(Prompt:="Enter the name for the New Menu Item." & vbNewLine & vbNewLine _
& "Try to Keep the Name short" & vbNewLine & vbNewLine & "", Title:="MAIN MENU")

If sName = "False" Then Exit Sub 'This is if the Cancel button is hit

If IsNumeric(Left(sName, 1)) Then 'Check the name doesn't start with a number
MsgBox "The Name Can Not start with a Number", vbInformation
GoTo Start
Else
End If

'Dont allow the name Data, check if name already exists and don't allow blank name

For Each wks In ActiveWorkbook.Worksheets

If sName = wks.Name Then
ElseIf sName = "Data" Then
ElseIf sName = vbNullString Then

MsgBox "Either the Name already exists or it is not a Valid Name", vbInformation
GoTo Start
Else
End If
Next wks

End Sub

 

[UniMord]

You want to use a test value to determine if things are going your way.
Something like this:

Sub Example()
    Dim sName As String 'New name for the new worksheet
    Dim wks As Worksheet 'Worksheet Object
    Dim flag As Boolean
 
    Do
        flag = False
        'Use InputBox to get the new menu item name
        sName = Application.InputBox(Prompt:="Enter the name for the New Menu Item." & vbNewLine & vbNewLine _
        & "Try to Keep the Name short" & vbNewLine & vbNewLine & "", Title:="MAIN MENU")
 
        If sName = "False" Then Exit Sub 'This is if the Cancel button is hit
 
        If IsNumeric(Left(sName, 1)) Then 'Check the name doesn't start with a number
            MsgBox "The Name Can Not start with a Number", vbInformation
            flag = True
        End If
 
        'Dont allow the name Data, check if name already exists and don't allow blank name
        If flag = False Then
            For Each wks In ActiveWorkbook.Worksheets
 
                If sName = wks.Name Then
                    ElseIf sName = "Data" Then
                    ElseIf sName = vbNullString Then
                        MsgBox "Either the Name already exists or it is not a Valid Name", vbInformation
                        flag = True
                        Exit For
                End If
            Next wks
        End If
    Loop While flag
End Sub

 

[tlawson1]

Many thanks for the reply. I have tried the code and all works well.

I have used a Boolean variable before to control events firing on a UserForm but was unsure on how to do this in a Do...Loop While rountine.

This has helped me and I hope it helps others......Many thanks