How To Automatically Set the Cursor on cell a1 on file open

[kfotedar]

Hi!,

Is there any way through VB code I could get the cursor to be on cell A1 when I open the excel file. My file has 10 sheets and I wanted that he cursor should be on cell a1 when any sheet on the file is selected.

Thanks,

Kavir

 

[TTom]

Hit ALT+F11 to open VBA.
Select: 'This Workbook' in the VBA Project's 'Miscrosoft Excel Objects' (upper left window)

In large right window put the following code:

Private Sub Workbook_Open()
Sheet1.Range("A1").Select
End Sub

This will select A1 when Workbook opens.

 

[kfotedar]

This only puts the cursor on cell a1 for sheet1. I've 10 sheets in the workbook, i want the cursor to be on a1 for every sheet when the workbook is opened.

Thanks.

K

 

[TTom]

You can use the following to set all sheets to "A1"
<code>
Private Sub Workbook_Open()
For Each ws In ActiveWorkbook.Sheets
Worksheets.Select
Range("A1").Activate
Next ws
End Sub
</code>

 

[Ziggy12]

Private Sub Workbook_Open()
Dim WSheet As Worksheet

For Each WSheet In Worksheets
WSheet.Activate
Range("A1").Select
Next
End Sub

 

[kfotedar]

Thanks guys for your help.

K

 

[Pinaceous]

Private Sub Workbook_Open()
Dim WSheet As Worksheet

For Each WSheet In Worksheets
WSheet.Activate
Range("A1").Select
Next
End Sub

Hello Ziggy12,

I really do like your approach and I've tried it out and it is working perfectly.

However, can you please add to your code above:

Where;

If the Sheet is EVEN

Range("A2").Select

likewise If the

Sheet is ODD Range("B3").Select

??

I've tried what I think would work but I'm not getting what I am after.

Many thanks,
Pinaceous

 

[daverunt]

Hi,

I had a go at this out of interest and here's my tuppence worth.

this works using the worksheet index.

For Each WSheet In Worksheets
 WSheet.Activate
 
 n = WSheet.Index
 If n Mod 2 = 0 Then

 Range("A2").Select
 Else
 Range("B3").Select
 End If
 
 Next