Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A very simple question....
Since i have just discovered the VBA-part of my office
2000 i have a - presumably - very simple Q. I wish to create a procedure that can change the colour of all the controls on a particular worksheet in a workbook. i.e. I have 6 optionbuttons and two checkboxes on a sheet I wish to assign a certain color to. I know how to extract the colour from a cell, assign it to a long-variable... But then.... My problem is to cycle through all the controls on a given sheet, assign the variable to the individual control(Since it seem as the OLEObjects dont have the .Backcolor property) Sorry for this obviously simple Q, but i have to start somewhere.... (And for me it isnīt simple- on the contrary) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A very simple question....
Are the controls from the Forms toolbar or Control Toolbox?
If the former, I don't know of a way to change color. If the latter, with the Control Toolbox open and in Design Mode, click on the Properties icon. There are a few choices for the BackColor property, but not the typical full range. It doesn't seem to me you can change the color with VBA. HTH, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A very simple question....
Here is one I just recorded to color a SHAPE. maybe it will help
Sub Macro12() ActiveSheet.Shapes("Rectangle 2").Select Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11 Selection.ShapeRange.Fill.Transparency = 0# Selection.ShapeRange.Line.Weight = 0.75 Selection.ShapeRange.Line.DashStyle = msoLineSolid Selection.ShapeRange.Line.Style = msoLineSingle Selection.ShapeRange.Line.Transparency = 0# Selection.ShapeRange.Line.Visible = msoTrue Selection.ShapeRange.Line.ForeColor.SchemeColor = 48 Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255) End Sub "S.S.Sander" wrote in message ... Since i have just discovered the VBA-part of my office 2000 i have a - presumably - very simple Q. I wish to create a procedure that can change the colour of all the controls on a particular worksheet in a workbook. i.e. I have 6 optionbuttons and two checkboxes on a sheet I wish to assign a certain color to. I know how to extract the colour from a cell, assign it to a long-variable... But then.... My problem is to cycle through all the controls on a given sheet, assign the variable to the individual control(Since it seem as the OLEObjects dont have the .Backcolor property) Sorry for this obviously simple Q, but i have to start somewhere.... (And for me it isnīt simple- on the contrary) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A very simple question....
Assuming an option button from the Control Toolbox Toolbar.
It sounds like you want the optionbutton to have the same backcolor as the cell under it. If so, I believe this is what you want: Sub Tester9() Dim oOpBtn As MSForms.OptionButton Dim lngColor as Long Dim oleObj as OleObject For Each oleObj In ActiveSheet.OLEObjects If TypeOf oleObj.Object Is MSForms.OptionButton Then lngColor = oleObj.TopLeftCell.Interior.Color Set oOpBtn = oleObj.Object oOpBtn.BackColor = lngColor End If Next End Sub The OleObject is the container of the OptionButton, and the Object property of the OleObject is the OptionButton itself. It is the optionbutton that has the backcolor property. I declared oOpBtn as MSForms.OptionButton and assigned it for clarity, but you can work directly with (in this case) oleObj.Object.Backcolor if you wish. -- Regards, Tom Ogilvy S.S.Sander wrote in message ... Since i have just discovered the VBA-part of my office 2000 i have a - presumably - very simple Q. I wish to create a procedure that can change the colour of all the controls on a particular worksheet in a workbook. i.e. I have 6 optionbuttons and two checkboxes on a sheet I wish to assign a certain color to. I know how to extract the colour from a cell, assign it to a long-variable... But then.... My problem is to cycle through all the controls on a given sheet, assign the variable to the individual control(Since it seem as the OLEObjects dont have the .Backcolor property) Sorry for this obviously simple Q, but i have to start somewhere.... (And for me it isnīt simple- on the contrary) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A very simple question....
Iīm not sure how to put this.....
Iīm grateful.... Thx.... A LOT.... Very good understanding of my prob. Good solution... And a very learning experience being here.... THX Again... S.S.Sander -----Original Message----- Assuming an option button from the Control Toolbox Toolbar. It sounds like you want the optionbutton to have the same backcolor as the cell under it. If so, I believe this is what you want: Sub Tester9() Dim oOpBtn As MSForms.OptionButton Dim lngColor as Long Dim oleObj as OleObject For Each oleObj In ActiveSheet.OLEObjects If TypeOf oleObj.Object Is MSForms.OptionButton Then lngColor = oleObj.TopLeftCell.Interior.Color Set oOpBtn = oleObj.Object oOpBtn.BackColor = lngColor End If Next End Sub The OleObject is the container of the OptionButton, and the Object property of the OleObject is the OptionButton itself. It is the optionbutton that has the backcolor property. I declared oOpBtn as MSForms.OptionButton and assigned it for clarity, but you can work directly with (in this case) oleObj.Object.Backcolor if you wish. -- Regards, Tom Ogilvy S.S.Sander wrote in message ... Since i have just discovered the VBA-part of my office 2000 i have a - presumably - very simple Q. I wish to create a procedure that can change the colour of all the controls on a particular worksheet in a workbook. i.e. I have 6 optionbuttons and two checkboxes on a sheet I wish to assign a certain color to. I know how to extract the colour from a cell, assign it to a long-variable... But then.... My problem is to cycle through all the controls on a given sheet, assign the variable to the individual control(Since it seem as the OLEObjects dont have the .Backcolor property) Sorry for this obviously simple Q, but i have to start somewhere.... (And for me it isnīt simple- on the contrary) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple question.... | Excel Discussion (Misc queries) | |||
Simple question..I think | Excel Discussion (Misc queries) | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Simple Question | Excel Programming |