switching to worksheet exit/enter code instead of change sheet code.

[omairhe]

Hey everyone,

The following code helps in arranging the columns across multiple sheets to be same as active sheet when enter is pressed on a cell. Code being perfect however is tedious due to repetitive pressing of the Enter Key. Instead I could think of whenever the active sheet is exited and a new sheet is selected then run the code only for the new sheet. hence, the new sheet will now have the same column selected as the last sheet with even same positioning of the scrolling aswell. for mroe reference please refer to the link of my OP

https://www.mrexcel.com/forum/excel-questions/1083556-select-same-column-across-multiple-sheets.html

[COLOR=#333333][I]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)[/I][/COLOR]    Dim ws As Worksheet, c As Long
    c = ActiveWindow.VisibleRange.Column
    Application.ScreenUpdating = False
    For Each ws In Sheets
        ws.Select
        Cells(ActiveCell.Row, Source.Column).Select
        ActiveWindow.SmallScroll ToLeft:=Columns.Count
        ActiveWindow.SmallScroll ToRight:=c - 1
    Next
    Sh.Select
    Application.ScreenUpdating = True [COLOR=#333333][I]End Sub[/I][/COLOR]

Would it be possible?

Thank you.

 

[Norie]

I suppose you could use the SheetDeactivate event.

Something like this perhaps.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ws As Worksheet
Dim cl As Range

    Set cl = ActiveCell
    
    Application.EnableEvents = False

    For Each ws In ThisWorkbook.Sheets
        Application.Goto ws.Range(cl.Address), True
    Next ws

    Application.Goto sh.Range(cl.Address), True

    Application.EnableEvents = True

End Sub

 

[omairhe]

I suppose you could use the SheetDeactivate event.

Something like this perhaps.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ws As Worksheet
Dim cl As Range

    Set cl = ActiveCell
    
    Application.EnableEvents = False

    For Each ws In ThisWorkbook.Sheets
        Application.Goto ws.Range(cl.Address), True
    Next ws

    Application.Goto sh.Range(cl.Address), True

    Application.EnableEvents = True

End Sub

When I tried the code it would bring me back to my previous sheet and cell A1 gets selected everytime. Thus I am unable to select a new sheet. Do you think MS office version has to do something with this behavior? I am using version 2019..

 

[Norie]

No it's my fault.

Try removing this line.

  Application.Goto sh.Range(cl.Address), True

 

[omairhe]

No it's my fault.

Try removing this line.

  Application.Goto sh.Range(cl.Address), True

Now, once the code is run it will still select cell A1. But this time around it takes me to the last sheet of my workbook. From there on I am unable to select other sheets in my workbook.

 

[Norie]

Well I'm afraid I can't think of another way to do this - Sh is the sheet that you are deactivating, there's nothing to reference the sheet you are activating.

Vice versa, if you tried the SheetActivate event Sh would refer to the sheet being activated and there would be no reference to the sheet you are deactivating.

 

[omairhe]

Well I'm afraid I can't think of another way to do this - Sh is the sheet that you are deactivating, there's nothing to reference the sheet you are activating.

Vice versa, if you tried the SheetActivate event Sh would refer to the sheet being activated and there would be no reference to the sheet you are deactivating.

That is at least good to know that it can't be done otherwise so I will have to stop looking else where and stick with my previous code.
Thank you for explaining it to me.

 

[Norie]

I'm not saying it can't be done, only that right now I can't think of a way to do it.

 

[omairhe]

I'm not saying it can't be done, only that right now I can't think of a way to do it.

What if we create a hidden helper sheet by name = Column Ref
Now when enter is pressed on active sheet the column reference is copied to the "Column Ref" sheet inside cell A1.

Now when we exit the active sheet . The column reference will be in A1 of "Column Ref"

Will that work?