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

 

[Mackeral]

You can use these "Target.Row" or "Target.Col" in the Windows Object "SelectionChange" to find out what cell you are pointing to.

For instance

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 12/6/18 Created, WML

    Target_Row = Target.Row
    Call Name_Put("Data_Calls_Row", Target.Row)
    Call Name_Put("Data_Calls_Col", Target.Column)

End Sub 'Worksheet_SelectionChange

Hers's the code for "Name_Put", "Name_Get" and "Name_Verify" if you want to use something like this:

Function Name_Get(Name_to_Use, _                Optional Return_What As NAMES_Field = nf_Names)
    ' Get a Value from Defined Names.
    ' "Retreave" specified either return the Value or the Formula.
    ' 11/12/09 Added Names_Verify Call.
    ' 4/30/12 Added "Optional_Value" to call. WML
    ' 8/9/13 Changed to Select. WML
    ' 8/30/13 Changed Select to UCase(Name_Get). Wml
    ' 6/26/15 Added Strip_Quotes call & took out Boolean testing. WML
    ' 8/19/16 Fix to make Ouput_Name_Value work. WML
    ' 12/27/16 Change call to use ml_MsgErrXtrl. WML
    ' 10/16/17 Modified for Excel 2016. WML
    ' 4/11/18 Added set initialization for Un_known "Flag_.." to False. WML
    
    ' If the found string is Quoted, then those quotes will be removed.
    
    
    Prog = "Name_Get"
    
    If Name_Verify(Name_to_Use) Then
        ' So Decode Return_What you get
        Select Case Return_What
            Case nf_Names
                Name_Get = Application.Evaluate((ActiveWorkbook.NAMES(Name_to_Use).RefersTo))
            Case nf_Formula
                Name_Get = ActiveWorkbook.NAMES(Name_to_Use)
        End Select
        
    ElseIf Left(Name_to_Use, 5) = "Flag_" Then
        Call Name_Put(Name_to_Use, False)
        Name_Get = False
        
    Else ' Error Mess
        Msg = "This Name was not found in the NAME MANAGER:"
        Call Msg_Err(Prog, Msg, QUOTE(Name_to_Use))


    End If
    
    If Left(Name_Get, 1) = """" Then
        TS = Name_Get
        Name_Get = Un_Bracket(TS)
    End If
    
End Function ' Name_Get()




Sub Name_Put(Name_to_Use, New_Stuff)
    ' Originally named Named_Put.
    ' Added call to verify Name_to_Use. 11/13/09 WML
    ' If called with null New_Data, changed to " ".
    ' 8/8/11 Changed. WML
    ' 10/14/17 Changed for Office . WML
    ' 3/9/18 Added Error Handling code. WML
    ' 4/29/18 Added code for empty string. WML
    ' 11/22/18 Took out Error Code stuff. WML
    
    Prog = "Name_Put"
    
    If New_Stuff = "" Then New_Stuff = """""" ' 4/29/18
    NAMES.Add Name:=Name_to_Use, RefersTo:=New_Stuff
    
End Sub ' Name_Put


Function Name_Verify(Name_To_Verify) As Boolean
    ' Make sure "Name_To_Verify" is in NAMES in the ActiveWorkbook.
    ' 9/19/17 New Code from _
      "stackoverflow.com/questions/40488819/vba-find-names-in-activeworkbook-names"
    ' 12/8/17 Reworked. WML
    
    On Error Resume Next
    Set Check = ActiveWorkbook.NAMES(Name_To_Verify)
    On Error GoTo 0


    Name_Verify = Not IsEmpty(Check)
        
End Function ' Name_Verify



Public Enum NAMES_Field ' 10/27/17WML
    nf_Names = 1
    nf_Formula = 2
End Enum

 

[rlv01]

You can use these "Target.Row" or "Target.Col" in the Windows Object "SelectionChange" to find out what cell you are pointing to.

Are you aware you are responding to a 10 year old thread?