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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
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 |