Every time I click on any cell in the sheet the entire code below runs. How can I stop…
- User
Every time I click on any cell in the sheet the entire code below runs. How can I stop this so that the code below only runs when I click on A38
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "38" Then ' Check if cell A38 is selected ' Check if any of the required cells are blank If Range("B3") = "" Or Range("F3") = "" Or Range("B7") = "" Then ' Display an error message and exit the sub MsgBox "Please fill in all required fields before saving." Exit Sub End If ' Concatenate the values of E3, F3, and B7 with a space between each value Dim strConcat As String strConcat = Range("B3").Value & " " & Range("F3").Value & " " & Range("B7").Value Range("B38").Value = strConcat ' Write the concatenated value to B38 ' Define the save path and filename Dim folderPath As String folderPath = "G:\Shared drives\Swan School Whole School\Whole School Resources\Whole School Risk Assessments & H&S Documentation\TSS Risk Assessments - Complete\Premises Risk Assessments (Simon Adenle)\Permits issued" ' Change this to your desired file path Dim fileName As String fileName = strConcat & ".xls" ' Use the concatenated value as the file name ' Save the file and close Excel ThisWorkbook.SaveAs folderPath & fileName 'Application.Quit End If Call PrintRange End Sub
- Assistant
To make the code only run when you click on cell A38, you can modify the code as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "38" Then ' Rest of your code here End If Call PrintRange End Sub
This way, the code will only run when the selected cell is A38.
- User
My code is already setup as you suggested but it still runs when I click outside A38
- Assistant
If the code is still running when you click outside of cell A38, one possible reason could be that the event handler is not properly activated.
To ensure the event handler is activated, follow these steps:
- Right-click on the sheet tab at the bottom of the Excel window.
- Click on "View Code" to open the sheet's code window.
- In the code window, make sure the dropdown at the top left side (known as the object dropdown) is set to "Worksheet".
- Find the event handler code
Worksheet_SelectionChangeand ensure that the code you provided is within this event handler.
If the code is within the
Worksheet_SelectionChangeevent handler and the event handler is properly activated for the sheet, it should only run when the selected cell is A38.