execute multiplication when data entry

  • Thread starter Thread starter goepf
  • Start date Start date
G

goepf

Hi,

I would like to have Excel multipling a number that I enter in a cel
by a factor 4 or 6 depending on the value of another cell (in the sam
row).
Meaning:
if I enter 1000 in G4 and hit Enter (or move to another cell) a cod
should run and check $B4 and if there is a "1000ML" it should multipl
by 6 and if there is a "1500ML" it should multiply by 4.

Any idea? :confused:

Tank
 
I already tried the

Worksheet_SelectionChange
and
Worksheet_Change

functions but this ends eighter in a loop (calculate) or it changes m
cells always when I move from one to the other...

Any ideas??
 
Goepf,

To use the WorksheetChange function without looping, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Static lockout As Boolean

If lockout Then Exit Sub
lockout = True

'Make your calculations here

lockout = False

End Sub


The 'Static' declaration means that the program 'remembers' the value of the
variable [lockout] between runs of the routine. Making a calculation in this
routine, and writing it to the sheet WILL cause another Change event, as you
have found, which needs to be locked out.

You can also use another technique which will disable the change event.

Application.EnableEvents=False
' Make Calculations here
Application.EnableEvents=True

HTH,
Alex J


goepf said:
I already tried the

Worksheet_SelectionChange
and
Worksheet_Change

functions but this ends eighter in a loop (calculate) or it changes my
cells always when I move from one to the other...

Any ideas???


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Thanks Alex,

Now an additional question:

Is it possible to apply these code only for a section of the Worksheet?
Or do I have to test the "target" location before executing the
calculation?

Thanks so far!
 
geopf,

You will need to test Target to see if it is the right range to make a
change.

Opions would be (among others):

If Target.column = 3 Then .....
or
If Target.address = "$A$12" Then ...
or
(assuming you have a named range "MultRange" on the sheet)

Dim Isect as Range
Dim nmRng as Range
Set NamedRng =Sheets("Sheet1").Range("MultRange")

Set Isect = Intersect(Target, NamedRng)
If Not Isect Is Nothing then
'Do your calculation on the target cell(s) here
End if


Hope this helps.
Alex J

goepf said:
Thanks Alex,

Now an additional question:

Is it possible to apply these code only for a section of the Worksheet?
Or do I have to test the "target" location before executing the
calculation?

Thanks so far!


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top