Finding the number of a worksheet

[Guest]

I am writing an menu driven application in which I want the user to only be
able to see one worksheet at a time. Each worksheet will have a unique
associated window with unique size and positioning.

Dim wrksht as worksheet



My goal is to have a sub of the form


sub UnhideSheetAndItsAssociatedWindow (sheet_)

ActiveWorkbook.Unprotect Password:="mypassword"

sheet_.visible = true

' all other sheets made invisible
' unhide window associated with sheet_
' hide all other windows

ActiveWorkbook.Protect Structure:=True, Windows:=True, _
password:="mypassword"



end sub


My questions are

1) is it possible to define an object of type window
2) if so, how might one associate a desired window with a desired worksheet
3) How might one code the sub so that if it is passed a parameter which is a
worksheet, it will unhide this sheet and hide all other sheets, and it will
unhide the associated window and hide all other windows ?

-regards

 

[Guest]

George,

number of questions in the question.
To hide all worksheets, work with

For all MyWorksheet in Thisworkbook.sheets
if MyWorkSheet <> sheet_ then myworksheet.hide 'hides all sheets but
your sheet_
next

Not sure why you want all sheets to have their own window? is it not simpler
to define the size and position of each sheet and resize and relocate the
window based on the sheet that is open?
Size of window can be addressed through
Application.Windows(1).Height
Application.Windows(1).Width
Application.Windows(1).Top
Application.Windows(1).Left

Good luck.
RDWJ

 

[Guest]

RD, that is *extremely* helpful. Based on what you made me aware of, I'm
only going to use one window, which will make my app simpler. BTW, as you
probably know, I can size the window in Excekl then find out its dimensions &
location with statements such as

Msgbox Application.Windows(1).Top