Where best to place an instruction in a VBA script
 

Hi All

I'm using this code when I open my workbook.

Private Sub Workbook_Open()
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1))
For Each Sheet In mysheets
Sheets("ShareSheet").ScrollArea = "A1:J27"

Next


End Sub


I'd like cell A200 to be selected and have the cursor box on it on
opening the worksheet.

I'm trying to place this line in the code :

Application.Goto Reference:="R200C1"

but can't get it to work.

Can someone advise please?

Grateful for any assistance.


Best Wishes

Where best to place an instruction in a VBA script
 

hi
Range("A200").select

Regards
FSt1

"Colin Hayes" wrote:


Hi All

I'm using this code when I open my workbook.

Private Sub Workbook_Open()
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1))
For Each Sheet In mysheets
Sheets("ShareSheet").ScrollArea = "A1:J27"

Next


End Sub


I'd like cell A200 to be selected and have the cursor box on it on
opening the worksheet.

I'm trying to place this line in the code :

Application.Goto Reference:="R200C1"

but can't get it to work.

Can someone advise please?

Grateful for any assistance.


Best Wishes
.

Where best to place an instruction in a VBA script
 

Try

Application.Goto Range("A200"), True

OR add this to the worksheet Activate event as below...

Private Sub Worksheet_Activate()
Application.Goto Range("A200"), True
End Sub

--
Jacob


"Colin Hayes" wrote:


Hi All

I'm using this code when I open my workbook.

Private Sub Workbook_Open()
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1))
For Each Sheet In mysheets
Sheets("ShareSheet").ScrollArea = "A1:J27"

Next


End Sub


I'd like cell A200 to be selected and have the cursor box on it on
opening the worksheet.

I'm trying to place this line in the code :

Application.Goto Reference:="R200C1"

but can't get it to work.

Can someone advise please?

Grateful for any assistance.


Best Wishes
.

Where best to place an instruction in a VBA script
 

In article , FSt1
writes
hi
Range("A200").select

Regards
FSt1


Hi

Ok thanks for getting back. That's fixed it now. Worked first time.



Best Wishes




Hi All

I'm using this code when I open my workbook.

Private Sub Workbook_Open()
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1))
For Each Sheet In mysheets
Sheets("ShareSheet").ScrollArea = "A1:J27"

Next


End Sub


I'd like cell A200 to be selected and have the cursor box on it on
opening the worksheet.

I'm trying to place this line in the code :

Application.Goto Reference:="R200C1"

but can't get it to work.

Can someone advise please?

Grateful for any assistance.


Best Wishes
.

Where best to place an instruction in a VBA script
 

I'm trying to place this line in the code :
Application.Goto Reference:="R200C1"

Hi. I'm glad it's working. It was a little confusing because one can't
select A200 after restrinting the selection to A1:J27

Worksheets(Array(1))

Unless I'm mistaken, this appears to only return 1 sheet.

Goto Reference:="R200C1"

Just to mention...if you want to use numbers instead of strings,
consider the Cells Property.

Here's my best guess on the code you posted.
Just note that Worksheets(1) "could" be ShareSheet, and you can't select
A200 when the scroll area is restricted to A1:J27.

Sub Demo()
Dim MySheet
Set MySheet = Worksheets(1)
Application.Goto MySheet.Cells(200, 1)

'Note: You can't Select A200 on "ShareSheet"
Sheets("ShareSheet").ScrollArea = "A1:J27"
End Sub

= = = = = = =
HTH :)
Dana DeLouis




On 1/19/2010 7:52 AM, Colin Hayes wrote:
In article , FSt1
writes
hi
Range("A200").select

Regards
FSt1


Hi

Ok thanks for getting back. That's fixed it now. Worked first time.



Best Wishes




Hi All

I'm using this code when I open my workbook.

Private Sub Workbook_Open()
Dim mysheets As Sheets
Set mysheets = Worksheets(Array(1))
For Each Sheet In mysheets
Sheets("ShareSheet").ScrollArea = "A1:J27"

Next


End Sub


I'd like cell A200 to be selected and have the cursor box on it on
opening the worksheet.

I'm trying to place this line in the code :

Application.Goto Reference:="R200C1"

but can't get it to work.

Can someone advise please?

Grateful for any assistance.


Best Wishes
.