Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add-In Visibility | Excel Discussion (Misc queries) | |||
Shared add-in UDF visibility | Excel Programming | |||
Worksheets - Visibility | Excel Programming | |||
Application visibility | Excel Programming | |||
Visibility | Excel Programming |