range and visibility

[Tanya]

Hi
I am trying to create a jeopardy game for school students to demonstrate
topic knowledge in Excel and don't have a lot of experience with macros. If
anyone would like to help me I would be grateful and happy to include their
name in the credits.

The sheets are running well as far as formula's. What I wold like to do is
run a macro from a command button which takes the player to sheet "Questions"
at a particular cell reference relative to the button selected i.e.
Commandbutton1 linked to Questions!B2

In addition to this I would like the commandbutton to become invisible.

I came up with this code to take the player to a particular cell, but it
isn't working and have no idea where to begin with changing the properties on
the commandbutton itself:

Private Sub CommandButton2_Click()

Sheets("Questions").Select
Range("B2").Select


End Sub

The second thing I would like to achieve is to have a commandbutton to reset
the workbook to how it looked at startup. i.e. void of any values
input/changes etc.

Any support is greatly appreciated and if anyone would like a copy of the
finished product to play with they can contact me off list.


Cheers
Tanya

 

[Joel]

Private Sub CommandButton2_Click()
Application.Goto Reference:=Worksheets("Questions").Range("B2"), _
scroll:=True
End Sub

To clear a sheet use

Worksheets("Questions").cells.clear

 

[Gord Dibben]

If you just want to clear contents and not formats use

Worksheets("Questions").cells.clear.contents


Gord Dibben MS Excel MVP

 

[Tanya]

Joel it is important that I don't delete any thing I created, your code wiped
the whole sheet.
Is there a way to clear cells that are not 'locked'?

 

[Tanya]

Thank you Joel
Is there a way to make the button invisible once it is activated?

 

[Dave Peterson]

Just a typo:
Worksheets("Questions").cells.clearcontents
(removed the dot between clear and contents.)

 

[Dave Peterson]

Add
Me.commandbutton2.visible = false
before the "End Sub" line

 

[Dave Peterson]

You have to loop through the cells.

dim myCell as range
with worksheets("Questions")
for each mycell in .usedrange.cells
if mycell.locked = true then
'skip it
else
mycell.clear 'or .clearcontents
end if
next mycell
end with

 

[Tanya]

Hi Dave
Sorry, didn't work for me.
It cleared the whole sheet of formulas and other text.
This sheet "Questions" has links to another sheet where it displays a
question and the student types in the correct response, formula tell the
student whether they are successful or not. This code wiped the whole page.
All that was left was the border formatting. [fortunately I have several
backup copies of this workbook]

I may be actually asking too much of Excel... Of course students could
always close the book and create a new one, if I save this as a template.
cheers
Tanya

 

[Tanya]

It was good thanks Dave except it also removed formatting. Is it possible to
avoid this? The page I am trying to clear has spaces for answers which have
borders around the cells, loosing these borders means the sheet is blank.

Thanks heaps.

cheers
Tanya

 

[Dave Peterson]

use
mycell.clearcontents
instead of
mycell.clear

 

[Dave Peterson]

See the other branch in this thread.

Hi Dave
Sorry, didn't work for me.
It cleared the whole sheet of formulas and other text.
This sheet "Questions" has links to another sheet where it displays a
question and the student types in the correct response, formula tell the
student whether they are successful or not. This code wiped the whole page.
All that was left was the border formatting. [fortunately I have several
backup copies of this workbook]

I may be actually asking too much of Excel... Of course students could
always close the book and create a new one, if I save this as a template.
cheers
Tanya

Just a typo:
Worksheets("Questions").cells.clearcontents
(removed the dot between clear and contents.)