Runtime error - goto
 

Hi,

I've tried to combine 2 macros that I've used successfully separately.

The first one protects the user interface and the second resets sheets to A2

Private Sub Workbook_Open()

Dim sht As Worksheet

Application.ScreenUpdating = False
For Each sht In Worksheets
Const TopLeft As String = "A2"
sht.Protect Password:="*9reh8", _
UserInterFaceOnly:=True
Application.Goto sht.Range(TopLeft), Scroll:=True

Next sht

Application.ScreenUpdating = True

End Sub

Could anyone tell me where I'm going wrong please

Many thanks
--
Trish

Runtime error - goto
 

Try moving the line Const TopLeft as String="A2" to just below the Dim sht line
--
Frank K


"Trish Smith" wrote:

Hi,

I've tried to combine 2 macros that I've used successfully separately.

The first one protects the user interface and the second resets sheets to A2

Private Sub Workbook_Open()

Dim sht As Worksheet

Application.ScreenUpdating = False
For Each sht In Worksheets
Const TopLeft As String = "A2"
sht.Protect Password:="*9reh8", _
UserInterFaceOnly:=True
Application.Goto sht.Range(TopLeft), Scroll:=True

Next sht

Application.ScreenUpdating = True

End Sub

Could anyone tell me where I'm going wrong please

Many thanks
--
Trish

Runtime error - goto
 

Works fine for me in 2003 and 2007 with no runtime error.

I would suggest qualifying WorkSheets just to be on safe side.

For Each sht In ThisWorkbook.Worksheets

Otherwise.............OK


Gord Dibben MS Excel MVP


On Sat, 12 Dec 2009 13:34:01 -0800, Trish Smith
wrote:

Hi,

I've tried to combine 2 macros that I've used successfully separately.

The first one protects the user interface and the second resets sheets to A2

Private Sub Workbook_Open()

Dim sht As Worksheet

Application.ScreenUpdating = False
For Each sht In Worksheets
Const TopLeft As String = "A2"
sht.Protect Password:="*9reh8", _
UserInterFaceOnly:=True
Application.Goto sht.Range(TopLeft), Scroll:=True

Next sht

Application.ScreenUpdating = True

End Sub

Could anyone tell me where I'm going wrong please

Many thanks

Runtime error - goto
 

Hi Frank,

Thanks for replying
I've worked it out - one of my sheets had a merged range at A1:A6.

Thank you

--
Trish


"Frank K" wrote:

Try moving the line Const TopLeft as String="A2" to just below the Dim sht line
--
Frank K


"Trish Smith" wrote:

Hi,

I've tried to combine 2 macros that I've used successfully separately.

The first one protects the user interface and the second resets sheets to A2

Private Sub Workbook_Open()

Dim sht As Worksheet

Application.ScreenUpdating = False
For Each sht In Worksheets
Const TopLeft As String = "A2"
sht.Protect Password:="*9reh8", _
UserInterFaceOnly:=True
Application.Goto sht.Range(TopLeft), Scroll:=True

Next sht

Application.ScreenUpdating = True

End Sub

Could anyone tell me where I'm going wrong please

Many thanks
--
Trish

Runtime error - goto
 

Thanks for trying it out Gord.

It was a merged range on one of my sheets. I try to avoid merging cells but
the person I'm doing the workbook for wants a horizontal title over a number
of rows.

I've added rows at the top to get over this.

Thanks again
--
Trish


"Gord Dibben" wrote:

Works fine for me in 2003 and 2007 with no runtime error.

I would suggest qualifying WorkSheets just to be on safe side.

For Each sht In ThisWorkbook.Worksheets

Otherwise.............OK


Gord Dibben MS Excel MVP


On Sat, 12 Dec 2009 13:34:01 -0800, Trish Smith
wrote:

Hi,

I've tried to combine 2 macros that I've used successfully separately.

The first one protects the user interface and the second resets sheets to A2

Private Sub Workbook_Open()

Dim sht As Worksheet

Application.ScreenUpdating = False
For Each sht In Worksheets
Const TopLeft As String = "A2"
sht.Protect Password:="*9reh8", _
UserInterFaceOnly:=True
Application.Goto sht.Range(TopLeft), Scroll:=True

Next sht

Application.ScreenUpdating = True

End Sub

Could anyone tell me where I'm going wrong please

Many thanks

.

Runtime error - goto
 

You gotta love those merged cells<g


Gord

On Sat, 12 Dec 2009 17:12:01 -0800, Trish Smith
wrote:

Hi Frank,

Thanks for replying
I've worked it out - one of my sheets had a merged range at A1:A6.

Thank you

Runtime error - goto
 

The person might be satisfied with Center Across Selection which causes no
problems as merged cells do.


Gord

On Sat, 12 Dec 2009 17:16:01 -0800, Trish Smith
wrote:

Thanks for trying it out Gord.

It was a merged range on one of my sheets. I try to avoid merging cells but
the person I'm doing the workbook for wants a horizontal title over a number
of rows.

I've added rows at the top to get over this.

Thanks again