Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of all option buttons in several worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of all option buttons in several worksheets
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 "Memphis" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of all option buttons in several worksheets
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 -- Regards, OssieMac "Memphis" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of all option buttons in several worksheets
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. "Memphis" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking option buttons and returning data across worksheets | Excel Discussion (Misc queries) | |||
Change Header & Footer Info w/ Worksheet Option Buttons | Excel Discussion (Misc queries) | |||
Change text for option buttons in message box | Excel Programming | |||
Worksheets and Control Option Buttons | Excel Worksheet Functions | |||
How to change color of buttons if pressed? | Excel Programming |