Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
In our office we have machines running both xl2000 and xl2003. The machine I
work on is 2000 and I have programmed some macros to make sheets very hidden. Other macros reference these sheets and do calculations without having to unhide them. However, when running the application on xl2003, the sheets are hidden properly, but when the calculating macros try to reference them error 9 occurs (out of range). Any ideas as to why? code (the following is just one of the places in the code that gets hung up. I've got a lot of macros that are stopping because of this) ----------------------------------------------------------- Private Sub ButtonGetMiles_Click() Application.ScreenUpdating = False Dim ChartRange As Object Dim Loc1 As Object Dim Loc2 As Object Dim DistanceMiles As Variant Dim DistanceKm As Variant Dim TravelTime As Variant ***the code stops with the following line \/ \/ \/ ***** With Workbooks("Login (1.0)").Sheets("Mileage Info") Set ChartRange = .Range("B2:FT176") Set Loc1 = .Range("A201") Set Loc2 = .Range("A204") End With DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, Loc2) DistanceKm = DistanceMiles * 5 / 3 TravelTime = DistanceMiles / 60 If ComboLoc1.Value = "" Then MsgBox "Please select a starting location." ComboLoc1.SetFocus Exit Sub End If If ComboLoc2.Value = "" Then MsgBox "Please select a destination." ComboLoc2.SetFocus Exit Sub End If If ComboLoc1.Value = ComboLoc2.Value Then MsgBox "Please choose two different destinations." ComboLoc1.SetFocus Exit Sub End If BoxMiles.Value = DistanceMiles BoxKm.Value = DistanceKm BoxTravelTime.Value = TravelTime Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
Is the workbook actually named "Login (1.0)"? Did you remove the XLS
extension? If not try With Workbooks("Login (1.0).xls").Sheets("Mileage Info") -- Jim "JNW" wrote in message ... | In our office we have machines running both xl2000 and xl2003. The machine I | work on is 2000 and I have programmed some macros to make sheets very hidden. | Other macros reference these sheets and do calculations without having to | unhide them. | | However, when running the application on xl2003, the sheets are hidden | properly, but when the calculating macros try to reference them error 9 | occurs (out of range). | | Any ideas as to why? | | code (the following is just one of the places in the code that gets hung up. | I've got a lot of macros that are stopping because of this) | ----------------------------------------------------------- | Private Sub ButtonGetMiles_Click() | Application.ScreenUpdating = False | Dim ChartRange As Object | Dim Loc1 As Object | Dim Loc2 As Object | Dim DistanceMiles As Variant | Dim DistanceKm As Variant | Dim TravelTime As Variant | | ***the code stops with the following line \/ \/ \/ ***** | With Workbooks("Login (1.0)").Sheets("Mileage Info") | Set ChartRange = .Range("B2:FT176") | Set Loc1 = .Range("A201") | Set Loc2 = .Range("A204") | End With | | DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, | Loc2) | DistanceKm = DistanceMiles * 5 / 3 | TravelTime = DistanceMiles / 60 | | If ComboLoc1.Value = "" Then | MsgBox "Please select a starting location." | ComboLoc1.SetFocus | Exit Sub | End If | | If ComboLoc2.Value = "" Then | MsgBox "Please select a destination." | ComboLoc2.SetFocus | Exit Sub | End If | | If ComboLoc1.Value = ComboLoc2.Value Then | MsgBox "Please choose two different destinations." | ComboLoc1.SetFocus | Exit Sub | End If | | BoxMiles.Value = DistanceMiles | BoxKm.Value = DistanceKm | BoxTravelTime.Value = TravelTime | Application.ScreenUpdating = True | End Sub | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
from the immediate window in xl2003
worksheets("Sheet3").Range("A1:C5").Formula = "=Rand()" worksheets("Sheet3").Visible = xlSheetVeryHidden ? worksheets("Sheet3").Range("B2").Formula =RAND() ? worksheets("Sheet3").Range("B2").Value 0.282418047629057 ? worksheets(3).Name Sheet3 ? worksheets("Sheet3").visible = xlSheetVeryHidden True I don't seem to have a problem. Maybe the sheets name isn't what you think it is? The sheet was very hidden, but I didn't have any workbook level protection. -- Regards, Tom Ogilvy "JNW" wrote in message ... In our office we have machines running both xl2000 and xl2003. The machine I work on is 2000 and I have programmed some macros to make sheets very hidden. Other macros reference these sheets and do calculations without having to unhide them. However, when running the application on xl2003, the sheets are hidden properly, but when the calculating macros try to reference them error 9 occurs (out of range). Any ideas as to why? code (the following is just one of the places in the code that gets hung up. I've got a lot of macros that are stopping because of this) ----------------------------------------------------------- Private Sub ButtonGetMiles_Click() Application.ScreenUpdating = False Dim ChartRange As Object Dim Loc1 As Object Dim Loc2 As Object Dim DistanceMiles As Variant Dim DistanceKm As Variant Dim TravelTime As Variant ***the code stops with the following line \/ \/ \/ ***** With Workbooks("Login (1.0)").Sheets("Mileage Info") Set ChartRange = .Range("B2:FT176") Set Loc1 = .Range("A201") Set Loc2 = .Range("A204") End With DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, Loc2) DistanceKm = DistanceMiles * 5 / 3 TravelTime = DistanceMiles / 60 If ComboLoc1.Value = "" Then MsgBox "Please select a starting location." ComboLoc1.SetFocus Exit Sub End If If ComboLoc2.Value = "" Then MsgBox "Please select a destination." ComboLoc2.SetFocus Exit Sub End If If ComboLoc1.Value = ComboLoc2.Value Then MsgBox "Please choose two different destinations." ComboLoc1.SetFocus Exit Sub End If BoxMiles.Value = DistanceMiles BoxKm.Value = DistanceKm BoxTravelTime.Value = TravelTime Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
Jim,
That was it. Do you, or anyone, know why 2000 VBA will open a file without the .xls extension while 2003 requires it? Just curious. Thanks again Jim "Jim Rech" wrote: Is the workbook actually named "Login (1.0)"? Did you remove the XLS extension? If not try With Workbooks("Login (1.0).xls").Sheets("Mileage Info") -- Jim "JNW" wrote in message ... | In our office we have machines running both xl2000 and xl2003. The machine I | work on is 2000 and I have programmed some macros to make sheets very hidden. | Other macros reference these sheets and do calculations without having to | unhide them. | | However, when running the application on xl2003, the sheets are hidden | properly, but when the calculating macros try to reference them error 9 | occurs (out of range). | | Any ideas as to why? | | code (the following is just one of the places in the code that gets hung up. | I've got a lot of macros that are stopping because of this) | ----------------------------------------------------------- | Private Sub ButtonGetMiles_Click() | Application.ScreenUpdating = False | Dim ChartRange As Object | Dim Loc1 As Object | Dim Loc2 As Object | Dim DistanceMiles As Variant | Dim DistanceKm As Variant | Dim TravelTime As Variant | | ***the code stops with the following line \/ \/ \/ ***** | With Workbooks("Login (1.0)").Sheets("Mileage Info") | Set ChartRange = .Range("B2:FT176") | Set Loc1 = .Range("A201") | Set Loc2 = .Range("A204") | End With | | DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, | Loc2) | DistanceKm = DistanceMiles * 5 / 3 | TravelTime = DistanceMiles / 60 | | If ComboLoc1.Value = "" Then | MsgBox "Please select a starting location." | ComboLoc1.SetFocus | Exit Sub | End If | | If ComboLoc2.Value = "" Then | MsgBox "Please select a destination." | ComboLoc2.SetFocus | Exit Sub | End If | | If ComboLoc1.Value = ComboLoc2.Value Then | MsgBox "Please choose two different destinations." | ComboLoc1.SetFocus | Exit Sub | End If | | BoxMiles.Value = DistanceMiles | BoxKm.Value = DistanceKm | BoxTravelTime.Value = TravelTime | Application.ScreenUpdating = True | End Sub | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
It doesn't have anything to do with the version of excel. It is a setting
in the folder's options. Best always to use the .xls extension which works regardless of the setting. -- Regards, Tom Ogilvy "JNW" wrote in message ... Jim, That was it. Do you, or anyone, know why 2000 VBA will open a file without the .xls extension while 2003 requires it? Just curious. Thanks again Jim "Jim Rech" wrote: Is the workbook actually named "Login (1.0)"? Did you remove the XLS extension? If not try With Workbooks("Login (1.0).xls").Sheets("Mileage Info") -- Jim "JNW" wrote in message ... | In our office we have machines running both xl2000 and xl2003. The machine I | work on is 2000 and I have programmed some macros to make sheets very hidden. | Other macros reference these sheets and do calculations without having to | unhide them. | | However, when running the application on xl2003, the sheets are hidden | properly, but when the calculating macros try to reference them error 9 | occurs (out of range). | | Any ideas as to why? | | code (the following is just one of the places in the code that gets hung up. | I've got a lot of macros that are stopping because of this) | ----------------------------------------------------------- | Private Sub ButtonGetMiles_Click() | Application.ScreenUpdating = False | Dim ChartRange As Object | Dim Loc1 As Object | Dim Loc2 As Object | Dim DistanceMiles As Variant | Dim DistanceKm As Variant | Dim TravelTime As Variant | | ***the code stops with the following line \/ \/ \/ ***** | With Workbooks("Login (1.0)").Sheets("Mileage Info") | Set ChartRange = .Range("B2:FT176") | Set Loc1 = .Range("A201") | Set Loc2 = .Range("A204") | End With | | DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, | Loc2) | DistanceKm = DistanceMiles * 5 / 3 | TravelTime = DistanceMiles / 60 | | If ComboLoc1.Value = "" Then | MsgBox "Please select a starting location." | ComboLoc1.SetFocus | Exit Sub | End If | | If ComboLoc2.Value = "" Then | MsgBox "Please select a destination." | ComboLoc2.SetFocus | Exit Sub | End If | | If ComboLoc1.Value = ComboLoc2.Value Then | MsgBox "Please choose two different destinations." | ComboLoc1.SetFocus | Exit Sub | End If | | BoxMiles.Value = DistanceMiles | BoxKm.Value = DistanceKm | BoxTravelTime.Value = TravelTime | Application.ScreenUpdating = True | End Sub | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
The calculation macro worked fine when I added .xls
I didn't know about the folder option. Thanks "Tom Ogilvy" wrote: from the immediate window in xl2003 worksheets("Sheet3").Range("A1:C5").Formula = "=Rand()" worksheets("Sheet3").Visible = xlSheetVeryHidden ? worksheets("Sheet3").Range("B2").Formula =RAND() ? worksheets("Sheet3").Range("B2").Value 0.282418047629057 ? worksheets(3).Name Sheet3 ? worksheets("Sheet3").visible = xlSheetVeryHidden True I don't seem to have a problem. Maybe the sheets name isn't what you think it is? The sheet was very hidden, but I didn't have any workbook level protection. -- Regards, Tom Ogilvy "JNW" wrote in message ... In our office we have machines running both xl2000 and xl2003. The machine I work on is 2000 and I have programmed some macros to make sheets very hidden. Other macros reference these sheets and do calculations without having to unhide them. However, when running the application on xl2003, the sheets are hidden properly, but when the calculating macros try to reference them error 9 occurs (out of range). Any ideas as to why? code (the following is just one of the places in the code that gets hung up. I've got a lot of macros that are stopping because of this) ----------------------------------------------------------- Private Sub ButtonGetMiles_Click() Application.ScreenUpdating = False Dim ChartRange As Object Dim Loc1 As Object Dim Loc2 As Object Dim DistanceMiles As Variant Dim DistanceKm As Variant Dim TravelTime As Variant ***the code stops with the following line \/ \/ \/ ***** With Workbooks("Login (1.0)").Sheets("Mileage Info") Set ChartRange = .Range("B2:FT176") Set Loc1 = .Range("A201") Set Loc2 = .Range("A204") End With DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, Loc2) DistanceKm = DistanceMiles * 5 / 3 TravelTime = DistanceMiles / 60 If ComboLoc1.Value = "" Then MsgBox "Please select a starting location." ComboLoc1.SetFocus Exit Sub End If If ComboLoc2.Value = "" Then MsgBox "Please select a destination." ComboLoc2.SetFocus Exit Sub End If If ComboLoc1.Value = ComboLoc2.Value Then MsgBox "Please choose two different destinations." ComboLoc1.SetFocus Exit Sub End If BoxMiles.Value = DistanceMiles BoxKm.Value = DistanceKm BoxTravelTime.Value = TravelTime Application.ScreenUpdating = True End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
In passing -
DistanceKm = DistanceMiles * 5 / 3 8 / 5 ??? Regards, Peter T "JNW" wrote in message ... In our office we have machines running both xl2000 and xl2003. The machine I work on is 2000 and I have programmed some macros to make sheets very hidden. Other macros reference these sheets and do calculations without having to unhide them. However, when running the application on xl2003, the sheets are hidden properly, but when the calculating macros try to reference them error 9 occurs (out of range). Any ideas as to why? code (the following is just one of the places in the code that gets hung up. I've got a lot of macros that are stopping because of this) ----------------------------------------------------------- Private Sub ButtonGetMiles_Click() Application.ScreenUpdating = False Dim ChartRange As Object Dim Loc1 As Object Dim Loc2 As Object Dim DistanceMiles As Variant Dim DistanceKm As Variant Dim TravelTime As Variant ***the code stops with the following line \/ \/ \/ ***** With Workbooks("Login (1.0)").Sheets("Mileage Info") Set ChartRange = .Range("B2:FT176") Set Loc1 = .Range("A201") Set Loc2 = .Range("A204") End With DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, Loc2) DistanceKm = DistanceMiles * 5 / 3 TravelTime = DistanceMiles / 60 If ComboLoc1.Value = "" Then MsgBox "Please select a starting location." ComboLoc1.SetFocus Exit Sub End If If ComboLoc2.Value = "" Then MsgBox "Please select a destination." ComboLoc2.SetFocus Exit Sub End If If ComboLoc1.Value = ComboLoc2.Value Then MsgBox "Please choose two different destinations." ComboLoc1.SetFocus Exit Sub End If BoxMiles.Value = DistanceMiles BoxKm.Value = DistanceKm BoxTravelTime.Value = TravelTime Application.ScreenUpdating = True End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
veryhidden with xl2000 & xl2003
Thanks Peter!
I've always used 5/3 to convert it in my head (can't get my brain wrapped around 8/5!). Never knew the exact conversion "Peter T" wrote: In passing - DistanceKm = DistanceMiles * 5 / 3 8 / 5 ??? Regards, Peter T "JNW" wrote in message ... In our office we have machines running both xl2000 and xl2003. The machine I work on is 2000 and I have programmed some macros to make sheets very hidden. Other macros reference these sheets and do calculations without having to unhide them. However, when running the application on xl2003, the sheets are hidden properly, but when the calculating macros try to reference them error 9 occurs (out of range). Any ideas as to why? code (the following is just one of the places in the code that gets hung up. I've got a lot of macros that are stopping because of this) ----------------------------------------------------------- Private Sub ButtonGetMiles_Click() Application.ScreenUpdating = False Dim ChartRange As Object Dim Loc1 As Object Dim Loc2 As Object Dim DistanceMiles As Variant Dim DistanceKm As Variant Dim TravelTime As Variant ***the code stops with the following line \/ \/ \/ ***** With Workbooks("Login (1.0)").Sheets("Mileage Info") Set ChartRange = .Range("B2:FT176") Set Loc1 = .Range("A201") Set Loc2 = .Range("A204") End With DistanceMiles = Application.WorksheetFunction.Index(ChartRange, Loc1, Loc2) DistanceKm = DistanceMiles * 5 / 3 TravelTime = DistanceMiles / 60 If ComboLoc1.Value = "" Then MsgBox "Please select a starting location." ComboLoc1.SetFocus Exit Sub End If If ComboLoc2.Value = "" Then MsgBox "Please select a destination." ComboLoc2.SetFocus Exit Sub End If If ComboLoc1.Value = ComboLoc2.Value Then MsgBox "Please choose two different destinations." ComboLoc1.SetFocus Exit Sub End If BoxMiles.Value = DistanceMiles BoxKm.Value = DistanceKm BoxTravelTime.Value = TravelTime Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VeryHidden | Excel Programming | |||
veryhidden sheet make restrictions ?! | Excel Programming | |||
Sheet hidden and VeryHidden | Excel Discussion (Misc queries) | |||
Performance trouble when changing from XL2000 to XL2003 | Excel Discussion (Misc queries) | |||
Problems with vb.veryhidden | Excel Programming |