MsgBox, Retry or Cancel

[ch46guy]

I have a userform that has the user input a worksheet name, which I wave to the variable BorName. I have one IF statement to find out if the worksheet already exists. If it does exists then I want a messagebox thrown and want it to retry or cancel. The second IF statement just allows the sheet to be created if it is NOT already present. The problem I'm having is when it gets to the msgbox it tells me "Compile Error: Expected: =".

Essentially when the message box comes up and the user presses retry, then I want it to go back to that userform to try and put another worksheet name in until a nonexistent name is entered.


thanks for any help,

Sub MIPtab()


Dim WSName As String
Dim NextRow As Integer
Dim BorName As String
Dim k As Integer
Dim s As Integer




BorName = BoringName.Value


For s = 1 To Worksheets.Count
        If Worksheets(s).Name = BorName = True Then
        MsgBox(BorName & " already exists", vbRetryCancel, "Vironex Information")
        Exit For
        End If

Next s


For k = 1 To Worksheets.Count
        If Worksheets(k).Name = BorName = False Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = BorName
        Exit For
        End If
 Next k

 

[L. Howard]

This compiles for me, but I did not run your code.

 MsgBox BorName & (" already exists"), vbRetryCancel, "Vironex Information"

Regards,
Howard

 

[ch46guy]

Thanks L.Howard. The message box comes up but when I select retry, then it moves along pass the code and goes onto the next FOR loop. I want it to go back to the userform and reset the variable BorName.

 

[L. Howard]

Maybe something like this.
Untested, but it compiles in my vb editor.

Howard

Dim i As Long

UF:
'This is where My UserForm is.

For s = 1 To Worksheets.Count
  If Worksheets(s).Name = BorName = True Then
      i = MsgBox("The sheet name you entered already exists, enter another name.", vbRetryCancel, "Vironex Information")
     If (i = vbRetry) Then
        GoTo UF
      ElseIf i = vbCancel Then
        Exit For
     End If
  End If
Next s

 

[L. Howard]

I think this does what you want.

Give it a test fly...

Howard

Option Explicit

Sub MBoxAndSheetCaper()
Dim i As Long, s As Long, k As Long
Dim BorName As String
UF:
BorName = InputBox("Enter a name")

For s = 1 To Worksheets.Count

  If Worksheets(s).Name = BorName = True Then
      i = MsgBox("The sheet name, " & BorName & _
                 ", you entered already exists.", _
                 vbRetryCancel, "Vironex Information")
     If (i = vbRetry) Then
        GoTo UF
      ElseIf i = vbCancel Then
        MsgBox "I will now exit For"
        Exit For
     End If
  End If
  
Next s

MsgBox "Going to next For"

For k = 1 To Worksheets.Count
        If Worksheets(k).Name = BorName = False Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = BorName
        Exit For
        End If
 Next k

End Sub

 

[ch46guy]

Thanks guys. So I finally figured it out. I needed vbOK instead of vbRetryCancel.

Simple solution.

 

[Rick Rothstein]

The problem I'm having is when it gets to the msgbox it tells me "Compile Error: Expected: =".

MsgBox [COLOR=#ff0000][B]([/B][/COLOR]BorName & " already exists", vbRetryCancel, "Vironex Information"[B][COLOR=#ff0000])[/COLOR][/B]

Remove the parentheses... you only use them when you use the MsgBox as a function and assign the output somewhere... when you use the MsgBox as a subroutine (like you are doing, the parentheses cannot be used (unless you preceded it by the Call keyword). However, given that you are having it display both the Retry and Cancel buttons, it would seem like you should be using the MsgBox like a function and assigning the user's button click to a variable so that you can see what he/she clicked and take the appropriate action within your code.