Toggle Button Caption

[5elpep]

Hi all,

I have a spin button on my worksheet which controls the
contents of a particular cell. I also have a toggle box on the
same worksheet. I need the caption on the toggle box to change
in real time as the cell changes.

Does anyone have any ideas as to how this might be achieved?

Thanks

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
Me.OLEObjects("ToggleButton1").Object.Caption = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[5elpep]

Thanks for the input BP. Unfortunately it doesn't seem to work. I
have pasted
the code in and I have changed "H1" to the relevent cell.

I click on the spin button, which changes the contents of the cell but
the toggle
button caption just stays the same....

 

[Guest]

I suspect this may be because the spin button change, although it may be
linked to a cell on the sheet, still doesn't trigger the Worksheet_Change
event.
Instead, you could try this in the same worksheet code:

Private Sub SpinButton1_Change()
Me.OLEObjects("ToggleButton1").Object.Caption = Range("H1").Value
End Sub

which incidentally still works if you edit H1 directly. Adjust
"ToggleButton1" to the name of your toggle button, and 'SpinButton1' to your
spin button's id (can be done by doubleclicking the spin button opn the sheet
in design mode).

You might also try this line:

OLEObjects("ToggleButton1").Object.Caption = SpinButton1.Value
or even shorter:
ToggleButton1.Caption = SpinButton1.Value

instead of 'Me.OLEjects... ' above, adjusting to your control's IDs, of
course, and they also work if you change H1 directly.

 

[5elpep]

Private Sub SpinButton1_Change()
Me.OLEObjects("ToggleButton1").Object.Caption = Range("H1").Value
End Sub

The above worked perfectly. Thanks for your help guys.