Prevent error in sheet added

kalcerro_1

New Member
Joined
Feb 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hello,
I'm adding this new sheet to my workbook and giving a specific name to the new sheet. I would like to know how can I protect the creation of a new sheet after this one is already working.
Also, I would like to add to the code the function to only open .csv files, is this possible?
I tried to record the option from data tab in excel but there's an error every time I tried to record the function.


VBA Code:
Private Sub CommandButton1_Click()


Dim getfilename As String
getfilename = Application.GetOpenFilename()
Sheets.Add(After:=Sheets("3. Sheet2")).Name = "4. Result"


End Sub
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,596
Office Version
  1. 365
Platform
  1. Windows
I would like to add to the code the function to only open .csv files, is this possible?
Try this:
VBA Code:
    Dim getfilename As String
    getfilename = Application.GetOpenFilename("Text Files (*.csv), *.csv")
 

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
158
Office Version
  1. 2010
Platform
  1. Windows
I'm adding this new sheet to my workbook and giving a specific name to the new sheet. I would like to know how can I protect the creation of a new sheet after this one is already working.
Try this:
VBA Code:
Private Function ShEx(sn$) As Boolean
    Dim sh As Worksheet
        For Each sh In Worksheets
            If StrComp(sh.Name, sn$, vbTextCompare) = 0 Then
                ShEx = True
                Exit Function
            End If
        Next
End Function

Sub kalcerro() 
    If ShEx("4. Result") Then
        MsgBox "Sheet '4. Result' exists!", 48, "Error"
        Exit Sub
    End If

    ActiveWorkbook.Sheets.Add After:=Worksheets("3. Sheet2")
    Worksheets.Add.Name = "4. Result"
End Sub
 
Solution

kalcerro_1

New Member
Joined
Feb 28, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
Private Function ShEx(sn$) As Boolean
    Dim sh As Worksheet
        For Each sh In Worksheets
            If StrComp(sh.Name, sn$, vbTextCompare) = 0 Then
                ShEx = True
                Exit Function
            End If
        Next
End Function

Sub kalcerro()
    If ShEx("4. Result") Then
        MsgBox "Sheet '4. Result' exists!", 48, "Error"
        Exit Sub
    End If

    ActiveWorkbook.Sheets.Add After:=Worksheets("3. Sheet2")
    Worksheets.Add.Name = "4. Result"
End Sub
It worked great @LazyBug thank you very much!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,596
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

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