Improve on a Goto rountine

tlawson1

Board Regular
Joined
Jun 15, 2003
Messages
69
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

UniMord

Active Member
Joined
May 6, 2002
Messages
311
You want to use a test value to determine if things are going your way.
Something like this:

Code:
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

Board Regular
Joined
Jun 15, 2003
Messages
69
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,947
Members
430,329
Latest member
asmith75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top