Disable WorkSheets Change

[STimber]

I am working on an Excel VBA project and would like to only allow th
user to view the first worksheet of a workbook that contains a varyin
amount of sheets (up to 10).

Orginally I wanted to Hide all of the sheets so that only the firs
appeared, but some necessary finctions require these other sheets to b
programmatically set as Selected, which errors if they are hidden.

My next option was to leave the other sheets unhidden, but not allo
the user to change worksheets. The code that i have entered for thi
reads:

Private Sub oSheet_Deactivate()
oBook.Sheets(1).Activate
End Sub

The problem with this code is that when the user tries to select a ne
sheet, it briefly appears and then change back to the original sheet
ALso is the user were to hold his/her mouse click, the sheet is visibl
until they release it.

Im pretty new to Excel programming, so I'm hoping that there is
simpler way to accomplish this. Any ideas?

Scot

 

[Tom Ogilvy]

Generally there is no coding requirement to activate a sheet.

Worksheets(2).Activate
Range("B9").Select
Selection.Value = 3

can be done with

Worksheets(2).Range("B9").Value = 3

as an example (no select or activate required).

There are a few commands that require selection, but these are very few and
not likely to be required here.

 

[gitcypher]

Your code looks correct. I've run into problems similar to this when I'
have the code reference one 'database' type of sheet that did all of m
necessary calculations.. a sheet I didn't want anyone to modify.
To stop the appearance of changing to one sheet, use

Application.ScreenUpdating = false and

Application.ScreenUpdating = True

Their pretty self explanitory. Their really good for (un)filtering
(un)hiding cells, and switching between sheets. Throw those two line
in your code wherever you need them.

-Gitcyphe

 

[gitcypher]

Your code looks correct. I've run into problems similar to this when I'
have the code reference one 'database' type of sheet that did all of m
necessary calculations.. a sheet I didn't want anyone to modify.
To stop the appearance of changing to one sheet, use

Application.ScreenUpdating = false and

Application.ScreenUpdating = True

Their pretty self explanitory. Their really good for (un)filtering
(un)hiding cells, and switching between sheets. Throw those two line
in your code wherever you need them.

-Gitcyphe

 

[STimber]

Thanks so much for the quick response.

Tom, I am using the activate fucntion to set the visible screen back t
the desired worksheet. Is there a better way of doing that?

gitcypher, I added the Application.ScreenUpdating code around m
Worksheet.Deactivate function, so it reads:

Private Sub oSheet_Deactivate()
Application.ScreenUpdating = False
oBook.Sheets(1).Activate
Application.ScreenUpdating = True
End Sub


This didnt seem to help much. I think by time the Workbook calls th
Deactivate fucntion, the screen has already been updated. I also trie
having the ScreenUpdating set to false during the Open function, bu
that didnt seem to work either.

Any ideas?

Scott

Also, Is there a way to remove the tabs programmitically

 

[Tom Ogilvy]

The point was, you never need to leave Sheets(1)

so there is no reason to worry about screenupdating or activating or using
the deactivate event.

Attack the cause of the problem, don't try to put bandaids on bad code.

 

[STimber]

Tom,

I must be misunderstanding your suggestion. The Workbook starts o
sheet 1, but contains many sheets (10 or so).

These other sheets are used for some calculations and uring a prin
function. As it stands, the user can navigate to these other Worksheet
using the tabs at the bottom on Excel.

What i would like to do is prevent the user from changing to the othe
sheets. Thats where I tried to place code in some sort of
Change_WorkSheet event, that would keep the user on Sheet 1
Worksheet_Deactivate was the closest i could find. I also didnt know
better way to change back to Sheet 1, so I used the Activate method. I
there a better way to accomplish either of these?

I would think there is a better way to keep the user from being able t
view these other sheets, but being somewhat knew to Excel programming
I do not know it.

Scot

 

[gitcypher]

I believe what Tom is trying to say is that a lot of manipulation can b
performed on a worksheet from within the code, even while it is hidden
You'll just have to write it with the proper references. I don't mea
to delve into your code too deeply, but I'd have to know exactly ho
your print function accesses the other sheets for calculation to giv
you the -best- help.

As an example, let’s say you have a series of random numbers oriente
vertically on 'Sheet2', whereas the first number's cell is name
"num1". You want these numbers sorted ascending. From within VBA, yo
can sort all the numbers, while not having to access the hidden sheet.

Range(Sheet2.Range("Num1"), Sheet2.Range("Num1").End(xlDown)).Sor
Order1:=xlAscending

What you can really take from this example is that a lot of recorde
macros are built as follows...


SOMETHING[/B].SELECT
SELECTION.PERFORMTAS

This can be replaced by Something.PerformTask, removing the need t
select a page before you are able to manipulate it.

I hope this helps more.

Tom, I hope this reply is more to your approval.

-Gitcyphe

Attachment filename: hiddensheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=48845

 

[STimber]

OK,

I think I understand better now. Unfortuantely, i think I may b
further from an answer to my problem.

These Excel Sheets were previously developed by some one else, and I a
accessing them through a VB project using Excel.Workbook an
Excel.Worksheet objects. The goal is to add this additiona
functionality w/o changing the code behind the original Excel Sheets.

That being said, here is a sample of a print fucntion used in one o
these sheets. If I understand correctly, there was a better way fo
this print function to be written.

Example:

Sheets(Array("Setup", "DO", "Temp", "Speed").Select

Sheets("Setup").PageSetup.Orientation = xlPortrait

Sheets("DO").PageSetup.Orientation = xlLandscape
Sheets("Temp").PageSetup.Orientation = xlLandscape
Sheets("Speed").PageSetup.Orientation = xlLandscape

ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Setup").Selec

 

[gitcypher]

try...

Sheets("Setup").PageSetup.Orientation = xlPortrait

Sheets(Array("Setup", "DO", "Temp").PageSetup.Orientation
xlLandscape

Sheets(Array("Setup", "DO", "Temp", "Speed").PrintOut Copies:=1


Sheets("Setup").Select

-Gitcyphe

 

[STimber]

gitcypher,

That will definitely work, my problem is that I cannot change the print
functions that were already created, which in many cases are quite
different from the example i provided.

Im accessing these from a standalone Visual Basic project and using
Excel.Worksheet and Excel.Workbook objects. I cannot change the code in
the sheet itself, only add some functionality.

This is why i was trying to disable the user's ability to change
sheets.

Any ideas?

Scott