VBA Target.Address

[shamas21]

Hi All

Can someone explain what the Taget.Address relates to in VBA. I cant figure it out, how can this be used? thanks

 

[VoG]

Here's an example which goes in a sheet's code module (right click the tab > View code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A10" Then
    MsgBox "You can't go there!!"
    Range("A1").Select
End If
End Sub

 

[shamas21]

Here's an example which goes in a sheet's code module (right click the tab > View code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A10" Then
    MsgBox "You can't go there!!"
    Range("A1").Select
End If
End Sub

Hi VOG

This doesnt seem to work. I have put this into 'Thisworkbook' but i can click anywhere within the sheet and allows me to input data

 

[lenze]

Hi Shamas!

The address property simply identifies the location of the Target. Note that Target may be more than 1 cell.
You can use it to isolate a cell for macro execution. Example

If Target.Address  = "$A$1" Then

or

If Target.Addres <> "$A$1" Then Exit Sub

You can use this property in a messagebox

MsgBox "Value of " & Target.Address & " is " & Target.Value

Note that Target.Address is ALWAYS an absolute reference and in Caps.

So Target.Address ="A1" or Target.Address = "$a$1" will not work

lenze

Edit: Vog's code should go in the WorkSheet module, not the ThisWorkBook module

 

[VoG]

It goes in the sheet's code module. Right click the sheet tab and select View Code.

 

[shamas21]

Ok guys thanks, it makes sence

 

[shamas21]

Here's an example which goes in a sheet's code module (right click the tab > View code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A10" Then
    MsgBox "You can't go there!!"
    Range("A1").Select
End If
End Sub

VOG what does the False, False signify?
Target.Address (False, False)

 

[shamas21]

Hi Shamas!

The address property simply identifies the location of the Target. Note that Target may be more than 1 cell.
You can use it to isolate a cell for macro execution. Example

If Target.Address  = "$A$1" Then

or

If Target.Addres <> "$A$1" Then Exit Sub

You can use this property in a messagebox

MsgBox "Value of " & Target.Address & " is " & Target.Value

Note that Target.Address is ALWAYS an absolute reference and in Caps.

So Target.Address ="A1" or Target.Address = "$a$1" will not work

lenze

Edit: Vog's code should go in the WorkSheet module, not the ThisWorkBook module

Lenze,

So can the address property be used in another way within a module ?

 

[VoG]

Target.Address will by default return an absolute address like $A$1 as lenze said.

Target.Address(False, False) makes both the row and column references relative.

 

[Norie]

shamas

Try highlighting Address in the code and hitting F1.

That should give you context sensitive help for the property.