Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checkbox name linked to a cell
How do I link a checkbox to a cell so that when the cell is renamed it also
changes the name in the checkbox? -- Tdp |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checkbox name linked to a cell
You could use an event macro that looks for a change to a particular cell and
then reacts to that typing change. If you want to try, then rightclick on the worksheet that owns both the checkbox and the cell that changes the caption name. Select view code and paste this into the newly opened code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'one cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If 'a checkbox from the Forms toolbar Me.CheckBoxes("Check box 1").Caption = Target.Value 'a checkbox from the control toolbox toolbar Me.CheckBox1.Caption = Target.Value End Sub I included sample code for both types of checkboxes (from the Forms toolbar and from the Control toolbox toolbar). You'll want to delete the code you don't need and make sure the checkbox names are correct. Tdp wrote: How do I link a checkbox to a cell so that when the cell is renamed it also changes the name in the checkbox? -- Tdp -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checkbox name linked to a cell
Thanks Dave,
Whilst I was waiting I tried different ways and came up with this one, Is there anything wrong with the following? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.CheckBox2.Caption = Worksheets("Sheet4").Range("C4").Value UserForm1.CheckBox3.Caption = Worksheets("Sheet4").Range("C5").Value End Sub -- Tdp "Dave Peterson" wrote: You could use an event macro that looks for a change to a particular cell and then reacts to that typing change. If you want to try, then rightclick on the worksheet that owns both the checkbox and the cell that changes the caption name. Select view code and paste this into the newly opened code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'one cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If 'a checkbox from the Forms toolbar Me.CheckBoxes("Check box 1").Caption = Target.Value 'a checkbox from the control toolbox toolbar Me.CheckBox1.Caption = Target.Value End Sub I included sample code for both types of checkboxes (from the Forms toolbar and from the Control toolbox toolbar). You'll want to delete the code you don't need and make sure the checkbox names are correct. Tdp wrote: How do I link a checkbox to a cell so that when the cell is renamed it also changes the name in the checkbox? -- Tdp -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checkbox name linked to a cell
This is on a userform?
Do you need to change the caption that often? With each change of selection? I would either use the the userform_initialize event to pick up the non-changing value or if the userform is shown non-modally, then I'd use the worksheet_change event. Tdp wrote: Thanks Dave, Whilst I was waiting I tried different ways and came up with this one, Is there anything wrong with the following? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.CheckBox2.Caption = Worksheets("Sheet4").Range("C4").Value UserForm1.CheckBox3.Caption = Worksheets("Sheet4").Range("C5").Value End Sub -- Tdp "Dave Peterson" wrote: You could use an event macro that looks for a change to a particular cell and then reacts to that typing change. If you want to try, then rightclick on the worksheet that owns both the checkbox and the cell that changes the caption name. Select view code and paste this into the newly opened code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'one cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If 'a checkbox from the Forms toolbar Me.CheckBoxes("Check box 1").Caption = Target.Value 'a checkbox from the control toolbox toolbar Me.CheckBox1.Caption = Target.Value End Sub I included sample code for both types of checkboxes (from the Forms toolbar and from the Control toolbox toolbar). You'll want to delete the code you don't need and make sure the checkbox names are correct. Tdp wrote: How do I link a checkbox to a cell so that when the cell is renamed it also changes the name in the checkbox? -- Tdp -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checkbox name linked to a cell
The caption may change now and then, but I`m after the ability to change the
checkbox caption. The code I try seems to work at the moment, but what I'm worried about if it can lead to some problems if the code is not complete? -- Tdp "Dave Peterson" wrote: This is on a userform? Do you need to change the caption that often? With each change of selection? I would either use the the userform_initialize event to pick up the non-changing value or if the userform is shown non-modally, then I'd use the worksheet_change event. Tdp wrote: Thanks Dave, Whilst I was waiting I tried different ways and came up with this one, Is there anything wrong with the following? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.CheckBox2.Caption = Worksheets("Sheet4").Range("C4").Value UserForm1.CheckBox3.Caption = Worksheets("Sheet4").Range("C5").Value End Sub -- Tdp "Dave Peterson" wrote: You could use an event macro that looks for a change to a particular cell and then reacts to that typing change. If you want to try, then rightclick on the worksheet that owns both the checkbox and the cell that changes the caption name. Select view code and paste this into the newly opened code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'one cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If 'a checkbox from the Forms toolbar Me.CheckBoxes("Check box 1").Caption = Target.Value 'a checkbox from the control toolbox toolbar Me.CheckBox1.Caption = Target.Value End Sub I included sample code for both types of checkboxes (from the Forms toolbar and from the Control toolbox toolbar). You'll want to delete the code you don't need and make sure the checkbox names are correct. Tdp wrote: How do I link a checkbox to a cell so that when the cell is renamed it also changes the name in the checkbox? -- Tdp -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Checkbox name linked to a cell
Why not just change the caption when the userform loads?
Option Explicit Private Sub UserForm_Initialize() Me.CheckBox2.Caption = Worksheets("Sheet4").Range("C4").Value Me.CheckBox3.Caption = Worksheets("Sheet4").Range("C5").Value End Sub Or am I missing something? Tdp wrote: The caption may change now and then, but I`m after the ability to change the checkbox caption. The code I try seems to work at the moment, but what I'm worried about if it can lead to some problems if the code is not complete? -- Tdp "Dave Peterson" wrote: This is on a userform? Do you need to change the caption that often? With each change of selection? I would either use the the userform_initialize event to pick up the non-changing value or if the userform is shown non-modally, then I'd use the worksheet_change event. Tdp wrote: Thanks Dave, Whilst I was waiting I tried different ways and came up with this one, Is there anything wrong with the following? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.CheckBox2.Caption = Worksheets("Sheet4").Range("C4").Value UserForm1.CheckBox3.Caption = Worksheets("Sheet4").Range("C5").Value End Sub -- Tdp "Dave Peterson" wrote: You could use an event macro that looks for a change to a particular cell and then reacts to that typing change. If you want to try, then rightclick on the worksheet that owns both the checkbox and the cell that changes the caption name. Select view code and paste this into the newly opened code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'one cell at a time End If If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub End If 'a checkbox from the Forms toolbar Me.CheckBoxes("Check box 1").Caption = Target.Value 'a checkbox from the control toolbox toolbar Me.CheckBox1.Caption = Target.Value End Sub I included sample code for both types of checkboxes (from the Forms toolbar and from the Control toolbox toolbar). You'll want to delete the code you don't need and make sure the checkbox names are correct. Tdp wrote: How do I link a checkbox to a cell so that when the cell is renamed it also changes the name in the checkbox? -- Tdp -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to place a checkbox in the middle of a cell, yet I want thecheckboxArea take up the entire cell. | Excel Discussion (Misc queries) | |||
How do I have a linked cell auto-size to fit the linked data? | Excel Discussion (Misc queries) | |||
How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
CheckBox in a cell? | Excel Worksheet Functions | |||
embed checkbox in a cell | Excel Discussion (Misc queries) |