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.
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.
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)
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?
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?
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?
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.
