ExcelBanter - View Single Post - VBA-User says active cell jumps to a different sheet upon Save. Please review my code

Royzer

Hi. We are using a shared workbook in Excel 2003. There are 32 sheets in the wb being used by 7 people at various times. The layouts of the sheets are identical. The three routines in the code set frames on each worksheet at B9, hide two columns when printing, and inserts a row and copies down the formulas in columns O, P and Q when a cell is double-clicked. Columns M-Q of each sheet are used only by one particular individual. Ever since I added the code below to the wb, this user has reported that occasionally when she saves the file (which she does every time she completes data entry for a sheet) the active cell has changed to B9 on a difference sheet from the one she was on when she saved. I have not witnessed this, but since she reports B9 as the destination cell when this occurs (the same cell the frames are set to), it can't be coincidental. But I can't see any reason for this to happen (of course, I am a beginner at VBA.) My file is too big to upload, though--even zipped. Thanks for your help!

Code:

Private Sub Workbook_Open()
'
' Auto_Open Macro
' set freeze panes when file opens



    Application.ScreenUpdating = False

    Sheets("KAKE").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("KBTX").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("KKCO").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("KKTV").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("KOLN").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("KOLO").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("KWTX").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("KXII").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("TV3").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("WBKO").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("WCAV").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("WCTV").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("WEAU").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("WHSV").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WIBW").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WIFR").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WILX").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WITN").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WJHG").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WKYT").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WMTV").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WNDU").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WOWT").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WRDW").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WSAW").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WSAZ").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WSWG").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WTAP").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WTOK").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WTVY").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WVLT").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    Sheets("WYMT").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("GIM").Select
    Range("B9").Select
    ActiveWindow.FreezePanes = True
    
    Sheets("WIBW").Select
    Range("a1").Select
    
    Application.ScreenUpdating = True
      
End Sub

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Cancel = True

    On Error GoTo Error_Handler
    Application.EnableEvents = False
    Range("K:K,L:L").EntireColumn.Hidden = True
    ActiveSheet.PrintOut
    Range("K:K,L:L").EntireColumn.Hidden = False

Clean_Exit:
    Application.EnableEvents = True
    Exit Sub

Error_Handler:
    MsgBox Err & " - " & Err.Description
    GoTo Clean_Exit

End Sub

Code:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With Target
        .Offset(1).EntireRow.Insert
        .EntireRow.Copy .Offset(1).EntireRow(1)
        With .Offset(1).EntireRow
            .Cells(1).Resize(, 14).ClearContents
            On Error Resume Next
            .SpecialCells(2).ClearContents
            On Error GoTo 0
        End With
    End With
End Sub