PDA

View Full Version : VBA Code to Search and Select Sheet through Input BOX



rohan8510
08-15-2015, 04:04 AM
hi I have this code from excel.com but how do I just search the sheet and select it through input box also is there any way the input box remains always in the sheet and does not go away requesting your help many thanks




Sub Macro()

Dim strNewName As String
Dim ws As Worksheet
Dim boolFound As Boolean

strNewName = InputBox("Please enter a valid Worksheet Name:", "New Worksheet Name")
For Each ws In Worksheets
If ws.Name Like strNewName Then boolFound = True: Exit For
Next

If boolFound = True Then
MsgBox ("Name Already Exisits, Please enter a new name")
Sheets(strNewName).Select

Else
Sheets.Add.Name = strNewName
End If

End Sub

rohan8510
08-16-2015, 09:29 AM
hi plz consider this thread solved in mr excel.com by mumps

mikerickson
08-16-2015, 10:13 AM
Perhaps this will do what you want

Sub test()
Dim uiName As String

Do
uiName = Application.InputBox("Enter new sheet name", Type:=2)
If uiName = "False" Or Trim(uiName) = vbNullString Then Exit Do

On Error Resume Next
If LCase(Worksheets(uiName).Name) <> LCase(uiName) Then
Err.Clear
With ThisWorkbook
With .Sheets.Add(after:=.Sheets(.Sheets.Count))
.Name = uiName
If Err Then
MsgBox Error
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
End With
End With
Else
Err.Clear
On Error GoTo 0
MsgBox "old name"
End If
Loop Until False
End Sub