Thanks, that is amazing the was case works that way and that you can have
multiple lines after the Case statement. I would have never guessed. It
works great.

The last thing that needs modification is to change the code for the
wks1Name = to use sheet4 (no quotes) instead of "Tour1". I tried this by
modifying the code so the
wks1Name.Visible = xlSheetVisible would be converted to the syntax upon
execution to sheet4.Visible = xlSheetvisible but got the follow error.

Compile error:
Invalid qualifier

I click on help so I wouldn't bother you again and the following came up

Invalid qualifier

Qualifiers are used for disambiguation. This error has the following cause
and solution:


The qualifier does not identify a project, module, object, or a variable of
user-defined type within the current scope.
Check the spelling of the qualifier. Make sure that the qualifying
identifier is within the current scope. For example, a variable of
user-defined type in a Private module is visible only within that module.

I tried several thing but they didn't work.

Here is the modified code.

Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim wks1Name As String
Dim wks2Name As String
Dim wkbkPwd As String

wkbkPwd = "hi"

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

wks1Name = ""
wks2Name = ""
Select Case LCase(myCBX.Name)
Case Is = LCase("check box 140")
wks1Name = Sheet4 'Original sheet name not renamed sheet name
Tour1
wks2Name = Sheet40 '... Tour1 Data
Case Is = LCase("check box 141")
wks1Name = Sheet5 '... Tour2
wks2Name = Sheet41 '.Tour2 Data

End Select

If wks1Name = "" Then
MsgBox "Design error--no sheet assigned to this checkbox"
Exit Sub
End If

'unprotect the workbook
With ThisWorkbook
Application.ScreenUpdating = False
'.Unprotect Password:=wkbkPwd
If myCBX.Value = xlOn Then
wks1Name.Visible = xlSheetVisible
wks2Name.Visible = xlSheetVisible
Else
wks1Name.Visible = xlSheetHidden
wks2Name.Visible = xlSheetHidden
End If
'.Protect Password:=wkbkPwd, structu=True, Windows:=False
End With
End Sub

