Specifying cell in Worksheet_Change even

[Rodney Crow]

I have a macro (Conversion) that runs when a specific cell, say "F6" is
changed from a blank cell to a given value using a dropdown box. The macro
does a conversion based on the choice, and puts the answer in "H6".
Obviously, to make it work for the whole sheet (down each row), I need to
pass the row value to the macro, but I'm drawing a blank as to how to do
this. I know this is probably an easy one, but could someone please help me?

 

[Gary''s Student]

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("F6:F65536")
Set t = Target
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 2).Value = t.Value * 2
Application.EnableEvents = True
End Sub

Instead of looking at F6, we look at a much larger range. Instead of
changing H6 we change the cell in column H corresponding to the cell changed
in column F

 

[Rodney Crow]

Thank you so much! I'll try it and see how well it works!

 

[Rodney Crow]

Okay, being a newbie at this, where would it call "Conversion"?

 

[Gary''s Student]

In place of:

t.Offset(0, 2).Value = t.Value * 2

have a line like:

Call Conversion(t)

1. Put Conversion in a standard module, not in the worksheet code area

2. Since Conversion has the target address, it can retrieve the value that
was just changed. It can apply its logic and modify t.Offset(0,2) itself.

 

[Rodney Crow]

Thank you for all your help. This is my first project involving a
self-written macro, and I cant get this to work. I still have alot more to
learn apparently!

 

[Rodney Crow]

Okay, maybe I'm not explaining enough about the macro. The macro
(Conversion) is activated when a dropdown box is clicked. Based upon that
choice, "Conversion" converts the choice for comparison (in this case, cost
per ounce). My code in "Conversion" uses If/Then/ElseIf statements based on
the cell that is clicked. For example:

If Cells(Row, "F").Value = " " Then
Cells(Row, "H").Value = "0.00 "
ElseIf Cells(Row, "F").Value = "BAG" Then
myVar = Val(InputBox("What is the size of the bag in pounds?", "Ounces"))
Cells(Row, "H").Value = Cells(Row, "G").Value / (myVar * 16)

and so on and so forth.

So what I need to do, is when say cell "f6" is clicked, it sends the row
value to my macro as the declared integer "Row" so that it will work for
every cell. I know there are probably many reading this that are rolling
their eyes at my brainlessness, but as I said, this is my first trial with
VBA.

Thank you all in advance for your help and suggestions.

 

[Gary''s Student]

It will not matter if F6 ( or any of F6 thru F65536) is changed thru direct
typing or via a data validation dropdown.

In the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("F6:F65536")
Set t = Target
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call Conversion(t)
Application.EnableEvents = True
End Sub

and in a standard module:

Sub Conversion(t)
Roww = t.Row
If Cells(Roww, "F").Value = " " Then
Cells(Roww, "H").Value = "0.00 "
End If
End Sub

Note that we give Conversion a range. It gets the row number and puts it in
the variable Roww.

 

[Rodney Crow]

Thank you so much. That one works AND i even understand HOW it works this
time!!

 

[Dave Peterson]

Actually, there's a bug in xl97 that stops the worksheet_change event from
firing under certain circumstances.

Debra Dalgleish has notes:
http://contextures.com/xlDataVal08.html#Change