Cell Addressing when selecting a Different Worksheet 1

[shytott]

Hi
Would anyone know if its possible when I move bewteen worksheets (via sheet tabs) that each time I go into a sheet, the cell pointer is in cell A1?

Many Thanks

 

[SkipVought]

Hi,

You could use the Workbook_SheetActivate event in teh Workbook Object code window

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  sh.[A1].select
End Sub
[code]

Skip,
[sub]
[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
[b]Tooth Company Freeze a Crowd![/b]  and
[b]Many are Cold, but Few are Frozen![/b][tongue][/sub]

 

[shytott]

Hi Skip
Thanks for that.
Sadly my total lack of VB training veritably shines through here..!

I right clicked a sheet tab, went to 'View code', and pasted in everything you sent bewteen your 2 '

's and nothing happened.  Im assuming Ive stuck your code in teh wrong place?  My spreadsheet has a total of 15 worksheets with 3 modules (if thats any help)!
Thanks
Shy

 

[SkipVought]

In the VB Editor View the Project Explorer.

Select the ThisWorkbook object

Paste the code.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[shytott]

Yup, that did it!

Once again my friend, I'm in your debt

Thanks for the help Skip

Shy

 

[shytott]

Skip
...on the off chance you see this...
your solution worked perfectly for a workbook which every sheet needed to be openned with the pointer in A1. However I have a different work book where I need the 'gotoA1' to work on some sheets and not on others. Instead of your global solution, is there a way I can only get certain sheets to 'gotoA1' and allow the others to just open unhindered so to speak. ie is there code which I can apply to the individual sheets when i click the sheet tab. Hope that makes sense!

Many thanks in anticipation
Shy

 

[PHV]

Have a look at the Worksheet_Activate event procedure.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

You're still doing the same thing on multiple sheets, it seems, so...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  select case sh.name
    case "Sheet1","Sheet2"
       sh.[A1].select 
  end select
End Sub

works for sheet names you include.

OR

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  select case sh.name
    case "Sheet1","Sheet2"
    Case else
       sh.[A1].select 
  end select
End Sub

works for sheet names you exclude.




Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[shytott]

Spot on Skip, thats nailed it!
Thanks very much.

On a similar sort of theme...
Do you know how I would force a workbook to open up on a menu sheet imaginatively named, "menu" !

Cheers
Shy

 

[SkipVought]

Check out the Workbook_Open event.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[shytott]

Hi Skip
Yup that worked also

Thanks once again for you help
Do apprecaite it!

Shy