Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

in vba how can i write a module variable to record last Target

  1. User

    in vba how can i write a module variable to record last Target

  2. Assistant

    To record the last target in VBA, you can create a module level variable and use the Worksheet_SelectionChange event to update it. Here's an example:

    1. Open the VBA editor by pressing Alt + F11.
    2. In the project explorer window, find the worksheet where you want to record the last target and double-click on it.
    3. In the code window for the worksheet, enter the following code:
    Private lastTarget As Range
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Set lastTarget = Target
    End Sub
    
    1. Save your work and close the VBA editor.

    Now, whenever you select a cell or range in the worksheet, the lastTarget variable will be updated with the selected range. You can use this variable in your code as needed.

  3. User

    How do I create a Public Module for LastTarget

  4. Assistant

    To create a public module for the last target in VBA, follow these steps:

    1. Open the VBA editor by pressing Alt + F11.
    2. In the project explorer window, right-click on your workbook name and select "Insert" > "Module". This will create a new module in your workbook.
    3. In the new module's code window, enter the following code:
    Public lastTarget As Range
    
    Public Sub UpdateLastTarget(ByVal Target As Range)
        Set lastTarget = Target
    End Sub
    
    1. Save your work.

    Now, you can access the lastTarget variable from any module or worksheet within your VBA project. To update the lastTarget value, call the UpdateLastTarget subroutine and pass the new target range as an argument. For example:

    Sub ExampleUsage()
        Dim rng As Range
        Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1")
        UpdateLastTarget rng
    End Sub
    

    In the above example, the lastTarget variable will be updated with the range "A1" in the worksheet "Sheet1".

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.