Update check box from cell

[Breck]

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]

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]

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

 

[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]

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]

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, structure:=True, Windows:=False
End With
End Sub

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

 

[Breck]

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]

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.

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?

 

[Breck]

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, structure:=True, Windows:=False
End With
End Sub

 

[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"

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, structure:=True, Windows:=False
End With
End Sub

 

[Breck]

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, structure:=True, Windows:=False
End With
End Sub

 

[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

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, structure:=True, Windows:=False
End With
End Sub

 

[Breck]

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]

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

Good luck.

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.