In my sheet 'PREQUEST' I would like to double click on The company Name in column A then…
- 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
- 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:
- Right-click on the sheet tab for 'PREQUEST' and select "View Code".
- 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- 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.
- 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
- 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 SubIn 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]."