Solved: use input box chara to create new woorksheet name

[GohLS]

Hi everyone,

i am working on a vba for a excel, which if user need to create new worksheet with inputbox chara.
which means whatever is key into the input box, it will be the new worksheet name ..

anyone can help?

Thanks

[mancubus]

a simple code:

[VBA]
Sub add_sht()
Sheets.Add.Name = InputBox("Please Enter the Name of the Worksheet")
End Sub[/VBA]

[mancubus]

to add the worksheet after last sheet:

[vba]
Sub add_sht()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = _
InputBox("Please Enter the Name of the Worksheet")
End Sub
[/vba]

[GohLS]

to add the worksheet after last sheet:

[vba]
Sub add_sht()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = _
InputBox("Please Enter the Name of the Worksheet")
End Sub
[/vba]

Thanks mancubus, this code works fine, however when click on cancel,there is error msg..
and if the input box is empty ( no input from user) or same input twice, it will also show error
is there a way to improve it?

hope you can help..

Thanks

[mancubus]

sure. try this.

[vba]
Sub add_sht()
Dim sht_name As Variant
On Error Resume Next
sht_name = InputBox("Please Enter the Name of the Worksheet")
If sht_name <> "" Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sht_name
Else
MsgBox "Cancelled / Blank"
End If
End Sub

[/vba]

[Bob Phillips]

[vba]

Sub AddSheet()
Dim shName As String
shName = InputBox("Please Enter the Name of the Worksheet")
If shName <> "" Then

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = shName
End If
End Sub
[/vba]

[mancubus]

i added control for checking the sheet name entered by user already exists.

[vba]
Sub add_sht()

Dim sht_name As Variant
Dim sht As Worksheet

On Error Resume Next
sht_name = InputBox("Please Enter the Name of the Worksheet")
If Len(sht_name) > 0 Then
For Each sht In ThisWorkbook.Worksheets
If StrComp(sht.Name, sht_name) = 0 Then
MsgBox "Sheet already exists!", vbCritical, "Warning!"
Exit Sub
End If
Next sht
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sht_name
Else
MsgBox "Cancelled / Blank"
End If
End Sub
[/vba]

[mancubus]

@xld:

is it better to declare the variable for worksheet name as "string"?

[Bob Phillips]

I think so mancubus, as it can only be a string. Always better to be specific IMO.

I would use a simple function for checking if sheet already exists, rather than a loop, like so

[vba]

Public Sub AddSheet()
Dim shName As String
Dim shExists As Boolean

Do

shName = InputBox("Please Enter the Name of the Worksheet")
If shName <> "" Then

shExists = SheetExists(shName)
If Not shExists Then

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = shName
Else

MsgBox "Worksheet " & shName & " already exists", vbOKOnly + vbInformation, "Add Sheet"
End If
End If
Loop Until Not shExists Or shName = ""
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
[/vba]

[mancubus]

thanks.

[GohLS]

so sorry did not check back on the msg.. but some how i got it..
actually i was doing some for of tracking of sale using VBA
it will allows user to key in new customer and update RFQ Number, quotation No, part number .. etc in row A5... stuff like that.. anyway it ok no.. i am not a VBA user by nature.. so i mostly copy and paste using others VBA code.. so the file is quite messy ... and at 1 point of time it is like 39mb hahaha.. now reduce to 300kb ++.. this is a great web for VBA newbie like me to learn and improve .. thanks guys for the help..