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

Royzer

Board Regular
Joined
Jun 22, 2010
Messages
51
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.) 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
 

Forum statistics

Threads
1,226,887
Messages
6,193,513
Members
453,804
Latest member
Daniel OFlanagan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top