Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable events
Hi...
I'm struggling to refine some code to automate the following. if A1=1 and B1=1 then I want an input box triggered to request a cost value that will go into Z1. The input box must be triggered by placing the 1 into B1 rather than A1. I have a desscending list going down 800 rows by which the input box value must be offset. eg the same must apply to a800, b800 and z800 as it does for all rows above. Anyhelp would be super. John C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable events
Hi John,
you have to process the worksheet_change event. Maybe the following code is of sme help for you: Private Sub Worksheet_Change(ByVal Target As Range) Dim var_input If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub If Target.Value = 1 And Me.Cells(Target.Row, "A").Value = 1 Then On Error GoTo CleanUp: Application.EnableEvents = False var_input = InputBox("Enter your cost value") Me.Cells(Target.Row, "Z").Value = var_input End If CleanUp: Application.EnableEvents = True End Sub Frank John C wrote: Hi... I'm struggling to refine some code to automate the following. if A1=1 and B1=1 then I want an input box triggered to request a cost value that will go into Z1. The input box must be triggered by placing the 1 into B1 rather than A1. I have a desscending list going down 800 rows by which the input box value must be offset. eg the same must apply to a800, b800 and z800 as it does for all rows above. Anyhelp would be super. John C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable events
Hi Frank..
I think I tried to be too simplistic in my example and hoped to configure any helpful code to my exact needs...I can't make it work...How would I make this work in your code.. if cell c29="Credit" and f29=1 then an input box is launched on the 1 being entered into f29 that sends a cost value to cell y29.. My ongoing thank JC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable events
Hi John
try the following Private Sub Worksheet_Change(ByVal Target As Range) Dim var_input If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("F:F")) Is Nothing Then Exit Sub If Target.Value = 1 And Me.Cells(Target.Row, "C").Value = "credit" Then On Error GoTo CleanUp: Application.EnableEvents = False var_input = InputBox("Enter your cost value") Me.Cells(Target.Row, "Y").Value = var_input End If CleanUp: Application.EnableEvents = True End Sub note: You have to enter this code in the specific worksheet (Right click on the worksheet tab and choose "Code") HTH Frank John C wrote: Hi Frank... I think I tried to be too simplistic in my example and hoped to configure any helpful code to my exact needs...I can't make it work...How would I make this work in your code... if cell c29="Credit" and f29=1 then an input box is launched on the 1 being entered into f29 that sends a cost value to cell y29... My ongoing thanks JC |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable events
Hi Frank..
Thanks for your help here. I pasted your code into my VBE and a line showed up red preventing the code from working.. If Target.Value = 1 And Me.Cells(Target.Row, "C").Value = "credit The I'm in Excel 97...does this make any difference.. Thanks again.. JC |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable events
One way:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim result As Variant With Target If .Count = 1 Then If Not Intersect(.Cells, Range("F:F")) Is Nothing Then If IsNumeric(.Value) Then If .Value = 1 And .Offset(0, -3).Value = "Credit" Then result = Application.InputBox( _ Prompt:="Enter cost value", _ Title:="Cost", _ Type:=1) If result < False Then 'False: user clicked Cancel Application.EnableEvents = False .Offset(0, 19).Value = result Application.EnableEvents = True End If End If End If End If End If End With End Sub Where N is your "cost value" In article , John C wrote: Hi Frank... I think I tried to be too simplistic in my example and hoped to configure any helpful code to my exact needs...I can't make it work...How would I make this work in your code... if cell c29="Credit" and f29=1 then an input box is launched on the 1 being entered into f29 that sends a cost value to cell y29... My ongoing thanks JC |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable events
Hi John
this should be one line. I think your newsreader broke it into two lines. So the line should read: IF Target.Value = 1 ........ = "credit" Then HTH Frank John wrote: Hi Frank... Thanks for your help here. I pasted your code into my VBE and a line showed up red preventing the code from working... If Target.Value = 1 And Me.Cells(Target.Row, "C").Value = "credit" Then I'm in Excel 97...does this make any difference... Thanks again... JC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO DO ENABLE ENABLE THE QUERY PARAMETER BUTTON? | Excel Worksheet Functions | |||
events? | Excel Discussion (Misc queries) | |||
Is there a way to turn events on or off? | Excel Programming | |||
events | Excel Programming | |||
events | Excel Programming |