VBA Question on InputBoxes to Fill Cells on Worksheet

[RalphMHill]

Hello everyone, first let me say thank you for any and all assistance provided. It has been a Long time since I used vBA.
I have an Estimate spreadsheet I use for my customers that I would like to use Input Boxes upon opening the workbook.




Version of the program: 2010
What you want it to do: Use InputBoxes to fill 4 different Cell references on wotksheet on Opening of the Workbook




Here is the code I have so far:


Private Sub Workbook_Open()

Dim strDate As String
Dim strEstimate As String
Dim strCustomerID As String
Dim strTaxRate As String

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'THIS WORKS FINE

strDate = InputBox("Please Enter the DATE as MM/DD/YYYY", "DATE", Format(Date - 1, "mm/dd/yyyy"))
'is there anyway to verify a correct date has been entered?
strDate = Format(CDate(strDate), "mm/dd/yyyy")
Range("R3:W3").Value = strDate


'THE PRE-FORMATTED INPUT SHOWS: 1899-*** (This is the Current Year, plus a 3 digit number which changes for each estimate

strEstimate = InputBox("Please Enter the ESTIMATE Number as YYYY-***", "ESTIMATE NUMBER", Format(Text - 1, "yyyy-***"))
'the Estimate number is the Year followed by a 3 digit number Example: 2017-115
'is there anyway to verify the correct format has been followed?
strEstimate = Format(CStr(strEstimate), "yyyy-***")
Range("R4:W4").Value = strEstimate



'THE PRE-FORMATTED INPUT SHOWS: -***XX-X (The Customer Number is usually C1801-1 (this number is used in a vlookup statement that 'references another worksheet and automatically retreives and poplulates the Name, Address, Phone Number Fields.

strCustomerID = InputBox("Please Enter the CUSTOMER ID as ***XX-X", "CUSTOMER ID", Format(Text - 1, "***XX-X"))
'Cumtomer ID's begin with the Letter C and then a Four Digit Number followed by a 1 or 2 (i.e. C1018-1) this is used to lookup customer
'information from a seperate spreadsheet CustomerID.xls
'is there anyway to verify the correct format has been followed?
strCustomerID = Format(CStr(strCustomerID), "C***X-X")
Range("E9:K9").Value = strCustomerID

'THE PRE-FORMATTED INPUT SHOWS: -X (This is the Tax Rate used for Materials (between .0725 and .100 Percent

strTaxRate = InputBox("Please enter the TAX RATE for Materials as Decimal Number, Example: .09 or .10", "TAX RATE", Format(Percentage - 2, "X"))
' Looking for a Number between .0725 and .1000 percent to be entered.
'is there anyway to verify the correct format has been followed?
strTaxRate = Format(CStr(strTaxRate))
Range("S18:T18").Value = strTaxRate

End Sub

Any and all suggestions will be greatly appreciated.

[mikerickson]

'is there anyway to verify a correct date has been entered?
Yes.

Do
strDate = InputBox("Please Enter the DATE as MM/DD/YYYY", "DATE", Format(Date - 1, "mm/dd/yyyy"))
if StrDate = "" Then Exit Sub: Rem cancel pressed
Loop Until IsDate(strDate)

strDate = Format(CDate(strDate), "mm/dd/yyyy"))

[RalphMHill]

Iget a Compile Syntax Error on the Last line of Code

[Paul_Hossler]

Option Explicit
Private Sub Workbook_Open()

Dim strDate As String
Dim strEstimate As String
Dim strCustomerID As String
Dim strTaxRate As String

' newHour = Hour(Now())
' newMinute = Minute(Now())
' newSecond = Second(Now()) + 5
' waitTime = TimeSerial(newHour, newMinute, newSecond)
' Application.Wait waitTime

Do
strDate = Application.InputBox("Please Enter the DATE as MM/DD/YYYY", "DATE", Format(Date - 1, "mm/dd/yyyy"))
If Len(strDate) = 0 Then Exit Sub
Loop Until strDate Like "##/##/####" And IsDate(strDate)
Range("R3:W3").Value = Format(CDate(strDate), "mm/dd/yyyy")


Do
strEstimate = InputBox("Please Enter the ESTIMATE Number as YYYY-***", "ESTIMATE NUMBER", Format(Year(Now), "####") & "-nnn")
If Len(strEstimate) = 0 Then Exit Sub
Loop Until strEstimate Like Format(Year(Now), "####") & "-###"
Range("R4:W4").Value = strEstimate


Do
strCustomerID = InputBox("Please Enter the CUSTOMER ID as ***XX-X", "CUSTOMER ID", "Cnnnn-1/2")
If Len(strCustomerID) = 0 Then Exit Sub
Loop Until strCustomerID Like "C####-[1-2]"
Range("E9:K9").Value = strCustomerID


Do
strTaxRate = InputBox("Please enter the TAX RATE for Materials as Decimal Number, Example: .09 or .10", "TAX RATE")
If Len(strTaxRate) = 0 Then Exit Sub
Loop Until CDbl(strTaxRate) >= 0.0725 And CDbl(strTaxRate) <= 0.1
Range("S18:T18").Value = CDbl(strTaxRate)

End Sub

[RalphMHill]

Thanks Paul This works great!