Calculate value based on a cell and inputbox
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. |
Calculate value based on a cell and inputbox
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 "Brad" wrote: 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. |
Calculate value based on a cell and inputbox
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? "Toppers" wrote: 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 "Brad" wrote: 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. |
All times are GMT +1. The time now is 12:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com