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

When I click on a cell, I get a pop up message which I have to click OK to close. How…

  1. User

    When I click on a cell, I get a pop up message which I have to click OK to close. How can I use VBA to remember the cell I clicked on before the message so that I can use it as a Target value

  2. Assistant

    You can achieve this by using a global variable in VBA to store the address or value of the last clicked cell. Here's an example:

    1. Press ALT + F11 to open the VBA editor in Excel.
    2. Insert a new module by clicking "Insert" > "Module".
    3. In the module, declare a global variable to store the last clicked cell:
    Public lastClickedCell As Range
    
    1. Next, you'll need to write a macro that assigns the clicked cell to the global variable and displays the pop-up message. Here's an example:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ' Store the clicked cell in the global variable
        Set lastClickedCell = Target
        
        ' Display the pop-up message
        MsgBox "You clicked on cell " & lastClickedCell.Address
    End Sub
    
    1. Save the workbook as a macro-enabled (.xlsm) file.

    Now, whenever you click on a cell, the Worksheet_SelectionChange event will be triggered. It will store the clicked cell in the lastClickedCell global variable and display a pop-up message containing the address of the clicked cell.

    You can then use the lastClickedCell variable elsewhere in your VBA code as needed, such as referencing its value or address for further processes.

  3. User

    After using the variable LastClickedCell, how do I clear the variable

  4. Assistant

    To clear the value stored in the lastClickedCell variable, you can set it to Nothing. Here's an example of how to clear the variable:

    Set lastClickedCell = Nothing
    

    By setting it to Nothing, the variable will no longer reference any cell, effectively clearing the stored value. Make sure to place this code at the appropriate position in your VBA code where you want to clear the variable.