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

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

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

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

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

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

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

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

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

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

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

If you go to the VBE and hit ctrl-r to see the project explorer.

Select your project and expand all those levels--including the worksheets.'

You'll see things like:
Sheet1(Tour1)
Sheet2(Tour1 Data)
Sheet3(Tour2)
....

The name inside ()'s is the name the user sees. The name in front of that is
called the code name and it should be much more difficult for the user to change
this.

Maybe you could incorporate those code names into your code:

Option Explicit
Sub testme2()

Dim mySheet As Worksheet
Dim myName As String
Dim wks As Worksheet

myName = "hi"

Set mySheet = Nothing
Select Case LCase(myName)
Case Is = "hi"
Set mySheet = Sheet1
Case Is = "bye"
Set mySheet = Sheet2
End Select

If mySheet Is Nothing Then
'something bad happened
Else
mySheet.Visible = xlSheetHidden
End If
End Sub


Breck wrote:

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

--

Dave Peterson

Thank you Dave for all of you help and time.

Everything is working perfectly. I have learned so much from you. I was
writing 16 different macros, now I have one. I've learning the amazing
capabilities of case and that it can handle more that one line. I now better
understand that the reason that my last code didn't work and how to
incorporate the code name into many of the functions that start with
worksheet. You have been so helpful and generous with your knowledge.

"Dave Peterson" wrote in message
...
If you go to the VBE and hit ctrl-r to see the project explorer.

Select your project and expand all those levels--including the
worksheets.'

You'll see things like:
Sheet1(Tour1)
Sheet2(Tour1 Data)
Sheet3(Tour2)
...

The name inside ()'s is the name the user sees. The name in front of that
is
called the code name and it should be much more difficult for the user to
change
this.

Maybe you could incorporate those code names into your code:

Option Explicit
Sub testme2()

Dim mySheet As Worksheet
Dim myName As String
Dim wks As Worksheet

myName = "hi"

Set mySheet = Nothing
Select Case LCase(myName)
Case Is = "hi"
Set mySheet = Sheet1
Case Is = "bye"
Set mySheet = Sheet2
End Select

If mySheet Is Nothing Then
'something bad happened
Else
mySheet.Visible = xlSheetHidden
End If
End Sub


Breck wrote:

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

--

Dave Peterson

I'm still confused over what you wanted <vbg, but it sounds like you're off and
running!

Good luck.

Breck wrote:

Thank you Dave for all of you help and time.

Everything is working perfectly. I have learned so much from you. I was
writing 16 different macros, now I have one. I've learning the amazing
capabilities of case and that it can handle more that one line. I now better
understand that the reason that my last code didn't work and how to
incorporate the code name into many of the functions that start with
worksheet. You have been so helpful and generous with your knowledge.

"Dave Peterson" wrote in message
...
If you go to the VBE and hit ctrl-r to see the project explorer.

Select your project and expand all those levels--including the
worksheets.'

You'll see things like:
Sheet1(Tour1)
Sheet2(Tour1 Data)
Sheet3(Tour2)
...

The name inside ()'s is the name the user sees. The name in front of that
is
called the code name and it should be much more difficult for the user to
change
this.

Maybe you could incorporate those code names into your code:

Option Explicit
Sub testme2()

Dim mySheet As Worksheet
Dim myName As String
Dim wks As Worksheet

myName = "hi"

Set mySheet = Nothing
Select Case LCase(myName)
Case Is = "hi"
Set mySheet = Sheet1
Case Is = "bye"
Set mySheet = Sheet2
End Select

If mySheet Is Nothing Then
'something bad happened
Else
mySheet.Visible = xlSheetHidden
End If
End Sub


Breck wrote:

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

--

Dave Peterson

--

Dave Peterson