Simple VBA Code written in Excel 2003 not working in Excel 2000

[Rich B.]

Hi. I wrote some simple code in Excel 2003 (see below) to hide certain rows
if the user selects a certain option button. It works fine on Excel 2003,
but when the user tries it on his machine (he has Excel 2000), he gets a
run-time 1004 error message. From what he describes it sounds like the error
is ocurring somewhere after the "End With" line since when he clicks "End" on
the error message screen, he is taken to the "TrlInputs" sheet (the started
on the "GenInputs" sheet since that is where the option button is located).

Any help would be greatly appreciated!

Thanks.
Rich



Private Sub OptionButtonActual_Click()

Application.ScreenUpdating = False
Sheets("TrlInputs").Select

Sheets("TrlInputs").Unprotect Password:="password"
With Sheets("TrlInputs")
.Rows("12:13").EntireRow.Hidden = True
.Rows("21:54").EntireRow.Hidden = False
End With
Sheets("TrlInputs").Range("A1").Select
Sheets("TrlInputs").Protect Password:="password", DrawingObjects:=True

Sheets("GenInputs").Select
Sheets("GenInputs").Range("H4").Select
Application.ScreenUpdating = True

End Sub

 

[Michael]

Use Worksheets instead of Sheets, or
First:
Dim wks as worksheet
Dim wks2 as worksheet
Second:

Set wks = WorkSheets("TrlInputs")
Set wks2 = WorkSheets("GenInputs")
Third:

Application.ScreenUpdating = False
wks.select

wks.Unprotect Password:="password"
With wks
.Rows("12:13").EntireRow.Hidden = True
.Rows("21:54").EntireRow.Hidden = False
End With
wks.Range("A1").Select
wks.Protect Password:="password", DrawingObjects:=True

wks2.Select
wks2.Range("H4").Select
Application.ScreenUpdating = True

Michael Arch.

 

[Rich B.]

Thanks Michael for your help. I made the changes that you suggested,
explicitly declaring each of the worksheets as worksheets. I just sent the
file to the user so I'll let you know if it worked.

Do you think there could be a problem with the "DrawingObjects" part of the
protect method? I think that Excel 2003 has more options to proect a
worksheet than Excel 2000 does, and if so, perhaps this could be a problem as
well if it is not recognized in Excel 2000???

Thanks again!
Rich

 

[Rich B.]

Michael-
I just got word back that the changes you suggested work - no more run-time
error!

Thanks for your help!

 

[Michael]

You're welcome