I have two sheets , one called 'PREQUEST' the other called 'PORDER' In the following…
- User
I have two sheets , one called 'PREQUEST' the other called 'PORDER'
In the following Events, they both have this identical code;
Private Sub Worksheet_Activate() activeCellAddress = ActiveCell.Address Dim prevSheet As Worksheet Dim sheetNames As Variant sheetNames = Array("PREQUEST", "PORDER") Set prevSheet = Worksheets(Me.index - 1) If Not IsError(Application.Match(prevSheet.Name, sheetNames, 0)) Then On Error Resume Next ActiveSheet.Range(selectedCellAddress).Select selectedCellAddress = "" On Error GoTo 0 End If End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Application.EnableEvents = True If Target.Row > 1 Then Cells.Interior.ColorIndex = 0 'Reset all cells to no color Target.EntireRow.Interior.ColorIndex = 6 'Highlight active row selectedCellAddress = Target.Address End If End Sub
I also have within the same workbook, Module2 that has this line of code: Public selectedCellAddress As String
If I select a Row Cell in sheet 'PREQUEST' and activate sheet 'PORDER', the row I was previously at in sheet 'PREQUEST' is selected in sheet 'PORDER'
However, if I select a Row Cell in sheet 'PORDER' and activate sheet 'PREQUEST', the row I was previously at in sheet 'PORDER' is not selected in sheet 'PREQUEST'
What is causing this error.
- Assistant
The issue may be with the variable names used in the code. In the Worksheet_Activate event, the variable activeCellAddress is used to store the address of the previously selected cell, but in the Worksheet_SelectionChange event, the variable selectedCellAddress is used to store the address of the currently selected cell.
To fix the issue, you can make sure that the same variable name is used consistently throughout the code. For example, you can change the variable name in the Worksheet_SelectionChange event to activeCellAddress.
Additionally, make sure that the variable is declared at the module level, so that it can be accessed by both worksheets. You can remove the line "Public selectedCellAddress As String" from Module2 and add it to the top of each worksheet module, before any Sub procedures.
Finally, you can add some error handling code to account for cases where there is no previously selected cell. You can modify the code in the Worksheet_Activate event as follows:
activeCellAddress = ActiveCell.Address Dim prevSheet As Worksheet Dim sheetNames As Variant sheetNames = Array("PREQUEST", "PORDER") Set prevSheet = Worksheets(Me.index - 1) If Not IsError(Application.Match(prevSheet.Name, sheetNames, 0)) Then On Error Resume Next If Not activeCellAddress = "" Then ActiveSheet.Range(activeCellAddress).Select End If activeCellAddress = "" On Error GoTo 0 End If
This code checks whether activeCellAddress is empty before trying to select the previously selected cell.