View Full Version : Solved: use input box chara to create new woorksheet name
GohLS
02-09-2011, 02:50 AM
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
02-09-2011, 03:22 AM
a simple code:
Sub add_sht()
Sheets.Add.Name = InputBox("Please Enter the Name of the Worksheet")
End Sub
mancubus
02-09-2011, 03:30 AM
to add the worksheet after last sheet:
Sub add_sht()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = _
InputBox("Please Enter the Name of the Worksheet")
End Sub
GohLS
02-09-2011, 07:29 AM
to add the worksheet after last sheet:
Sub add_sht()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = _
InputBox("Please Enter the Name of the Worksheet")
End Sub
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
02-14-2011, 01:55 AM
sure. try this.
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
Bob Phillips
02-14-2011, 02:18 AM
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
mancubus
02-14-2011, 02:28 AM
i added control for checking the sheet name entered by user already exists.
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
mancubus
02-14-2011, 02:43 AM
@xld:
is it better to declare the variable for worksheet name as "string"?
Bob Phillips
02-14-2011, 02:57 AM
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
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
mancubus
02-14-2011, 05:29 AM
thanks.
:beerchug:
GohLS
02-15-2011, 07:26 AM
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..
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.