"Dave Peterson" wrote in message
...
Instead of two separate cases (once the first is satisfied, the second
(and
subsequent) won't even be checked):

Case Is = LCase("check box 140"): wks1Name = "Tour1"
Case Is = LCase("check box 140"): wks2Name = "Tour1 Data"


Try combining the cases like:

Case Is = LCase("check box 140")
wks1Name = "Tour1"
wks2Name = "Tour1 Data"


Breck wrote:

Sorry for the poor explanation. I have have modified the code to what I
thought would work. It should be more more helpful in understanding.
Except
I'm getting this error when I run it.

"Runtime error '9': "
"Subscript out of range"

This line is highlighted depending on whether you are checking the box or
removing the checkmark

.Worksheets(wks2Name).Visible = xlSheetVisable
.Worksheets(wks2Name).Visible = xlSheetHidden

Here is the modified code

Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim wks1Name As String
Dim wks2Name As String
Dim wkbkPwd As String

wkbkPwd = "hi"

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

wks1Name = ""
wks2Name = ""
Select Case LCase(myCBX.Name)
Case Is = LCase("check box 140"): wks1Name = "Tour1"
<=== this needs to be sheet4
Case Is = LCase("check box 140"): wks2Name = "Tour1 Data" <===
this needs to be sheet40
Case Is = LCase("check box 141"): wks1Name = "Tour2"
<=== This needs to be sheet5
Case Is = LCase("check box 142"): wks2Name = "Tour2 Data" <===
this needs to be sheet41

End Select

If wks1Name = "" Then
MsgBox "Design error--no sheet assigned to this checkbox"
Exit Sub
End If

'unprotect the workbook
With ThisWorkbook
Application.ScreenUpdating = False
'.Unprotect Password:=wkbkPwd
If myCBX.Value = xlOn Then
.Worksheets(wks1Name).Visible = xlSheetVisible
.Worksheets(wks2Name).Visible = xlSheetVisible
Else
.Worksheets(wks1Name).Visible = xlSheetHidden
.Worksheets(wks2Name).Visible = xlSheetHidden
End If
'.Protect Password:=wkbkPwd, structu=True, Windows:=False
End With
End Sub

"Dave Peterson" wrote in message
...
I'm not sure I understand...

First, when the workbook is protected (with its structure checked),
then
the
user can't rename, delete, move, insert any sheet. You'll have to
provide
another macro to do any of those things.

But I don't understand what the difference between sheet4 and sheet40
is.
Why
would you need two sheets and what one gets hidden?

I don't understand enough to help.

Sorry.



Breck wrote:

Thank you. This is so cool it works like a charm.

Since each sheet can be renamed by the user from the default name to a
tournament name how can it be modified to use the original sheet name
(name)
which doesn't change. The workbook in its template form has a sheet
for
each
tournament and has a corresponding sheet with data. So sheet4 which
has a
default name "Tour1" has a data input sheet40 named "Tour1 Data". When
a
user enters a Tournament Name on a setup sheet the Tab or sheet name
is
changed to name that is entered. This way a used can quickly click on
the
sheet name to go to the information for that tournament. At the end of
the
season all Tournaments will have data in them. I'm hiding future
unused
sheets which total 16 tournaments and 16 tournament data so there are
just
the minimum sheets that are currently being used which have a tab
showing.
Sorry for the long explanation. Does this make any sense?

"Dave Peterson" wrote in message
...
I'd use a macro that is assigned to the checkbox.

You can assign the same macro to each of the checkboxes from the
Forms
toolbar.

If the checkbox is checked, then the worksheet is visible:

Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim wksName As String
Dim wkbkPwd As String

wkbkPwd = "hi"

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

wksName = ""
Select Case LCase(myCBX.Name)
Case Is = LCase("check box 1"): wksName = "Sheet2"
Case Is = LCase("check box 2"): wksName = "SheetNameHere"
End Select

If wksName = "" Then
MsgBox "Design error--no sheet assigned to this checkbox"
Exit Sub
End If

'unprotect the workbook
With ThisWorkbook
.Unprotect Password:=wkbkPwd
If myCBX.Value = xlOn Then
.Worksheets(wksName).Visible = xlSheetVisible
Else
.Worksheets(wksName).Visible = xlSheetHidden
End If
.Protect Password:=wkbkPwd, structu=True, Windows:=False
End With
End Sub




Breck wrote:

I hadn't thought about protecting the structure. Good Idea. The
workbook
that I'm creating is for keeping volleyball stats for High School
games
and
Club Volleyball Tournaments there will be a couple of dozen teams
at
the
volleyball club that will be using it. I'm just trying to plan for
all
of
the possibilities that inexperienced users might encounter. If I
can't
figure out code I make it so users can't hide or unhide the sheets
manually.
I've used macro recorder to get the following code. I just haven't
figured
out how to get the Value = xlOn/xlOff to update the check box yet.
I'm
still
trying. Thanks.

Sub Checked()
' Checked Macro
'
ActiveSheet.Shapes("Check Box 140").Select
With Selection
.Value = xlOn

End With

ActiveCell.Select
End Sub

Sub UnChecked()
' UnChecked Macro
'
ActiveSheet.Shapes("Check Box 140").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = False
End With
ActiveCell.Select
End Sub

"Dave Peterson" wrote in message
...
You could protect the workbook's structure and make your macro
(through
the
checkbox) the only way to hide/unhide a worksheet.

Or maybe you could drop the UDF and just use a workbook event
that
updates
the
checkbox. I'm not sure how the UDF would be accurate at any
particular
point.

Breck wrote:

Yes that is exactly the results that I got. Unfortunately I need
the
best of both worlds. I have tried write VBA code but I have just
started 2 weeks ago learning VBA. The last programing class that
I
had
was in 1972 my first year in college and I failed that course. I
need
the check box to return the current status of the sheet because
a
user
can hide or unhide a sheet by right clicking on the tab. I want
the
user to be able to hide "checked" or unhide "not checked" a
sheet
by
clicking on the check box. I just haven't figured out yet how to
have
the check boxed update. I know about the start code when a sheet
become active. I just haven't been able to figure out code to
check
for status of a hidden/active sheet in another part of the
workbook
and update the check box for that status. thanks for your
response.

Breck

On Jan 26, 10:31 am, Dave Peterson
wrote:
I put a checkbox from the Forms toolbar on a worksheet.

I rightclicked on the checkbox and chose Format
Control|Control
tab.

I specified A1 as the cell link.

I put a formula in that cell that evaluated to true or false.
When
the
cell
returned true, the checkbox was checked. When the formula
returned
false, the
checkbox was unchecked.

But be aware that if I checked or unchecked that checkbox
manually,
then the
formula in that linked cell was lost.

Breck wrote:

Is there a way to update a check box from a cell?

I have a formula in cell "P3" that returns either True or
False. I
would like to have the Check Box "Check Box 140" update to
checked
if
True or unchecked if False. The formula in cell "P3" is a
UDF
that
returns True if a sheet is hidden and False if it isn't

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson