When I click on a cell, I get a pop up message which I have to click OK to close. How…
- 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
- 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:
- Press ALT + F11 to open the VBA editor in Excel.
- Insert a new module by clicking "Insert" > "Module".
- In the module, declare a global variable to store the last clicked cell:
Public lastClickedCell As Range- 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- Save the workbook as a macro-enabled (.xlsm) file.
Now, whenever you click on a cell, the
Worksheet_SelectionChangeevent will be triggered. It will store the clicked cell in thelastClickedCellglobal variable and display a pop-up message containing the address of the clicked cell.You can then use the
lastClickedCellvariable elsewhere in your VBA code as needed, such as referencing its value or address for further processes. - User
After using the variable LastClickedCell, how do I clear the variable
- Assistant
To clear the value stored in the
lastClickedCellvariable, you can set it toNothing. Here's an example of how to clear the variable:Set lastClickedCell = NothingBy 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.