Msg box

[CodesiriuS]

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...

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

 

[kasan]

This code will loop throw all sheets and if sheet with name "FDM FORMATTED" already exist - MsgBox will appear and sub will be interrupted.

For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "FDM FORMATTED" Then
        MsgBox "Already formated!"
        Exit Sub
    End If
Next i

 

[CodesiriuS]

Hey Kasan thanks for this! The code is getting hung up here

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 -

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]

the i "Variable not defined"
So lets define variable i, insert this line in the beginning of your code.

Dim i as Integer[/COLOR]

 

[V_Malkoti]

I prefer to make reusable functions for such tasks:

' 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]

Hey Kasan thanks for this! The code is getting hung up here

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 -

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]

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

 

[CodesiriuS]

Thanks Kasan!!!

 

[CodesiriuS]

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.

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

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]

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:

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:

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

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