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

I want to create a global variable 'selectedCellAddress' in a VBA module that holds the…

  1. User

    I want to create a global variable 'selectedCellAddress' in a VBA module that holds the Target.Address and changes depending on the slecetedCellAddress.

    It must capture the selectedCellAddress from this event; Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row > 1 Then Cells.Interior.ColorIndex = 0 Target.EntireRow.Interior.ColorIndex = 6 selectedCellAddress = Target.Address End If

    How can I do this

  2. Assistant

    To create a global variable in a VBA module, first declare it as a public variable before any sub or function:

    Public selectedCellAddress as String

    Then, in the Worksheet_SelectionChange event, assign the value of Target.Address to the global variable:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row > 1 Then Cells.Interior.ColorIndex = 0 Target.EntireRow.Interior.ColorIndex = 6 selectedCellAddress = Target.Address End If End Sub

    Now, the global variable 'selectedCellAddress' will hold the Target.Address and will change depending on the selected cell address.

  3. User

    When I create the module, what code goes into it

  4. Assistant

    If you want to create a module that holds the global variable 'selectedCellAddress', you simply need to add the following code to a new module in your VBA project:

    Public selectedCellAddress As String

    That's it! This code will create the public global variable 'selectedCellAddress' that you can use in any part of your VBA project. Just make sure that you don't declare this variable again in any other module or procedure, so it remains global.