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?

Sheets() and Window() Objects not working?
 

If the subscript is out of range, it means the value you gave cannot be found
within the existing range. So, your "x" is probably a number which is not
value given the context (scenario) you have.

I suggest you debug using F8 to step through the code and verify the values
as you go along. Alternatively, print them to the Immediate Window.

All I can say is that the value is not within the range, thus the error. So
you need to check the value and make sure it is valid.
--
Robert
Author of RibbonX: Customizing the Office 2007 Ribbon:
LATEST INTERACTIVE TUTORIAL:
http://www.msofficegurus.com/videos/...p_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/

Sheets() and Window() Objects not working?
 

Hi,

I suspect the problem is that it's looking for Sheet1 in the workbook you're
opening, not in your log workbook. Try the following:

Private Sub Workbook_Open()
Dim x As Integer
Dim wb as workbook

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"S:\Daily Reports\Add-ins\MI Log.xls"

set wb = workbooks("MI Log.xls")
x = wb.worksheets("Sheet1").Range("A1").Value
wb.worksheets("Sheet1").Cells(x, 1) = Environ("UserName")
wb.worksheets("Sheet1").Cells(x, 2) = "Daily Figures (All)"
wb.worksheets("Sheet1").Cells(x, 3) = Now()
wb.worksheets("Sheet1").Range("A1") = x + 1

Windows("MI Log.xls").Close savechanges:=True
End Sub

"PaulW" wrote:

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?

Sheets() and Window() Objects not working?
 

Your Macro worked on the sheets part, but the last line to close the workbook
threw up the same error. Thankfully changing it from "Windows" to "Workbooks"
worked, but its still odd.

"Sam Wilson" wrote:

Hi,

I suspect the problem is that it's looking for Sheet1 in the workbook you're
opening, not in your log workbook. Try the following:

Private Sub Workbook_Open()
Dim x As Integer
Dim wb as workbook

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"S:\Daily Reports\Add-ins\MI Log.xls"

set wb = workbooks("MI Log.xls")
x = wb.worksheets("Sheet1").Range("A1").Value
wb.worksheets("Sheet1").Cells(x, 1) = Environ("UserName")
wb.worksheets("Sheet1").Cells(x, 2) = "Daily Figures (All)"
wb.worksheets("Sheet1").Cells(x, 3) = Now()
wb.worksheets("Sheet1").Range("A1") = x + 1

Windows("MI Log.xls").Close savechanges:=True
End Sub

"PaulW" wrote:

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?