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

This code does not react: Private Sub Worksheet_SelectionChange(ByVal Target As Range)…

  1. User

    This code does not react:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim was As Worksheet Set was = ActiveSheet

    If Target = "BB2" Then was.Range("A3").Calculate was.Range("B3").Calculate End If

  2. Assistant

    As an AI language model, I cannot see any "End Sub" to end the code properly. It is missing in this code. Please try adding "End Sub" at the end of the code and see if it works.

  3. User

    With the End Sub, it still does not work. My Workbook is set to manual calculation

  4. Assistant

    If your workbook is set to manual calculation, then you need to modify the code to force the calculation to occur after the worksheet range values have changed. This can be achieved by adding the line of code "Application.Calculate" after the Target = "BB2" line. Here is the updated code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        Dim was As Worksheet
        Set was = ActiveSheet
    
        If Target = "$B$2" Then
            was.Range("A3").Calculate
            was.Range("B3").Calculate
            Application.Calculate
        End If
        
    End Sub
    

    This should force the calculation to occur after the range values have changed.

  5. User

    still does not work

  6. Assistant

    If the code is still not working, it may be due to a few other potential reasons:

    1. The worksheet containing the code is not the active worksheet. Double-check that the code is located in the correct worksheet module.

    2. The cell reference in the "If" statement is not correct. Verify that the cell reference to trigger the calculation is accurate.

    3. Your Excel settings or security settings may be preventing the code from running. Check to make sure that Excel security settings are not too high, and that macros are enabled.

    If none of these solutions work, please provide more context so that we can help you better.

  7. User

    When I use Target to reference a column i.e. If Target.Column = 2, the code works. But when I use Target to reference a cell, the code does not work

  8. Assistant

    If the code works when using Target.Column to reference a column, but not Target to reference a specific cell, it may be because the syntax used for the Target reference is incorrect. To reference a specific cell, you need to use the Address method of the Range object to extract the cell address. Here is an updated version of the code that should work for you:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim was As Worksheet
        Set was = ActiveSheet
        
        If Target.Address = "$B$2" Then
            was.Range("A3").Calculate
            was.Range("B3").Calculate
            Application.Calculate
        End If
    End Sub
    

    This code uses the Target.Address property to check if the target cell address is "BB2" before triggering the calculation.

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