range and visibility
 

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

range and visibility
 

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

range and visibility
 

If you just want to clear contents and not formats use

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


Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

range and visibility
 

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'?


"Gord Dibben" wrote:

If you just want to clear contents and not formats use

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


Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

range and visibility
 

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

"Joel" wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

range and visibility
 

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

Gord Dibben wrote:

If you just want to clear contents and not formats use

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

Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

--

Dave Peterson

range and visibility
 

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

Tanya wrote:

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

"Joel" wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

--

Dave Peterson

range and visibility
 

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 wrote:

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'?

"Gord Dibben" wrote:

If you just want to clear contents and not formats use

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


Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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



--

Dave Peterson

range and visibility
 

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

"Dave Peterson" wrote:

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

Gord Dibben wrote:

If you just want to clear contents and not formats use

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

Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

--

Dave Peterson

range and visibility
 

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" wrote:

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 wrote:

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'?

"Gord Dibben" wrote:

If you just want to clear contents and not formats use

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


Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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



--

Dave Peterson

range and visibility
 

use
mycell.clearcontents
instead of
mycell.clear



Tanya wrote:

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" wrote:

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 wrote:

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'?

"Gord Dibben" wrote:

If you just want to clear contents and not formats use

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


Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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



--

Dave Peterson


--

Dave Peterson

range and visibility
 

See the other branch in this thread.



Tanya wrote:

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

"Dave Peterson" wrote:

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

Gord Dibben wrote:

If you just want to clear contents and not formats use

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

Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 16:33:00 -0800, Joel wrote:

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

To clear a sheet use

Worksheets("Questions").cells.clear


"Tanya" wrote:

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

--

Dave Peterson


--

Dave Peterson