Change color of all option buttons in several worksheets

[Memphis]

I have on average 10 option buttons along with check boxes per each worksheet.
They are currently colored to blend with the sheet colors (2 to three
different colors per sheet).
I am adding a command button on the 1st sheet (FaceSheet) to strip all color
from all worksheets and to also strip the color of all option buttons and
check boxes. Is there a way to write some code that will select all option
buttons and check boxes on all worksheets and strip them of their color?
(Currently I am envisioning a super long array with all option buttons and
check box names, I would like to avoid this).
Also after the print action, I would like all worksheets and option buttons
and check boxes to get their colors back to the same colors as before.

Thank you

 

[Harald Staff]

Hi

Why don't you simply set the PrintObject properties to False ? (Properties
pane, or Rightclick, Format if they are from the Forms controls).

If not, this may or may not be a start, depending on what else your sheets
contains:

Sub test()
Dim L As Long
For L = 1 To Sheets(1).DrawingObjects.Count
MsgBox Sheets(1).DrawingObjects(L).Name
Next
End Sub

HTH. Best wishes Harald

 

[OssieMac]

I think that you will have to loop through the sheets and the objects on each
sheet like the following code. The code assumes that you have used ActiveX
controls from the Controls Toolbar and not controls from the Forms Toolbar.

To get your color codes, simply open the properties for the control, select
the required color from the palet and then copy the code and paste it into
the code in the VBA editor. (The VBA editor may automatically shorten the
code)

The first sub makes all the controls black and the second sets colors. The
reason for the nested Select Case is that you can have controls with the same
name on different worksheets.

Sub SetObjColorBlack()

Dim ws As Worksheet
Dim objCtrl As OLEObject

For Each ws In Worksheets
With ws
For Each objCtrl In .OLEObjects
If TypeName(objCtrl.Object) = "CheckBox" Or _
TypeName(objCtrl.Object) = "OptionButton" Then

objCtrl.Object.ForeColor = &H0& 'black

End If
Next
End With
Next ws
End Sub


Sub SetObjColor()
Dim ws As Worksheet
Dim objCtrl As OLEObject

For Each ws In Worksheets
With ws
For Each objCtrl In .OLEObjects
If TypeName(objCtrl.Object) = "CheckBox" Or _
TypeName(objCtrl.Object) = "OptionButton" Then
Select Case ws.Name
Case "Sheet1"
Select Case objCtrl.Name
Case "OptionButton1", "CheckBox1"
objCtrl.Object.ForeColor = &HFF& 'red
Case "OptionButton2", "CheckBox2"
objCtrl.Object.ForeColor = &HFF0000 'blue
End Select

Case "Sheet2"
Select Case objCtrl.Name
Case "OptionButton1", "CheckBox1"
objCtrl.Object.ForeColor = &HFF0000 'blue
Case "OptionButton2", "CheckBox2"
objCtrl.Object.ForeColor = &HFF& 'red
End Select
End Select
End If
Next
End With
Next ws

End Sub

 

[Memphis]

Thank you Guys,
I am going to give these a try and report back.
Harald: The option buttons and the check boxes need to print.
I am stripping the colors of the sheets and buttons for two reasons, 1st
legibility and second save on ink ;-) But I like the colors on the screen
since it places the user's focus on different sections of the page.

Thanks again.