Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to calculate results in one cell, based on data of another | Excel Discussion (Misc queries) | |||
Calculate Value on new cell based on List | New Users to Excel | |||
Calculate the number of weeksdays based on a cell value | Excel Worksheet Functions | |||
IF statement to calculate based on cell values | Excel Worksheet Functions | |||
Calculate value based on a cell and inputbox | Excel Programming |