VBA syntax

[Derrick]

how do i select the current cell im in .. in vba
lets say, im in B3 and edit the value - im working on a code that will say:
- in Row 3, check and recalculate D3 if necessary(which uses VBA to create a
validated list based on a loop... so i can't just use formulas)
if in row 4, check and recalculate D4.

so.. i need to be able to get the number of the row im in.. so i can set it
to a variable and go from there

any help?

 

[Jacob Skaria]

Msgbox Activecell.Row
Activecell.Column
Activecell.Address

If this post helps click Yes

 

[Derrick]

thanks Jacob
im not quite sure what it all means.. can you explain what is happening with
msgbox, and the other .Column, .Address lines?
... so i can know how to edit it later

 

[Jacob Skaria]

Activecell
refers to the current cell in VBA.

ActiveCell.Address
returns the current cell address

ActiveCell.Row
returns the current row and similarly column..

If this post helps click Yes

 

[Derrick]

hi again. i've figured it out mostly... but now im wondering if i can not
use the active cell.. but the cell i've just edited... because if i press
enter, it gives me the active cell row below the cell i just edited
is this possible?

 

[Derrick]

hi again. thanks for the explanation! now im wondering if i can:
not use the active cell.. but the cell i've just edited...
because if i press enter, it gives me the active cell row below the cell i
just edited
if i press tab im still ok. but i dont wanna have that possibility
is this achievable?

 

[Jacob Skaria]

Right click the sheet tab> View code> and paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address & vbCrLf & Target.Row
End Sub

The worksheet change event will be fired as soon as you change a field
value.. Target refers to the cell you are in. Target.Row refers to the row
and similarly column..Target.Value refers to the value you have typed in

If this post helps click Yes