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

In my sheet 'PREQUEST' I would like to double click on The company Name in column A then…

  1. User

    In my sheet 'PREQUEST' I would like to double click on The company Name in column A then using the company Name sheet which is column K on the same row, get all the values of Column O from the sheet name in a popup message

  2. Assistant

    To achieve this, you can use a VBA code that runs when you double-click on a cell in column A of the 'PREQUEST' sheet. Here's an example of how the code can be implemented:

    1. Right-click on the sheet tab for 'PREQUEST' and select "View Code".
    2. In the code window that opens, paste the following VBA code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim ws As Worksheet
        Dim companyName As String
        Dim companySheet As Worksheet
        Dim popupMessage As String
        Dim lastRow As Long
        Dim i As Long
    
        ' Check if the double-click occurred in column A
        If Target.Column = 1 Then
            ' Get the company name
            companyName = Target.Value
            
            ' Find the company sheet by name
            On Error Resume Next
            Set companySheet = ThisWorkbook.Sheets(companyName)
            On Error GoTo 0
            
            ' If the company sheet exists
            If Not companySheet Is Nothing Then
                lastRow = companySheet.Cells(companySheet.Rows.Count, "K").End(xlUp).Row
                
                ' Get all the values of Column O
                For i = 1 To lastRow
                    popupMessage = popupMessage & companySheet.Range("O" & i).Value & vbCrLf
                Next i
                
                ' Show the popup message with the values
                MsgBox popupMessage, vbInformation, "Column O values for " & companyName
            Else
                MsgBox "Company sheet not found.", vbExclamation
            End If
            
            Cancel = True ' Prevents entering edit mode on double-click
        End If
    End Sub
    
    1. Save the workbook and close the VBA editor.

    Now, when you double-click on a cell in column A of the 'PREQUEST' sheet, the code will:

    • Get the company name.
    • Check if a sheet with that name exists in the workbook.
    • If the sheet exists, it will retrieve all the values from Column O in that sheet and store them in a popup message.
    • Display the popup message with the values of Column O for the selected company.

    Remember to save your workbook as a macro-enabled file format (.xlsm) to retain the VBA code.

  3. User

    Can you amend this code so that if there are no values in column O, the message will read 'No Fixed Rates for '& companyName

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim ws As Worksheet Dim companyCode As String Dim companySheet As Worksheet Dim popupMessage As String Dim lastRow As Long Dim i As Long ' Check if the double-click occurred in column A If Target.Column = 1 Then ' Get the company name companyCode = Target.Offset(0, 10).Value ' Find the company sheet by name On Error Resume Next Set companySheet = ThisWorkbook.Sheets(companyCode) On Error GoTo 0 ' If the company sheet exists If Not companySheet Is Nothing Then lastRow = companySheet.Cells(companySheet.Rows.Count, "O").End(xlUp).Row ' Get all the values of Column O For i = 1 To lastRow popupMessage = popupMessage & companySheet.Range("O" & i).Value & vbCrLf Next i ' Show the popup message with the values MsgBox popupMessage, vbInformation, "Fixed Rates for " & companyName Else MsgBox "Company sheet not found.", vbExclamation End If Cancel = True ' Prevents entering edit mode on double-click End If End Sub

  4. Assistant

    Certainly! Here's the amended code that includes the message for when there are no values in column O:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim ws As Worksheet
        Dim companyCode As String
        Dim companySheet As Worksheet
        Dim popupMessage As String
        Dim lastRow As Long
        Dim i As Long
    
        ' Check if the double-click occurred in column A
        If Target.Column = 1 Then
            ' Get the company code from column K
            companyCode = Target.Offset(0, 10).Value
            
            ' Find the company sheet by name
            On Error Resume Next
            Set companySheet = ThisWorkbook.Sheets(companyCode)
            On Error GoTo 0
            
            ' If the company sheet exists
            If Not companySheet Is Nothing Then
                lastRow = companySheet.Cells(companySheet.Rows.Count, "O").End(xlUp).Row
                
                ' Check if column O has values
                If lastRow > 0 Then
                    ' Get all the values of Column O
                    For i = 1 To lastRow
                        popupMessage = popupMessage & companySheet.Range("O" & i).Value & vbCrLf
                    Next i
                    
                    ' Show the popup message with the values
                    MsgBox popupMessage, vbInformation, "Fixed Rates for " & companyCode
                Else
                    MsgBox "No Fixed Rates for " & companyCode, vbInformation
                End If
            Else
                MsgBox "Company sheet not found.", vbExclamation
            End If
            
            Cancel = True ' Prevents entering edit mode on double-click
        End If
    End Sub
    

    In this updated code, after finding the company sheet, it checks if the last row of column O is greater than 0. If it is, it retrieves and displays the values as before. However, if the last row is 0, it displays a message stating "No Fixed Rates for [companyName]."