Msg box

CodesiriuS

New Member
Joined
Dec 18, 2016
Messages
27
Hello I'm trying to add a message box that alerts users that "this workbook has been formated already". For the most part I have a sub routine that formats a user selected workbook, it then adds formatting and lastly makes a new worksheet called "FDM FORMATTED" the problem is users get errors when the workbook already has a sheet named "FDM FORMATTED". I'm trying to see what the best practice is for error handling duplicate worksheets in a workbook? Any thoughts...


Code:
Dim ws1 As Worksheet, ws2 As Worksheet


    'setup
    Application.ScreenUpdating = False


    With wb
        Set ws1 = .ActiveSheet


    On Error Resume Next
    'delete existing
    Application.DisplayAlerts = False
    .Worksheets("NEW").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0


    'add new
    Set ws2 = Worksheets.Add
    ws2.Name = "FDM FORMATTED"
      
         End With
 
Last edited:

Some videos you may like

Excel Facts

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

kasan

New Member
Joined
Dec 30, 2016
Messages
14
This code will loop throw all sheets and if sheet with name "FDM FORMATTED" already exist - MsgBox will appear and sub will be interrupted.
Code:
For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "FDM FORMATTED" Then
        MsgBox "Already formated!"
        Exit Sub
    End If
Next i
 

CodesiriuS

New Member
Joined
Dec 18, 2016
Messages
27
Hey Kasan thanks for this! The code is getting hung up here
Code:
For i = 1 To Worksheets.Count
on the i "Variable not defined".
I tried to add exchange the i for ws1 but that created a mismatch did I enter this in the right space? sorry if this is a dumb question I'm fairly new to VBA -

Code:
Dim ws1 As Worksheet, ws2 As Worksheet


    'setup
    Application.ScreenUpdating = False


    With wb
        Set ws1 = .ActiveSheet


    On Error Resume Next
    'delete existing
    Application.DisplayAlerts = False
    .Worksheets("NEW").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'add new
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "FDM FORMATTED" Then
        MsgBox "Already formated!"
          End If
Next
Set ws2 = Worksheets.Add
    ws2.Name = "FDM FORMATTED"
    
       End With
 

kasan

New Member
Joined
Dec 30, 2016
Messages
14
the i "Variable not defined"
So lets define variable i, insert this line in the beginning of your code.
Code:
Dim i as Integer[/COLOR]
 

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898

ADVERTISEMENT

I prefer to make reusable functions for such tasks:

Code:
' function to check if the sheet exists
Function sheetExists(sheetName As String) As Boolean
    On Error Resume Next
    sheetExists = (ThisWorkbook.Sheets(sheetName).Name <> "")
    Err.Clear
    On Error GoTo 0
End Function


' test the function
Sub testFunctionCode()
    MsgBox sheetExists("Sheet9")
End Sub
 

MrIfOnly

Active Member
Joined
Nov 10, 2016
Messages
489
Hey Kasan thanks for this! The code is getting hung up here
Code:
For i = 1 To Worksheets.Count
on the i "Variable not defined".
I tried to add exchange the i for ws1 but that created a mismatch did I enter this in the right space? sorry if this is a dumb question I'm fairly new to VBA -

Code:
Dim ws1 As Worksheet, ws2 As Worksheet


    'setup
    Application.ScreenUpdating = False


    With wb
        Set ws1 = .ActiveSheet


    On Error Resume Next
    'delete existing
    Application.DisplayAlerts = False
    .Worksheets("NEW").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'add new
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "FDM FORMATTED" Then
        MsgBox "Already formated!"
          End If
Next
Set ws2 = Worksheets.Add
    ws2.Name = "FDM FORMATTED"
    
       End With

You forgot to put the "Exit Sub" statement in after the MsgBox "Already formated!" statement as in kasan's original post.

Regards,

CJ
 

CodesiriuS

New Member
Joined
Dec 18, 2016
Messages
27

ADVERTISEMENT

