Hi Everybody.
I am trying to set all checkboxes and radio buttons on a spreadsheet to False every time a drop down list value changes and I am using the following code:
However, this will only work fine with ungrouped ActiveX controls.
I have several panes of controls grouped together and these will not switch to False.
It seems like grouped controls becomes shapes and unfortunately I don't find a way to modify their value property via .GroupItems
However, I manage to modify their visibility.
I have tried statements like (commented and in bold):
The reason for keeping some controls grouped is to switch their visibility in groups (see full code below).
Also, not all checkboxes and radio buttons are associated to cells, I guess I could associate each of them to a cell and switch the cell values to false as a workaround.
However, it would be good to understand what I am doing wrong..
Any advice much appreciated!
Thanks,
Aldo
Complete code:
I am trying to set all checkboxes and radio buttons on a spreadsheet to False every time a drop down list value changes and I am using the following code:
Code:
Dim ole As OLEObject
For Each ole In ActiveSheet.OLEObjects
If ole.progID = "Forms.CheckBox.1" Or ole.progID = "Forms.OptionButton.1" Then
ole.Object.Value = False
End If
Next oleI have several panes of controls grouped together and these will not switch to False.
It seems like grouped controls becomes shapes and unfortunately I don't find a way to modify their value property via .GroupItems
However, I manage to modify their visibility.
I have tried statements like (commented and in bold):
Code:
For Each T In ActiveSheet.Shapes("Advertisement2").GroupItems
T.Visible = True
' [B]If T.Type = "Forms.CheckBox.1" Then T.Object.Value = False[/B]
' [B]If T.Type = "Forms.CheckBox.1" Then T.Object.Value = 0
[/B] ' [B]Worksheets("Form").OLEObjects.Value = True[/B]
' [B]If TypeName(T) = "CheckBox" Then T.Object.Value = True[/B]
NextAlso, not all checkboxes and radio buttons are associated to cells, I guess I could associate each of them to a cell and switch the cell values to false as a workaround.
However, it would be good to understand what I am doing wrong..
Any advice much appreciated!
Thanks,
Aldo
Complete code:
Code:
Private Sub AppType_Change()
Dim Status As Boolean
Dim SelectedAppType As String
SelectedAppType = ActiveSheet.AppType.Text
Dim ole As OLEObject
For Each ole In ActiveSheet.OLEObjects
If ole.progID = "Forms.CheckBox.1" Or ole.progID = "Forms.OptionButton.1" Then
ole.Object.Value = False
End If
Next ole
If SelectedAppType = "Advertisement" Then
For Each T In ActiveSheet.Shapes("Advertisement2").GroupItems
T.Visible = True
' MsgBox T.Object.Value
' If T.Type = "Forms.CheckBox.1" Then T.Object.Value = False
' Worksheets("Form").OLEObjects("DMRC6_ChimneyInUse").Object.Value = False
' Worksheets("Form").OLEObjects.Value = True
' If TypeName(T) = "CheckBox" Then T.Object.Value = True
' Worksheets("Form").OLEObjects("DMRC6_ChimneyInUse").Object.Value = False
' Worksheets("Form").OLEObjects.Value = True
Next
Else
For Each T In ActiveSheet.Shapes("Advertisement2").GroupItems
T.Visible = False
' T.Object.Value = False
Next
End If
Last edited:
