frz
03-05-2008, 05:51 AM
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
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