Enable events

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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"
 
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
 
Back
Top