Code for popup box asking for date input

[jimstrongy]

I need a VBA code that will find a sheet "Missing Date" and
Pop up input box will open asking "Please input date".
Once the user inputs date in pop up box code should change the name of the sheet "Missing Date" to date that was inputed in pop up box. Format of the date should be for example: 10-21-12

Thank you

[john55]

hi,
just see if it helps you

Sub Button1_Click()
Dim name As String
If Not SheetExists("Missing Date") Then
    MsgBox "Sheet: Missing Date  doesn't exist!", vbOKOnly + vbCritical, "oops!"
Else
    Sheets("Missing Date").Activate
   
name = InputBox("Please Enter the Name of the Worksheet as dd-mmm-yyyy")
ActiveSheet.name = name
'Range("A1").Value = name
 End If
End Sub


Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

[jimstrongy]

hi,
just see if it helps you

Sub Button1_Click()
Dim name As String
If Not SheetExists("Missing Date") Then
    MsgBox "Sheet: Missing Date  doesn't exist!", vbOKOnly + vbCritical, "oops!"
Else
    Sheets("Missing Date").Activate
   
name = InputBox("Please Enter the Name of the Worksheet as dd-mmm-yyyy")
ActiveSheet.name = name
'Range("A1").Value = name
 End If
End Sub


Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

Thanks so much code work perfect, only issue I have is that when pop up box open. User can put date in any format and that creates a problem for other calculations.
Is there any way that input can only be limited to mm-dd-yy and any other format would not be accepted.

[john55]

hi, try this

Sub Button2_Click()
Dim name As String
If Not SheetExists("Missing Date") Then
    MsgBox "Sheet: Missing Date  doesn't exist!", vbOKOnly + vbCritical, "oops!"
Else
    Sheets("Missing Date").Activate
   
'name = InputBox("Please Enter the Name of the Worksheet as dd-mmm-yyyy")
strDate = Application.InputBox(Prompt:="Enter a Date for Worksheet", Title:="Rename Sheet", Default:=Format(date, "Short Date"), Type:=1)
strDate = Format(strDate, "dd-mm-yyyy")

On Error GoTo ende:
ActiveSheet.name = strDate
End If
ende:
End Sub


Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

[john55]

hi jimstrongy,
thx for rep!
If your problem is solved, please say so clearly, and mark your thread as Solved:
Click the Edit button on your first post in the thread,
Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.