Macro to name the newly created worksheets

[Krisz06]

Hi guys,
I'm trying to create a macro which activate when I'm clicking on add new worksheet.

What I'm trying is that an inputbox appear, there I can add the name I want.
But if the name is already exist, an other inputbox appear till I don't put a value which can be the name.




Thank you!

 

[Molden]

Hi guys,
I'm trying to create a macro which activate when I'm clicking on add new worksheet.

What I'm trying is that an inputbox appear, there I can add the name I want.
But if the name is already exist, an other inputbox appear till I don't put a value which can be the name.




Thank you!

Put this bit in the thisworkbook part:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim strSheetName As String

line1:

    strSheetName = InputBox("Enter New Sheet Name", "Add Sheet")
    
    If SheetExists(strSheetName) = False Then
        If strSheetName = "" Then
            MsgBox "Sheet Not Named", vbInformation
        Else
            ActiveSheet.Name = strSheetName
        End If
    Else
        MsgBox "Sheet Name Exists try again", vbCritical
        GoTo line1
    End If

End Sub

and this bit in a module

Function SheetExists(strSheetName As String) As Boolean

Dim ws As Worksheet

    SheetExists = False

    For Each ws In Worksheets
        If ws.Name = strSheetName Then
            SheetExists = True
        End If
    Next

End Function

Hope this helps

 

[Krisz06]

Put this bit in the thisworkbook part:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim strSheetName As String

line1:

    strSheetName = InputBox("Enter New Sheet Name", "Add Sheet")
    
    If SheetExists(strSheetName) = False Then
        If strSheetName = "" Then
            MsgBox "Sheet Not Named", vbInformation
        Else
            ActiveSheet.Name = strSheetName
        End If
    Else
        MsgBox "Sheet Name Exists try again", vbCritical
        GoTo line1
    End If

End Sub

Hope this helps

It works perfectly.
The module one I had the same, but the first part I tried in different ways which were not working..

this "line1" thing seems good, but can you please explain the logic of it? In just simple google search I couldn't find anything about it.
It works kind of like a bookmark and I can just use it with GoTo line1 commands in any kind of codes?


Thank you for your answer!!!

 

[Molden]

Glad I could help.

I'm not sure if using Goto is the most elegant way of doing it but as long as the result is right.

GoTo is used to jump to different parts of your code. You can replace Line1 with any label for example

Sub MyTest()

    If ActiveCell = 10 Then
        GoTo Hell
    End If

    ' Some other code could go here which would be skipped

Hell:

    MsgBox "Done"

End Sub

the code for your answer starts top to bottom as normal but when the condition isn't met in the input box then the goto line starts the code again like a loop.

Hope this makes sense

 

[Krisz06]

Hi,
Thank you very much! Perfectly make sense Will use it in the future!