Calculate value based on a cell and inputbox

[Guest]

Hello all,

I have a quoting sheet and I want to be able to prorate # of months an item
will be covered by warranty based on a date. What I have is 30 lines so I
would want an inputbox to open if the user inserts a Y in the "C" Column. The
inputbox would ask for a date and it would take that date and subtract it
from a cell that has a different date in it. Thus giving me an answer. I'm
sure this possible, just don't know the proper syntax. I was going to start
of with something like:


Private Sub Worksheet_Changes(ByVal Target As Range)
Dim ans As String
If Target.Address = Range("$C$25", "$C$54") And Target.Value = "Y" Then
With Worksheets("agreement")
ans = InputBox("What is the warranty expiration date #", "Prorate months",
Sheets("Agreement").ActiveCell.Value)
-then the "ans" would be subtracted from cell $L$11-

Can this be done?

P.S. I already have a Private Sub Worksheet_Change(ByVal Target As Range)
being used for a different function, can I add another one to avoid conflict
by adding an "s" to the end to make it Private Sub Worksheet_Changes(ByVal
Target As Range)?

Thanks.

 

[Guest]

Brad,
You cannot add an 's' to Worksheet_Change so you must put both
(or more) sets of logic in the one routine. The code below gives an outline
solution.

I changed the the inputbox range reference (to $a$1 for testing) as your
original reference to Activecell does (did) not work. [Your active cell is
Target].

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans As String, DteDiff As Integer

If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then
If UCase(Target.Value) = "Y" Then ' allow for Y and y
With Worksheets("agreement")
ans = InputBox("What is the warranty expiration date #", "Prorate
months", _
Sheets("Agreement").Range("$A$1").Value)
DteDiff = Range("$L$11") - DateValue(ans)
MsgBox DteDiff
End With
End If
Else
If Target.Address = "$B$1" Then ' your original logic goes here .....
MsgBox " Add logic here.."
End If

End If
End Sub


HTH

 

[Guest]

What if I change where the user enters "Y", to say the "B" column. How can I
then have the "Ans" from the input box entered into the "C" column? Would an
offset be possible?

Brad,
You cannot add an 's' to Worksheet_Change so you must put both
(or more) sets of logic in the one routine. The code below gives an outline
solution.

I changed the the inputbox range reference (to $a$1 for testing) as your
original reference to Activecell does (did) not work. [Your active cell is
Target].

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans As String, DteDiff As Integer

If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then
If UCase(Target.Value) = "Y" Then ' allow for Y and y
With Worksheets("agreement")
ans = InputBox("What is the warranty expiration date #", "Prorate
months", _
Sheets("Agreement").Range("$A$1").Value)
DteDiff = Range("$L$11") - DateValue(ans)
MsgBox DteDiff
End With
End If
Else
If Target.Address = "$B$1" Then ' your original logic goes here .....
MsgBox " Add logic here.."
End If

End If
End Sub


HTH

Hello all,

I have a quoting sheet and I want to be able to prorate # of months an item
will be covered by warranty based on a date. What I have is 30 lines so I
would want an inputbox to open if the user inserts a Y in the "C" Column. The
inputbox would ask for a date and it would take that date and subtract it
from a cell that has a different date in it. Thus giving me an answer. I'm
sure this possible, just don't know the proper syntax. I was going to start
of with something like:


Private Sub Worksheet_Changes(ByVal Target As Range)
Dim ans As String
If Target.Address = Range("$C$25", "$C$54") And Target.Value = "Y" Then
With Worksheets("agreement")
ans = InputBox("What is the warranty expiration date #", "Prorate months",
Sheets("Agreement").ActiveCell.Value)
-then the "ans" would be subtracted from cell $L$11-

Can this be done?

P.S. I already have a Private Sub Worksheet_Change(ByVal Target As Range)
being used for a different function, can I add another one to avoid conflict
by adding an "s" to the end to make it Private Sub Worksheet_Changes(ByVal
Target As Range)?

Thanks.