Hi

I use combo and list boxes regularly to insert data into a cell. I have
never been able to work out how to bring the cell background colour or font
colour along with the data. Can anyone help?

Kind Regards

Mick

Mick

Are you using Combo and list from Forms or from ActiveControls?
Do you want to extract the color background from a cell and copty it t
the Combo or List?

Just to be sure on the problem... ;)

Regards

Jose Luis

Mick Wrote:
Hi

I use combo and list boxes regularly to insert data into a cell.
have
never been able to work out how to bring the cell background colour o
font
colour along with the data. Can anyone help?

Kind Regards

Mic

--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=39361

Jose

I use the Control Toolbox mostly. What I want to do is to be able to
use not only the cell value from a list stored elsewhere in the sheet,
but also the backgound colour and font colour as it is in the list.
i.e. if a cell in the list contains the value of say 10 with a font
colour of red on a yellow background. At the moment all I can se is the
value in the drop down list, but not the font colour or the background
colour.

Hope that make sense?

Kind Regards

Mick


*** Sent via Developersdex http://www.developersdex.com ***

Hi Mick



As far as i know you when you assign color to a listbox it's to all the
entries of that list, not individual cells in the ListBox, but i'm not
sure cause i'm relativitily new to VBA.

Here is some piece of code that could help you in your search.


Code:
--------------------
Sub Colorcell()
MsgBox "Color font " & ActiveCell.Font.Color ' just to see the font color
MsgBox "Color interior " & ActiveCell.Interior.Color ' just to see the interior color
ListBox1.ListFillRange = "E5:E15"
ListBox1.ForeColor = ActiveCell.Font.Color 'assigns the color from the cell to the LIstBox
ListBox1.BackColor = ActiveCell.Interior.Color 'the same...
End Sub
--------------------



Hope it helps in some way

Regards

Jose Luis

Mick Southam Wrote:
Jose

I use the Control Toolbox mostly. What I want to do is to be able to
use not only the cell value from a list stored elsewhere in the sheet,
but also the backgound colour and font colour as it is in the list.
i.e. if a cell in the list contains the value of say 10 with a font
colour of red on a yellow background. At the moment all I can se is
the
value in the drop down list, but not the font colour or the background
colour.

Hope that make sense?

Kind Regards

Mick


*** Sent via Developersdex http://www.developersdex.com ***


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=393614

Hi!

I seem to have a similar problem about setting the ComboBox color. I tried
to use the code in the last message, but it seemed to just give some error
about "object required". Is this code supposed to work with ComboBoxes as
well? Can I just change ListBox1.ForeColor to ComboBox.Forecolor and so on?
This just doesn't seem to work.

Greetings,
Erkka

"jose luis" wrote:


Hi Mick



As far as i know you when you assign color to a listbox it's to all the
entries of that list, not individual cells in the ListBox, but i'm not
sure cause i'm relativitily new to VBA.

Here is some piece of code that could help you in your search.


Code:
--------------------
Sub Colorcell()
MsgBox "Color font " & ActiveCell.Font.Color ' just to see the font color
MsgBox "Color interior " & ActiveCell.Interior.Color ' just to see the interior color
ListBox1.ListFillRange = "E5:E15"
ListBox1.ForeColor = ActiveCell.Font.Color 'assigns the color from the cell to the LIstBox
ListBox1.BackColor = ActiveCell.Interior.Color 'the same...
End Sub
--------------------



Hope it helps in some way

Regards

Jose Luis

Mick Southam Wrote:
Jose

I use the Control Toolbox mostly. What I want to do is to be able to
use not only the cell value from a list stored elsewhere in the sheet,
but also the backgound colour and font colour as it is in the list.
i.e. if a cell in the list contains the value of say 10 with a font
colour of red on a yellow background. At the moment all I can se is
the
value in the drop down list, but not the font colour or the background
colour.

Hope that make sense?

Kind Regards

Mick


*** Sent via Developersdex http://www.developersdex.com ***


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=393614

Erkka,

Yes you can change from ListBox to ComboBox. The example in my las
post was tried in Excel 2003, if it's not working could be because it'
for Control Tools not Forms or may be because of teh name of the ListBo
used. Check the name in the properties list, change if neccesary fro
ListBox1.ForeColor to TheName.ForeColor.

Hope this helps to make it run.

Regards

Jose Luis


Erkka Wrote:
Hi!

I seem to have a similar problem about setting the ComboBox color.
tried
to use the code in the last message, but it seemed to just give som
error
about "object required". Is this code supposed to work with ComboBoxe
as
well? Can I just change ListBox1.ForeColor to ComboBox.Forecolor and s
on?
This just doesn't seem to work.

Greetings,
Erkka

"jose luis" wrote:


Hi Mick



As far as i know you when you assign color to a listbox it's to al
the
entries of that list, not individual cells in the ListBox, but i'
not
sure cause i'm relativitily new to VBA.

Here is some piece of code that could help you in your search.


Code:
--------------------
Sub Colorcell()
MsgBox "Color font " & ActiveCell.Font.Color ' just to see th
font color
MsgBox "Color interior " & ActiveCell.Interior.Color ' just to se
the interior color
ListBox1.ListFillRange = "E5:E15"
ListBox1.ForeColor = ActiveCell.Font.Color 'assigns the color fro
the cell to the LIstBox
ListBox1.BackColor = ActiveCell.Interior.Color 'the same...
End Sub
--------------------



Hope it helps in some way

Regards

Jose Luis

Mick Southam Wrote:
Jose

I use the Control Toolbox mostly. What I want to do is to be abl
to
use not only the cell value from a list stored elsewhere in th
sheet,
but also the backgound colour and font colour as it is in th
list.
i.e. if a cell in the list contains the value of say 10 with
font
colour of red on a yellow background. At the moment all I can s
is
the
value in the drop down list, but not the font colour or th
background
colour.

Hope that make sense?

Kind Regards

Mick


*** Sent via Developersdex http://www.developersdex.com ***


--
jose luis

------------------------------------------------------------------------
jose luis's Profile
http://www.excelforum.com/member.php...o&userid=13312
View this thread
http://www.excelforum.com/showthread...hreadid=393614



--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=39361