Thats Pretty Slick, Im going to have to remember this one! Thanks
 

CodesiriuS

New Member
Joined
Dec 18, 2016
Messages
27
Thanks again Kasan - if I could bug you one more time. So for the code as it stands if a sheet is formatted already the message box pops up as expected but then it immediatley presents another msg box notifying the user that the file was processed and saved. Is there a way close the workbook if there is an error? First code is the module and the second bit of coding is coming from the form.

Code:
Sub Cleanup(wb As Workbook)
Dim ws1 As Worksheet, ws2 As Worksheet


    'setup
    Application.ScreenUpdating = False


    With wb
        Set ws1 = .ActiveSheet


    On Error Resume Next
    'delete existing
    Application.DisplayAlerts = False
    .Worksheets("NEW").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'add new
    Dim i As Integer
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "FDM FORMATTED" Then
        MsgBox "Already formated!"
          End If
          Exit Sub
            Set ws2 = Worksheets.Add
    ws2.Name = "FDM FORMATTED"
    
    Next
    
      'copy data from 1 to 2
    ws1.UsedRange.Copy
    ws2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


    'delete rows with col A blank
    On Error Resume Next
    ws2.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0


    'delete rows with col C blank
    On Error Resume Next
    ws2.Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0


    'delete rows with col D text
    On Error Resume Next
    ws2.Columns(4).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
    On Error GoTo 0


    'delete rows with col F numbers
    On Error Resume Next
    ws2.Columns(6).SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete
    On Error GoTo 0


    'cleanup
    Application.ScreenUpdating = True


    ws2.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
     End With
End Sub

Code:
Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim wbOpen As Workbook
Dim SelectedFile As String


ChDir "C:" ' change this to open the dialog in a specific directory if required




    SelectedFile = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", , "Please select workbook to format")


    If SelectedFile <> "False" Then
        Set wbOpen = Workbooks.Open(SelectedFile)
        Cleanup wbOpen
        MsgBox "Your File has been processed and saved"
        wbOpen.Close SaveChanges:=True
    End If


End Sub
 

kasan

New Member
Joined
Dec 30, 2016
Messages
14
Hi,
Lets follow the logic of your code. First of all you click CommandButton1 and Sub CommandButton1_Click() is called.
This sub contains call another sub - Cleanup:
Code:
Cleanup wbOpen
After Sub Cleanup will be finish (doesn't matter what is result of this sub) - your CommandButton1_Click() sub will continued to the end, that means MsgBox "Your file has been processed..." will always appear.

Try this one:

Code:
Sub Cleanup(wb As Workbook)Dim ws1 As Worksheet, ws2 As Worksheet


    'setup
    Application.ScreenUpdating = False


    With wb
        Set ws1 = .ActiveSheet


    On Error Resume Next
    'delete existing
    Application.DisplayAlerts = False
    .Worksheets("NEW").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'add new
    Dim i As Integer
    For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "FDM FORMATTED" Then
[COLOR=#ff0000]        MsgBox "Already formated!"
[/COLOR][COLOR=#FF0000]        wb.Close SaveChanges:=False[/COLOR]
[COLOR=#ff0000]        Exit Sub[/COLOR]
    End If
     
        Set ws2 = Worksheets.Add
    ws2.Name = "FDM FORMATTED"
    
    Next
   
   ' all format code
   
[COLOR=#ff0000]    wb.Close SaveChanges:=True[/COLOR]
[COLOR=#ff0000]    MsgBox "Your File has been processed and saved"[/COLOR]
    

End Sub


Code:
Private Sub CommandButton1_Click()Application.DisplayAlerts = False
Dim wbOpen As Workbook
Dim SelectedFile As String

ChDir "C:" ' change this to open the dialog in a specific directory if required


    SelectedFile = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", , "Please select workbook to format")

    If SelectedFile <> "False" Then
        Set wbOpen = Workbooks.Open(SelectedFile)
        Cleanup wbOpen
        
    End If

End Sub


 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

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