RalphMHill
02-02-2017, 03:26 PM
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.
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.