ToggleButton OnAction Problem

[frz]

Hi experts..

I have had ALOT of trouble converting some checkboxes to togglebuttons. I thought that I had finally got around it doing the piece of code:

Sub test()

Dim i As Integer

For i = 1 To 6

If i Mod 2 = 0 Then
Range("A1").Cells(i, 1) = Rnd
Else: Range("A1").Cells(i, 1) = -1 * Rnd
End If

Call AddToggle(i)
Next i

End Sub

Sub AddToggle(i As Integer)

Dim RngTgBtn As Range
Dim Str As String

If Left(ActiveSheet.Cells(i, 1), 1) = "-" Then
Str = Left(ActiveSheet.Cells(i, 1), 5)
Else: Str = Left(ActiveSheet.Cells(i, 1), 4)
End If

Set RngTgBtn = Range("A1").Cells(4, 3 + i)
RngTgBtn.RowHeight = 16.5

With ActiveSheet
.OLEObjects.Add(ClassType:="Forms.ToggleButton.1").Select
With Selection
.Left = RngTgBtn.Left
.Top = RngTgBtn.Top
.Width = RngTgBtn.Width
.Height = RngTgBtn.Height
.Name = "myTglBtn" & i
End With
With Selection.Object
.Caption = Str
.Font.Size = 7
.Font.Bold = True
.Value = True
End With
.Shapes("myTglBtn" & i).OLEFormat.Object.LinkedCell = "'" & ActiveSheet.Name & "'!" & Range("A1").Cells(1, 3 + i).Address
'.Shapes("myTglBtn" & i).OLEFormat.Object.OnAction = ThisWorkbook.Name & "!" & "msg"
'This last line of code is not working - what to do :(
End With

End Sub

Sub msg()

MsgBox "Working", vbInformation

End Sub
But I cant assign any actions to the togglebutton:help - is it at all possible?

The CheckBox code I was redoing looked like this:

Sub chkb()

Dim RngChckBox As Range

Set RngChckBox = Range("A1").Cells(2, 2)
ActiveSheet.CheckBoxes.Add(RngChckBox.Left, RngChckBox.Top, RngChckBox.Width, RngChckBox.Height).Select
With Selection
.LinkedCell = "'Sheet1'!" & RngChckBox.Cells(1, 2).Address
.Characters.Text = "Test"
.OnAction = ThisWorkbook.Name & "!" & "msg"
End With

End Sub

Sub msg()

MsgBox "Working", vbInformation

End Sub

[Bob Phillips]

You have created an activeX control, so you need to add a click procedure to your worksheet code module.

[frz]

Is it possible to add the togglebutton as non-activeX control?

Due to the fact, that the adding of togglebuttons in the end is a dynamic process - and I dont know how to make those click_procedures dynamically??

If the answer is much more simple please do simplify :)

[Bob Phillips]

Not that I know of.

It is easy to add event code dynamically, but I am failing to integrate it with your code. I can run two routines separeately, but togetrher it crashes Excel.

Why not just have a button and change the caption to state whetehr it is on or off?