Focus is on wrong sheet after opening.

[StargateFanFromWork]

I've never had this happen before. When I open up a workbook, it doesn't
open up to the first sheet. It starts that way but then flips to the last
one. It doesn't matter if the doct. was saved while on the first sheet, it
always reverts to the last one next time it's opened.

The other two sheets besides the main one are just information sheets that
the user will rarely need but must be there. Is there any way to have the
main sheet be the one that is seen after opening?

Some specs: this is an Excel doct. saved in a DOCSopen environment, which
shouldn't be an issue at all, but thought I'd mention it. XL2K on a recent
upgrade to WinXP from Win2K.

Thanks. D

 

[Guest]

Hi,
I am no expert on VB code, but have you tried adding

Private Sub Workbook_Open()
Sheets("the sheet to view on opening here").select
end sub

to the ThisWorkbook part of the VB editor?
just a thought!

 

[StargateFanFromWork]

Hi,
I am no expert on VB code, but have you tried adding

Private Sub Workbook_Open()
Sheets("the sheet to view on opening here").select
end sub

to the ThisWorkbook part of the VB editor?
just a thought!

Hallelujah! Thanks goodness. That works. It flickers to the back sheet
but then scoots back to the first and you can hardly see it; just a flicker.
But it gets the job done. I've never seen this behaviour before. But no
matter. The user opens up the doct and it comes up with floating
commandbar, too. Everything seems a-okay. This is great. Thanks much.
D

 

[Roger Govier]

Hi

You could prevent the flicker with
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("the sheet to view on opening here").select
Application.ScreenUpdating = True
end sub

 

[StargateFan]

Hi

You could prevent the flicker with
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("the sheet to view on opening here").select
Application.ScreenUpdating = True
end sub

Thanks. Will give that a try tomorrow. D

 

[StargateFanFromWork]

Hi

You could prevent the flicker with
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("the sheet to view on opening here").select
Application.ScreenUpdating = True
end sub

[snip]

After applying the above to the workbook, I still had that awful flicker.
Then it occurred to me (d'uh) to look at the rest of the code. I'm no vb
expert of course but I've created so many XL2K workbooks now with floating
toolbars that I'm more or less familiar with what elements should be there.
In the This Workbook module (correct word?), I had a count sheets code in.
I can't remember which workbook needed this but one did. Removed all that
extra stuff and the flickering stopped on its own. Crazy, huh? But I
managed to figure this out on my own, so I'm feeling pretty smug <lol>.

Nevertheless, all this code is going into my Tips folder. It'll come in
handy at some point, I know.

Thanks once again for everyone's help. Much appreciated. D

 

[Roger Govier]

Hi

Thanks for the feedback letting us know how you got on.
Well done for figuring out the cause of the problem, and solving it
yourself.

--
Regards

Roger Govier

Hi

You could prevent the flicker with
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("the sheet to view on opening here").select
Application.ScreenUpdating = True
end sub

[snip]

After applying the above to the workbook, I still had that awful
flicker. Then it occurred to me (d'uh) to look at the rest of the
code. I'm no vb expert of course but I've created so many XL2K
workbooks now with floating toolbars that I'm more or less familiar
with what elements should be there. In the This Workbook module
(correct word?), I had a count sheets code in. I can't remember which
workbook needed this but one did. Removed all that extra stuff and
the flickering stopped on its own. Crazy, huh? But I managed to
figure this out on my own, so I'm feeling pretty smug <lol>.

Nevertheless, all this code is going into my Tips folder. It'll come
in handy at some point, I know.

Thanks once again for everyone's help. Much appreciated. D

 

[StargateFanFromWork]

Hi

Thanks for the feedback letting us know how you got on.
Well done for figuring out the cause of the problem, and solving it
yourself.

<wry grin> Turns out I'm still not so smart, though <sigh>.

I never looked at that code before adding it in initially. So today when I
went to use the workbook, the filters didn't work. Guess what controls
those filters on a protected sheet?!

In fact, this has turned out to be a mess and I doubt I'll be able to fix it
easily. The trouble is that when I've left unprotected workbooks behind in
contracts, they get quite corrupted. I may not be an expert but I know more
than anyone I've come across so far over many years. Over a year ago I
returned back to a place after doing a contract in between; I'd been gone
for only 10 shorts months and my workbooks were all atrociously deranged!
So I learning how to put the protection code in before and after sorting,
etc.

The difficulty with this process is that XL2K doesn't allow filtering on
protected, at least, not without a lot of hassle. When I added back the
filtering code to the workbook that I'd taken out, the awful flickering
returned. Yet the filtering doesn't even work after pushing a button to
re-activate the protection after a sort, for example.

Yet it's a toss-up. Do I leave the filtering and have an open, corruptible
environment ... or do I remove the filtering, leave all the
unprotect/protect codes in places before and after sorts but the user can't
filter at any of the columns at all. Tough, tough choice. It's easy with
me as I know what to do and even have a button to unprotect a sheet manually
before sorting. I made sure that all the buttons on the commandbar invoke
the protection automatically afterwards.

Perhaps that might be a possible solution - to add a special unprotect
button for filtering. Trouble with that is the the user might not really
use the buttons enough so the sheet wouldn't necessarily get protected right
after filtering, etc.

I'll have to think about this one. Fortunately I still have about another 2
weeks before the new person starts and I'll have a few days to train that
person before I finish up myself.

Anyway, here's the code that is supposed to unlock the filtering with the
added anti-flickering code. (This is the code that only unlocks the
filtering till the first protection is put back on.) Perhaps something
might occur to someone here. You're all so much more advanced than I am and
perhaps there's something I don't know re the filtering:

********************************************************************************
Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I

'-------------------------------------------------------------------------
' code to stop flickering (doesn't work so far).
Application.ScreenUpdating = False
Sheets("LAR Stats").Select
Application.ScreenUpdating = True
'-------------------------------------------------------------------------


End Sub
********************************************************************************

Thanks. D

--
Regards

Roger Govier

Hi

You could prevent the flicker with
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("the sheet to view on opening here").select
Application.ScreenUpdating = True
end sub

[snip]

[snip]

 

[StargateFanFromWork]

Hi

Thanks for the feedback letting us know how you got on.
Well done for figuring out the cause of the problem, and solving it
yourself.

<wry grin> Turns out I'm still not so smart, though <sigh>.
[snip]

Yet it's a toss-up. Do I leave the filtering and have an open,
corruptible environment ... or do I remove the filtering, leave all the
unprotect/protect codes in places before and after sorts but the user
can't filter at any of the columns at all. Tough, tough choice. [snip]

Had to pass along conclusions in case anyone else finds themselves in a
similar situation. I'm going to leave the ability to manually filter off.
I've again removed all the filtering code that caused the flickers in the
sheet upon opening. This was after trying the workbook again just now and
finding that even though the manual filters don't work at all, the ones in
the printing buttons do. They allow the sheet to be printed out 3 different
ways, one with all the entries, one with in-house training and another with
training done via outside sources. That's good enough, I believe, since
with fiscal year end there isn't much more I can do not having all the
knowledge needed to make such a complex thing work from all angles. If they
need anything more than that, I'll leave it to them to figure it out.

I will be paying attention to any posts in the archives again re filtering
protected sheets to see if I can learn anything more soon but in the
meantime at least we have the ability for those 3 reports above and that's
good. Since those are possible to do, it's more important now to leave the
protection codes** in place to keep
the book from being easily messed up.

(** I mean by that these 2 codes put at beginning and end of each various
commandbar macros to enable sorting, etc.:
ActiveSheet.Unprotect 'place at the beginning of the code
ActiveSheet.Protect ' place at end of code)

Thanks. All this helped. One learns so much this way even though it is a
tad frustrating. D

 

[Roger Govier]

Hi

You need to stop screen updating at the start of the sub, then switch
back on at the end.

Private Sub Workbook_Open()
Dim I As Integer
Application.ScreenUpdating = False

For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
Sheets("LAR Stats").Select

Application.ScreenUpdating = True

End Sub


--
Regards

Roger Govier

Hi

Thanks for the feedback letting us know how you got on.
Well done for figuring out the cause of the problem, and solving it
yourself.

<wry grin> Turns out I'm still not so smart, though <sigh>.

I never looked at that code before adding it in initially. So today
when I went to use the workbook, the filters didn't work. Guess what
controls those filters on a protected sheet?!

In fact, this has turned out to be a mess and I doubt I'll be able to
fix it easily. The trouble is that when I've left unprotected
workbooks behind in contracts, they get quite corrupted. I may not be
an expert but I know more than anyone I've come across so far over
many years. Over a year ago I returned back to a place after doing a
contract in between; I'd been gone for only 10 shorts months and my
workbooks were all atrociously deranged! So I learning how to put the
protection code in before and after sorting, etc.

The difficulty with this process is that XL2K doesn't allow filtering
on protected, at least, not without a lot of hassle. When I added
back the filtering code to the workbook that I'd taken out, the awful
flickering returned. Yet the filtering doesn't even work after
pushing a button to re-activate the protection after a sort, for
example.

Yet it's a toss-up. Do I leave the filtering and have an open,
corruptible environment ... or do I remove the filtering, leave all
the unprotect/protect codes in places before and after sorts but the
user can't filter at any of the columns at all. Tough, tough choice.
It's easy with me as I know what to do and even have a button to
unprotect a sheet manually before sorting. I made sure that all the
buttons on the commandbar invoke the protection automatically
afterwards.

Perhaps that might be a possible solution - to add a special unprotect
button for filtering. Trouble with that is the the user might not
really use the buttons enough so the sheet wouldn't necessarily get
protected right after filtering, etc.

I'll have to think about this one. Fortunately I still have about
another 2 weeks before the new person starts and I'll have a few days
to train that person before I finish up myself.

Anyway, here's the code that is supposed to unlock the filtering with
the added anti-flickering code. (This is the code that only unlocks
the filtering till the first protection is put back on.) Perhaps
something might occur to someone here. You're all so much more
advanced than I am and perhaps there's something I don't know re the
filtering:

********************************************************************************
Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I

'-------------------------------------------------------------------------
' code to stop flickering (doesn't work so far).
Application.ScreenUpdating = False
Sheets("LAR Stats").Select
Application.ScreenUpdating = True
'-------------------------------------------------------------------------


End Sub
********************************************************************************

Thanks. D

--
Regards

Roger Govier

Hi

You could prevent the flicker with
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("the sheet to view on opening here").select
Application.ScreenUpdating = True
end sub

[snip]

[snip]

 

[StargateFan]

Hi

You need to stop screen updating at the start of the sub, then switch
back on at the end.

Private Sub Workbook_Open()
Dim I As Integer
Application.ScreenUpdating = False

For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
Sheets("LAR Stats").Select

Application.ScreenUpdating = True

End Sub

Thanks. D