MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to Use Excel Objects ?

  Asked By: Aloisa    Date: Mar 07    Category: MS Office    Views: 932

My Requirement is,

1.I am having a xls file called Test.xls
2.It contains data and some cells are filled with color
3.I want to get the color of those cell
4.I want to get the width of cell and cell contains wrapped text or not
5.I want to get Coloumn title Horizontal value and vertical value



3 Answers Found

Answer #1    Answered By: Haru Tanaka     Answered On: Mar 07

Sounds like you need some help with the VBA Object model in Excel.
There are things you can do with applications, workbooks, worksheets,
and ranges (a range can be a single cell).

You can get properties, assign values, trigger events, do stuff when
events are triggered, etc. For more information, please refer to the VBE
Help file, and view the extensive documentation on the object model.

Once you have done that, it will become clear that you want to mess
around with the attributes of a range (in this case, the cell  the cursor
is on).

A neat way to find out what commands you should use is to record a short

Put your cursor on cell B4, record a macro, and do the following:
Change the colour of the cell.
Change the width  of the cell.
Change the text  to wrapped text.

Not sure what you mean by column title  horizontal and vertical value.
But whatever you meant, do that as well.

Stop macro recording, and view what you have just recorded in the VBE.
It should look something like this:
Sub Macro2()
' Macro2 Macro
' Macro recorded 26/07/2006 by paul.vermeulen

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Columns("C:C").ColumnWidth = 11.57
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

To now write your code the format will become very clear:

Dim CellColour as variant
Dim CellWidth as variant

CellColour = selection.interior.colorindex.value
CellWidth = selection.columnwidth.value

Have a go, and feel free to post the final code.

Answer #2    Answered By: Jacob Evans     Answered On: Mar 07

I think he wants to pick up the text  from Col A for the row the taget cell  is
in and Row 1 for the column the target cell is in.

Answer #3    Answered By: Chaths Massri     Answered On: Mar 07

For number 2 & 3, below is a function I wrote a while back. The
syntax is =Color(A1). This will tell you the number value of the
color. Excel00 has 56 colors and I think -4142 is empty or white, I
can't remember.

Function Color(arg1)
'Created by BJeffery

Color = arg1.Interior.ColorIndex
End Function

Didn't find what you were looking for? Find more on How to Use Excel Objects ? Or get search suggestion and latest updates.