veryhidden with xl2000 & xl2003

[JNW]

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

 

[Jim Rech]

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
| 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
|

 

[Tom Ogilvy]

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.

 

[JNW]

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

 

[Tom Ogilvy]

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.

 

[JNW]

The calculation macro worked fine when I added .xls

I didn't know about the folder option. Thanks

 

[Peter T]

In passing -

DistanceKm = DistanceMiles * 5 / 3

8 / 5 ???

Regards,
Peter T

 

[JNW]

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