Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

I would like to double click on any cell in the range D5 to D500 and when I do so I want…

  1. User

    I would like to double click on any cell in the range D5 to D500 and when I do so I want the value in Column L of the same row to be displayed in a Pop Up message.

  2. Assistant

    You can achieve this by using Google Apps Script in Google Sheets. Follow these steps:

    1. Open your Google Sheets file.
    2. Click on "Extensions" in the top menu, then select "Apps Script."
    3. Delete any existing code and replace it with the following script:
    function onEdit(e) {
      var sheet = e.source.getActiveSheet();
      var range = e.range;
      
      // Check if the edited range falls within the specified range
      if (range.getColumn() == 4 && range.getRow() >= 5 && range.getRow() <= 500) {
        var row = range.getRow();
        var lCellValue = sheet.getRange('L' + row).getValue();
        Browser.msgBox('Value in Column L:', lCellValue);
      }
    }
    
    1. Save the script by clicking on the floppy disk icon or pressing Ctrl + S.
    2. Close the Apps Script editor.
    3. Now, whenever you double click on any cell within the range D5 to D500, a pop-up message will display the value in Column L of the same row.

    Note: This script uses the onEdit trigger, so it will only work when the cells are manually edited, not when values are changed through formulas or scripts.

  3. User

    Can you provide me the code in VBA for excel

  4. Assistant

    Certainly! Here's the VBA code to achieve the same functionality in Excel:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim rng As Range, cl As Range
        
        'Set the range to D5:D500
        Set rng = Me.Range("D5:D500")
        
        'Check if the double-clicked cell is within the specified range
        If Not Intersect(Target, rng) Is Nothing Then
            'Get the value in Column L of the same row as the double-clicked cell
            Set cl = Me.Cells(Target.Row, "L")
            
            'Display the value in a pop-up message
            MsgBox "Value in Column L: " & cl.Value
        End If
    End Sub
    

    Follow these steps to implement the code:

    1. Open your Excel workbook.
    2. Press Alt+F11 to open the Visual Basic Editor.
    3. In the Project Explorer window, locate and double-click the worksheet in which you want to apply this functionality.
    4. Paste the code into the worksheet module.
    5. Close the Visual Basic Editor.
    6. Now, whenever you double-click on any cell within the range D5 to D500 in that worksheet, a pop-up message will display the value in Column L of the same row.

    Note: This code should be placed in the worksheet module, not in a regular module.

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