Hidden and visible sheets-swap

**KausBorealis** · 01-20-2008, 05:25 AM

Hi all,

in our classrooms my students work with Excel.
My goal is to show a single worksheet at the opening of the workbook, with important hints for the user. All the other worksheets must be hidden at this stage.
After reading the hints the user may proceed,
(clicking a button) and the result should be:
1. the hidden worksheets will be visible
2. except the one which was visible at the opening.

When the user finishes the work the original
hidden/visible situation should be restored.

I wrote a code for the Woksheets collection, and I made a For each; next cycle for all the sheets to do it and it works.

My question is: is it any other way to do it? I mean a shorter one ore more elegant?

Thanks in advance:
KausBorealis

**VBA Noob** · 01-20-2008, 05:39 AM

Would be helpful for others to display your code.

Note please remember to wrap code as per rule 5 below

Instead of clicking a button could you use a timer ??

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

http://www.cpearson.com/excel/OnTime.aspx

http://www.ozgrid.com/Excel/run-macro-on-time.htm

When the user finishes the work the original

How would you know when there finished. Maybe another timer

Depending on the layout of the sheets maybe when they answer the last question on sheet 1 then it unhides the next sheet and when you answer the last question it rehides all sheets via a event macro

http://www.cpearson.com/excel/Events.aspx

VBA Noob

**KausBorealis** · 01-20-2008, 06:46 AM

Hello VBA Noob,

and thanks.

I am now at home, while the Excel workbook is in my office computer.

The OnTime however here can't be used, beause I don't know in advance how long time is neccesary for the actual student to understand the instructions.

**VBA Noob** · 01-20-2008, 07:15 AM

See if this helps. After they read instuction sheet they change cell D23 (last answer) which takes them to the next sheet

Code used

Start Sheet module

Please Login or Register  to view this content.

First Sheet module

Please Login or Register  to view this content.

Second Sheet module

Please Login or Register  to view this content.

Last sheet module

Please Login or Register  to view this content.

VBA Noob

**KausBorealis** · 01-20-2008, 10:51 AM

Hi VBA Noob,

thanks for your code, nice.

In the meantime I found your reference (from yesterday)
http://www.vbaexpress.com/kb/getarticle.php?kb_id=578
which contains a nice solution.

I made something similar to it but this solution is much better. Anyway what I wrote doesn't contain anythig additional to this document.

KausBorealis

**VBA Noob** · 01-20-2008, 10:52 AM

Good to here.

I've now added the example file (post 4) for my code which I missed off the first time

VBA Noob

**royUK** · 01-20-2008, 11:22 AM

This works with the sheet index number, so from left to right with the first sheet being the Intro sheet.

In a Standard Module

Please Login or Register  to view this content.

In each of sheets that precede the final sheet add a Commandbutton with this code

Please Login or Register  to view this content.

In the final sheet add a button with this code

Please Login or Register  to view this content.

**KausBorealis** · 01-20-2008, 03:44 PM

Hi royUK,

I tried your code with 3 worksheets, it runs perfectly.
It is a great help for a teacher.

KausBorealis

**royUK** · 01-20-2008, 03:59 PM

Originally Posted by KausBorealis

Hi royUK,

I tried your code with 3 worksheets, it runs perfectly.
It is a great help for a teacher.

KausBorealis

Glad it helped

Hidden and visible sheets-swap

LinkBack

Thread Tools

Rate This Thread

Display

Hidden and visible sheets-swap

My code is not in my home (now)

I found your another useful reference

Thanks for royUK

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions