CODEWITHSUNDEEP
vbaexcel
Aditya Nugroho
Aditya Nugroho

Reputation: 59

Add a new sheet using Input Box, check existing sheet names and invalid sheet names

Im new to VBA but i need to do something with it. I want to make input box that add a new sheet with specific name. somehow i can make it after some searching over the forum. here are the steps that i want to do, but i cant make it completely done.

Here's the code im using so far

Public Sub CariSheet()
Dim SheetName As String
Dim shExists As Boolean

Do

SheetName = InputBox("Write the name of sheet", "Add Sheet")
If NamaSheet <> "" Then

shExists = SheetExists(SheetName)
If Not shExists Then

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetName
MsgBox "The sheet " & (SheetName) & " is successfuly made", , "Result"
Else

MsgBox "The name is already exist, please enter a new name", vbOKOnly + vbInformation, "Name"

End If
End If
Loop Until Not shExists Or SheetName = ""
End Sub

Private Function SheetExists(ByVal SheetName As String, _
Optional ByVal wb As Workbook)

If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = Not wb.Worksheets(SheetName) Is Nothing

End Function

any help will be appreciated, thanks in advance for your attention. ah and sorry for my bad english.

Upvotes: 2

Views: 5217

Answers (2)

Paresh J
Paresh J

Reputation: 2419

Check if this code helps you:

Just added Else part for you Main If condition where you check If Sheetname is not blank.

Also, You can also uncomment my line Exit Sub if you want to exit subroutine in case of blank input box.

Public Sub CariSheet()
Dim SheetName As String
Dim shExists As Boolean

Do

SheetName = InputBox("Write the name of sheet", "Add Sheet")
If SheetName <> "" Then
shExists = SheetExists(SheetName)
    If Not shExists Then    
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetName
        MsgBox "The sheet " & (SheetName) & " is successfuly made", , "Result"
    Else
        MsgBox "The name is already exist, please enter a new name", vbOKOnly + vbInformation, "Name"
    End If
Else
    MsgBox "Please enter a sheet name.", vbOKOnly + vbInformation, "Warning"
'Exit Sub
End If
Loop Until Not shExists Or SheetName = ""
End Sub

Upvotes: 5

brettdj
brettdj

Reputation: 55682

This code caters for errors for either:

  1. the sheet name already existing
  2. the sheet name being invalid (empty (ie ""), too long or invalid characters)

Code updates so sheet name is validated for length, and then by a Regexp for Valid characters for Excel sheet names before the sheet is created

If either 1 or 2 is true the user is re-prompted (with an additional try again message)

Public Sub CariSheet()
Dim SheetName As String
Dim bFinished As Boolean
Dim strMsg As String
Dim ws As Worksheet

Do While Not bFinished
    SheetName = InputBox("Pls enter the name of the sheet", strMsg, "Add Sheet")
    On Error Resume Next
    Set ws = Sheets(SheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        Select Case Len(SheetName)
        Case 0
        strMsg = "Sheet name is blank"
        Case Is > 31
        strMsg = "Sheet name exceeds 31 characters"
        Case Else
            If ValidSheetName(SheetName) Then
                Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                ws.Name = SheetName
            Else
                strMsg = "Sheet name has invalid characters"
            End If
        End Select
    Else
        strMsg = "Sheet exists"
        Set ws = Nothing
    End If
Loop

End Sub

test for valid sheet name

Function ValidSheetName(strIn As String) As Boolean
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    objRegex.Pattern = "[\<\>\*\\\/\?|]"
    ValidSheetName = Not objRegex.test(strIn)
End Function

Upvotes: 4

Related Questions