Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Resetting error reporting after On Error GoTo | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
On Error Goto doesn't goto | Excel Programming | |||
On Error Goto doesn't goto | Excel Programming | |||
On error goto 0? | Excel Programming |