Scroll to top of each active worksheet

[alyssa75]

When I open a workbook, I want each sheet to be set to A1. This isn't working because many of the sheets have frozen panes....so selecting doesn't scroll to the top. Anyway, to have each sheet scroll to the top when opening the workbook?

Thanks!

 

[Smitty]

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            Application.Goto Reference:=ws.Range("A1"), Scroll:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[alyssa75]

Yep! Thanks much!

 

[johns_st]

Is there a way to have the sheet scroll to the end and then back up to the beginning? I ask this because I have a Microsoft DT picker control embedded in a few of the cells and when the worksheet is opened after being saved, you must scroll the page down and then up again to activate the DT Picker control. I have seen numerous complaints regarding this "bug" however no solution to embed as a temporary fix.

 

[Smitty]

Welcome to the Board!

The DT Picker control for Excel is a POS, and should be avoided if at all possible, kind of like merged cells.

You can try recording a macro scrolling down, then up, but I don't know if it will do any good. Otherwise I'd suggest a custom combobox with dates/times, as they don't seem to have the same issues. There was also a recent post where someone offered a custom Date/Time contol, so I'll see if I can't dig it up for you.

 

[johns_st]

Welcome to the Board!

The DT Picker control for Excel is a POS, and should be avoided if at all possible, kind of like merged cells.

You can try recording a macro scrolling down, then up, but I don't know if it will do any good. Otherwise I'd suggest a custom combobox with dates/times, as they don't seem to have the same issues. There was also a recent post where someone offered a custom Date/Time contol, so I'll see if I can't dig it up for you.

I agree it is a POS but I finally have it customized and liked by my colleagues. I just have that retarded issue of having to scroll the sheet down and up to activate the control. I have seen some vba codes used for other applications and tried to modify to use on my worksheets but I have been unsuccessful.

 

[Smitty]

Do you also have the issue where you have a "ghost" image of the DTPicker wherever you placed it, but it actually shows up near A1? That's one I haven't been able to figure out.

I'll make a note to the Excel team and see if they can fix it for the next version, and it would be great if you could detail the problems you have with it. The more information we can send just makes the next version that much better.

 

[johns_st]

Do you also have the issue where you have a "ghost" image of the DTPicker wherever you placed it, but it actually shows up near A1? That's one I haven't been able to figure out.

I'll make a note to the Excel team and see if they can fix it for the next version, and it would be great if you could detail the problems you have with it. The more information we can send just makes the next version that much better.

I had the ghost issue when I started designing it but I figured out how to remove the ghost and quite frankly I can't remember how I did it. The only issue I have is the scroll to activate issue.

I did encounter a run-time issue this morning. Everything was fine on my computer last night when running various worksheet scenarios but when I distributed the workbook to one of my colleagues he encountered the run-time error. I'll have to find out from him what the code was. I'm just not sure why he run-time error didn't happen to me on my computer.