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

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

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

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

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

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

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