Object Creation
 

I am working on a VB macro that takes spreadsheet from a third party vendor
and grabs the headers for each column. It then allows to the user of the form
to line up these incoming headers adjacent to my preset ones in two side by
side list boxes. Based on which Preset Header the incoming ones are next to,
I want to apply specific formatting to these incoming data ranges.

I was thiking the best way to do this would be to create an object for each
Column that would have the three following properties:

Header Text
ColumnLetter (Which physical column it is on the sheet)
FormatType (Which of my predefined formats it should use after I have sorted
the incoming spreadsheet headers)

Questions:

How do I create the objects if this is the easiest way to solve my problem?
How do I create these properties inside of the objects?
How do I create my own custom formatting?

PLEASE HELP!!!! Thank you!!!

Nate

Object Creation
 

Nate,
You could format a cell in a hidden column/worksheet for each format style
you have. Then you just apply (copy/pastespecial format) .

As for creating objects in Excel, you can use a Class module, then create an
array of your objects.(or possibly with a Collection).

<cHeaderFormat
Option Explicit

Dim mHeaderText As String
Dim ColumnDestination As String
Dim FormatSource As Range

Public Property Let HeaderText(argIn As String)
mHeaderText = argIn
End Property

Public Property Get HeaderText() As String
HeaderText = mHeaderText
End Property

Public Property Let ColumnDestination(argIn As String)
mColumnDestination = argIn
End Property

Public Property Get ColumnDestination() As String
ColumnDestination = mColumnDestination
End Property

Public Property Let FormatSource(argIn As Range)
If argIn.Cells.Count 1 Then
MsgBox "Only single cell allowed"
Exit Property
End If

Set mFormatSource = argIn
End Property

Public Property Get FormatSource() As Range
Set FormatSource = mFormatSource
End Property
</cHeaderFormat

And then using this class, create an array at the suitable time, maybe when
the userform loads, with 1 object for each item in you listbox

<UserForm
Dim objHeaders() As cHeaderFormat

Private Sub UserForm_Initialize()
Dim i As Long

With lstHeaders
ReDim objHeaders(.ListCount)
For i = 0 To .ListCount - 1
Set objHeaders(i) = New cHeaderFormat
objHeaders(i).HeaderText = .List(i)
Next
End With
End Sub
</UserForm

Adjust for your actual situation

NickHK

"Nate Lasko" wrote in message
...
I am working on a VB macro that takes spreadsheet from a third party
vendor
and grabs the headers for each column. It then allows to the user of the
form
to line up these incoming headers adjacent to my preset ones in two side
by
side list boxes. Based on which Preset Header the incoming ones are next t
o,
I want to apply specific formatting to these incoming data ranges.

I was thiking the best way to do this would be to create an object for
each
Column that would have the three following properties:

Header Text
ColumnLetter (Which physical column it is on the sheet)
FormatType (Which of my predefined formats it should use after I have
sorted
the incoming spreadsheet headers)

Questions:

How do I create the objects if this is the easiest way to solve my
problem?
How do I create these properties inside of the objects?
How do I create my own custom formatting?

PLEASE HELP!!!! Thank you!!!

Nate