Administration Form with Radio Controls
 

Hi

If I have an administration form in Excel that I want to send to colleagues
with radio buttons to select choices, how can I run a control process to
ensure that for the various items, the user has selected a choice for each
area i.e. that no area has simply been missed?

If possible, a simple robust process would be preferable.

Many thanks

Tim

Administration Form with Radio Controls
 

This is not easy. I assume each set of radio buttons are in a Group
box. You can't find out which radio buttons are in each group box but
you can find which Group box a button is located. This is a 3 step
process.

1) Get the names of each group box and set the status of the Group box
to False indicating no radio buttons where found to be set.
2) Find all the radio buttons and get the Group box name and the Value
(checked or not checked). then if check change the status of the group
to True
3) then check all the Group boxes t find out if they are true or
false.


Sub TestGroups()
Dim Groups()
GroupCount = 0

'create an array of all the group boxes on the sheet
'first item is group name
For Each shp In ActiveSheet.Shapes

If shp.FormControlType = xlGroupBox Then
ReDim Preserve Groups(0 To GroupCount, 0 To 1)
Groups(GrouCount, 0) = shp.OLEFormat.Object.Name
'set value to false indicating not initialized
Groups(GrouCount, 0) = shp.OLEFormat.Object.Name
Groups(GrouCount, 1) = False
GroupCount = GroupCount + 1
End If
Next shp

'now get all the control buttons
For Each shp In ActiveSheet.Shapes

If shp.FormControlType = xlOptionButton Then
GroupName = shp.OLEFormat.Object.GroupBox.Name

If shp.OLEFormat.Object.Value = 1 Then
'find group name and set to true
For i = 0 To UBound(Groups, 1)
If Groups(i, 0) = GroupName Then
Groups(i, 1) = True
Exit For
End If
Next i
End If
End If
Next shp

'display the groups not initialized
For i = 0 To UBound(Groups, 1)
If Groups(i, 1) = False Then
MsgBox (Groups(i, 0) & " Has no value set")
End If
Next i


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145165

Administration Form with Radio Controls
 

Hi Joel

Many thanks for posting this which I will be trying out on the sheet.

Best wishes

Tim

"joel" wrote in message
...

This is not easy. I assume each set of radio buttons are in a Group
box. You can't find out which radio buttons are in each group box but
you can find which Group box a button is located. This is a 3 step
process.

1) Get the names of each group box and set the status of the Group box
to False indicating no radio buttons where found to be set.
2) Find all the radio buttons and get the Group box name and the Value
(checked or not checked). then if check change the status of the group
to True
3) then check all the Group boxes t find out if they are true or
false.


Sub TestGroups()
Dim Groups()
GroupCount = 0

'create an array of all the group boxes on the sheet
'first item is group name
For Each shp In ActiveSheet.Shapes

If shp.FormControlType = xlGroupBox Then
ReDim Preserve Groups(0 To GroupCount, 0 To 1)
Groups(GrouCount, 0) = shp.OLEFormat.Object.Name
'set value to false indicating not initialized
Groups(GrouCount, 0) = shp.OLEFormat.Object.Name
Groups(GrouCount, 1) = False
GroupCount = GroupCount + 1
End If
Next shp

'now get all the control buttons
For Each shp In ActiveSheet.Shapes

If shp.FormControlType = xlOptionButton Then
GroupName = shp.OLEFormat.Object.GroupBox.Name

If shp.OLEFormat.Object.Value = 1 Then
'find group name and set to true
For i = 0 To UBound(Groups, 1)
If Groups(i, 0) = GroupName Then
Groups(i, 1) = True
Exit For
End If
Next i
End If
End If
Next shp

'display the groups not initialized
For i = 0 To UBound(Groups, 1)
If Groups(i, 1) = False Then
MsgBox (Groups(i, 0) & " Has no value set")
End If
Next i


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=145165