Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets() and Window() Objects not working?
First off, the macro:
Private Sub Workbook_Open() Dim x As Integer Application.ScreenUpdating = False Workbooks.Open Filename:= _ "S:\Daily Reports\Add-ins\MI Log.xls" x = Sheets("Sheet1").Range("A1").Value Sheets("Sheet1").Cells(x, 1) = Environ("UserName") Sheets("Sheet1").Cells(x, 2) = "Daily Figures (All)" Sheets("Sheet1").Cells(x, 3) = Now() Sheets("Sheet1").Range("A1") = Sheets("Sheet1").Range("A1").Value + 1 Windows("MI Log.xls").Close savechanges:=True End Sub In an effort to activily track which Management Information reports are being used, who by, and how often, my idea was to add this bit of code into every report. The idea being that every time someone opens a report the macro triggers, opens the "MI Log.xls" and adds three peices of information, the name of the person, which report they're using, and the time. X is so I don't need to find a new line, and in A1 the spreadsheet has a number showing the next new line. The problem I have, is that this code gives two errors, one on the Sheets and one on the Windows: Run-Time Error '9' Subscript out of range I can remove the sheets, and hope in the future I don't want to make use of the other sheets in MI Log.xls and forget to put it back to sheet one, but i'm against using ActiveWindow.close as that has produced problems for me before. Can anyone think why these errors might be occuring? And how to fix them? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot shift objects off sheets | Excel Worksheet Functions | |||
right click on objects not working | Excel Discussion (Misc queries) | |||
VBA & XL2K: Working with objects/class modules | Excel Programming | |||
Working with Comment objects in VBA | Excel Programming | |||
Working with line OLE objects | Excel Programming |