AndyTampa
Board Regular
- Joined
- Aug 14, 2011
- Messages
- 199
- Office Version
- 365
- 2016
- Platform
- Windows
I have this spreadsheet I use with templates on it. I am constantly typing the same notes into this one template so I started creating buttons that will append the text with more text. I've ventured into message boxes and input boxes for when I didn't fill a cell or just need something specific added in the middle of the text. I've never used these boxes before.
I've got one button that I've (sort of) gotten to work, but I'd like to perfect it if possible. It's an input box that asks for a date. I've tried all sorts of solutions I've found online but there are issues with all of them.
Here's what I've got so far.
This mostly works. However, IsDate accepts decimal numbers and provides output as a time and also accepts short month-day inputs, converting them to a date and assuming they are 2024. The only formats I want to be accepted are mm/dd/yyyy, mm/dd/yy or m/d/yy.
I've tried testing the input with the below but get a Type Mismatch error if it's not right.
Format doesn't work without the VBA. in front of it.
I've also tried testing the input with the below but it won't accept dates like 5/5/24.
Since I'm unfamiliar with the InputBox, I was wondering if there was any way to force it to only accept actual dates only.
I've got one button that I've (sort of) gotten to work, but I'd like to perfect it if possible. It's an input box that asks for a date. I've tried all sorts of solutions I've found online but there are issues with all of them.
Here's what I've got so far.
VBA Code:
Private Sub CommandButton19_Click()
Dim String1 As String
Dim TDate As String
String1 = Range("PCase")
TDate = InputBox("Please enter the new FTD in the format MM/DD/YYYY.", "New FTD")
If IsDate(TDate) Then
TDate = CDate(TDate)
Range("ActTaken") = Range("ActTaken") & " The existing FTD has been removed from case" & String1 & _
" and replaced with a " & TDate & " FTD as "
Else
MsgBox "Oops. Next time enter a date."
End If
End Sub
This mostly works. However, IsDate accepts decimal numbers and provides output as a time and also accepts short month-day inputs, converting them to a date and assuming they are 2024. The only formats I want to be accepted are mm/dd/yyyy, mm/dd/yy or m/d/yy.
I've tried testing the input with the below but get a Type Mismatch error if it's not right.
Code:
If Not VBA.Format(TDate, "mm/dd/yyyy") Then
Format doesn't work without the VBA. in front of it.
I've also tried testing the input with the below but it won't accept dates like 5/5/24.
Code:
If TDate Like "[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]" Then
Since I'm unfamiliar with the InputBox, I was wondering if there was any way to force it to only accept actual dates only.