Check if sheetname exists before create

Vincent88

Board Regular
Joined
Mar 5, 2021
Messages
157
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
I run my code to duplicate a worksheet. If the worksheet is already existed , then no duplication of sheet and message box (Sheet (sheetname) does exists) will show. But this message box will also pop up when new sheet is created but I do not want this. I only want this msg box to pop up when I run the marco again and found the same sheetname already there. Please help.

VBA Code:
Function DoesSheetExists(sh As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
       Set ws = Sheets(sh)
    On Error GoTo 0

    If Not ws Is Nothing Then DoesSheetExists = True
End Function

Sub Check()
     Dim szToday As String
     szToday = Format(Date, "d mmm yyyy")
    

    If DoesSheetExists(szToday) Then
        MsgBox "Sheet " & szToday & " does exists"
     Else
         Call Module18.BlankSheet03
         MsgBox "Sheet" & szToday & " Created"
    End If
End Sub
 

Excel Facts

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,560
Try:
VBA Code:
Sub Check()
    Application.ScreenUpdating = False
    Dim szToday As String
    szToday = Format(Date, "d mmm yyyy")
    If Not Evaluate("isref('" & szToday & "'!A1)") Then
        Call Module18.BlankSheet03
        MsgBox "Sheet " & szToday & " created."
    Else
        MsgBox "Sheet " & szToday & " exists."
    End If
    Application.ScreenUpdating = True
End Sub
The function is not needed.
 

Vincent88

Board Regular
Joined
Mar 5, 2021
Messages
157
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Try:
VBA Code:
Sub Check()
    Application.ScreenUpdating = False
    Dim szToday As String
    szToday = Format(Date, "d mmm yyyy")
    If Not Evaluate("isref('" & szToday & "'!A1)") Then
        Call Module18.BlankSheet03
        MsgBox "Sheet " & szToday & " created."
    Else
        MsgBox "Sheet " & szToday & " exists."
    End If
    Application.ScreenUpdating = True
End Sub
The function is not needed.
Hi mumps,
When executing, (MsgBox "Sheet " & szToday & " exists.") still appears first before new sheet is created then another msgbox shown after sheet is created !
 

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
474
Office Version
  1. 2013
Platform
  1. Windows
Hi Vincent88,

with the code you posted as well with that of mumps only one message box will pop up. Maybe you have a look at your
Module18.BlankSheet03 as the additional MsgBox might be called from there.

And I personally would get rid of any message box which is modal and will halt the execution of the code till answered. Your aim is to create a sheet named with the date given. Check for the existence: if it´s not there the code should create this sheet. If it already exists no action is required. Without any information the result will be the same after the macro execution that a worksheet with the given name exists. I would recommend to use a message box if the sheet could not be created.

Ciao,
Holger
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,560

ADVERTISEMENT

Please post the code in Module18.
 

Vincent88

Board Regular
Joined
Mar 5, 2021
Messages
157
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi Vincent88,

with the code you posted as well with that of mumps only one message box will pop up. Maybe you have a look at your
Module18.BlankSheet03 as the additional MsgBox might be called from there.

And I personally would get rid of any message box which is modal and will halt the execution of the code till answered. Your aim is to create a sheet named with the date given. Check for the existence: if it´s not there the code should create this sheet. If it already exists no action is required. Without any information the result will be the same after the macro execution that a worksheet with the given name exists. I would recommend to use a message box if the sheet could not be created.

Ciao,
Holger

Please post the code in Module18.
VBA Code:
Sub BlankSheet()
  Dim ws As Worksheet
  Dim LastColumn As Long
  Dim strSheetName As String

  If ActiveWorkbook Is ThisWorkbook Then
    Set ws = ActiveSheet
 
    LastColumn = ws.Range("A1").CurrentRegion.Columns.Count
 
    On Error Resume Next
    Application.DisplayAlerts = False
  ''  ws.Range(Cells(1, 1), Cells(1, LastColumn)).Copy
    ws.Copy before:=ActiveSheet
    strSheetName = Format(Date, "d mmm yyyy")
    ActiveSheet.Name = strSheetName
    Application.DisplayAlerts = True
 
    'Clear All Contents
    With ActiveSheet
      .Cells.ClearContents
      With .OLEObjects
        .Visible = True
        .Delete
      End With
      With .Pictures
        .Visible = True
        .Delete
      End With
      .Range("A1").Resize(1, LastColumn).Value = ws.Range("A1").Resize(1, LastColumn).Value
      .ListObjects.Add(xlSrcRange, .Range("A1").Resize(2, LastColumn), , xlYes).Name = strSheetName
      .ListObjects(strSheetName).TableStyle = "TableStyleMedium2"
      .ListObjects(strSheetName).ShowAutoFilterDropDown = False
    End With
    Set ws = Nothing
  End If

End Sub
Meet Google Drive – One place for all your files

Hi, here is the code and the link to view the file
 

Vincent88

Board Regular
Joined
Mar 5, 2021
Messages
157
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Hi Vincent88,

with the code you posted as well with that of mumps only one message box will pop up. Maybe you have a look at your
Module18.BlankSheet03 as the additional MsgBox might be called from there.

And I personally would get rid of any message box which is modal and will halt the execution of the code till answered. Your aim is to create a sheet named with the date given. Check for the existence: if it´s not there the code should create this sheet. If it already exists no action is required. Without any information the result will be the same after the macro execution that a worksheet with the given name exists. I would recommend to use a message box if the sheet could not be created.

Ciao,
Holger
Hi HaHoBe,
I modified the code but the issue yet resolved. Below is the link of my file. See if you can help me to fix the bug.
Meet Google Drive – One place for all your files
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,560
Your link takes me to the sign-in page. We need a direct link to your file.
 

Forum statistics

Threads
1,136,322
Messages
5,675,075
Members
419,549
Latest member
EliteBeat

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