A very simple question....

  • Thread starter Thread starter S.S.Sander
  • Start date Start date
S

S.S.Sander

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)
 
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
 
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

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)
 
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

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)
 
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
 
Back
Top