About Us

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Attempted edit of protected cell jumps user to another cell	megangomez	Excel Discussion (Misc queries)	2	August 6th 09 12:54 AM
On save excel jumps to another worksheet. How to fix VBA code?	BillJenk	Excel Programming	2	October 9th 08 10:13 PM
Need VBA Code to Let User Define Active Sheet	zulfer7	Excel Discussion (Misc queries)	3	March 29th 07 02:59 PM
Copy from active sheet and paste into new sheet using info from cell in active	Ingve	Excel Programming	3	January 23rd 06 09:57 PM
Change the interior color of a cell - Code Review	Tiny Tim	Excel Programming	6	December 17th 05 09:49 PM

Menu

About Us