MsgBox, Retry or Cancel

ch46guy

New Member
Joined
Apr 29, 2013
Messages
29
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,

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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
This compiles for me, but I did not run your code.


Code:
 MsgBox BorName & (" already exists"), vbRetryCancel, "Vironex Information"
Regards,
Howard
 

ch46guy

New Member
Joined
Apr 29, 2013
Messages
29
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Maybe something like this.
Untested, but it compiles in my vb editor.

Howard


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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
I think this does what you want.

Give it a test fly...

Howard


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

New Member
Joined
Apr 29, 2013
Messages
29
Thanks guys. So I finally figured it out. I needed vbOK instead of vbRetryCancel.

Simple solution.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
The problem I'm having is when it gets to the msgbox it tells me "Compile Error: Expected: =".

Code:
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.
 
Last edited:

Forum statistics

Threads
1,082,316
Messages
5,364,491
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top