A better way to enter heading captions?

[RonMcK]

Hi, All!

Somewhere, here, in the past week or two, I believe I noticed someone using some code to expedite entering Column headings. As I recall, the heading line (or lines) were concatenated (comma separated?) in a string. When the occasion called for creating the heading line(s), the code then parsed the strings.

Does this feature ring any bells for you? It was in the code but was not the focus of OP's question. I noodled about with some searches but came up with zero. Can you point me at the title (or words you recall being in it)?

Of interest is seeing how formating of the heading captions was handled.

I appreciate any help you can give.

Thanks!

Ron McKenzie
Orlando, FL

[mikerickson]

This works for me
[VBA]myHeaders = Array("Name", "Address", "Phone")
Range("A1").Resize(1, UBound(myHeaders) + (1 - LBound(myHeaders))).Value = myHeaders[/VBA]

perhaps this is similar to what you are thinking of

Dim myHeaders As Variant
Dim myHeaderString As String

myHeaderString = "Name,Address,Phone"
myHeaders = Split(myHeaderString, ",")
Range("A1").Resize(1, UBound(myHeaders) + 1).Value = myHeaders

[mdmackillop]

As for formatting, create your own Header Style and apply it
[VBA]
Range("A1").Resize(1, UBound(myHeaders) + 1).Style = MyHeaderStyle
[/VBA]

[RonMcK]

Malcolm,

How do I get from something like this for a cell's formatting:

[vba]Private Sub FormatBlock(ByRef Block As Object)

With Block

.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.SchemeColor = 47
.Fill.Transparency = 0.5
.Line.Weight = 0.5
.Line.DashStyle = msoLineSquareDot
.Line.Style = msoLineSingle
.Line.Transparency = 0#
.Line.Visible = msoTrue
.Line.ForeColor.SchemeColor = 47
.Line.BackColor.RGB = RGB(255, 255, 255)
End With
End Sub[/vba]
to:

... = myHeaderStyle

??

Thanks,

Ron
Orlando, FL

[RonMcK3]

Mike and Malcolm,

Thanks for your suggestions, they line up well with what I was remembering and want to do.

[mdmackillop]

Glad to help Ron. Personally, I've never used styes, but as I understand them, this is what they were designed for.

[RonMcK3]

Malcolm,

How do I assign formatting 'data' to myHeaderStyle ?

Or, asked a different what, where do I define myHeaderStyle ?

(I'm feeling dense at the moment.)

[mdmackillop]

As I said, I never use them. I would look up the Help file, personally. Let me know what you find. I'm always willing to learn! I've seen very little use of Styles here. Maybe you could become our "style expert"?

[mikerickson]

I use styles sometimes. Temporary highlights is another use for them.

[VBA]Range("A1:c10").Style = "HeaderFormat"[/VBA]

The macro recorder is the best way to get code that defines a style.
Since styles are stored with the spreadsheet, a VB routine that defines a style sometimes only runs at design time.

On advantage of useing styles is that the user can change the headers to match their taste. The user can alter the "HeaderFormat" Style without opening the VBEditor.

[RonMcK3]

Malcolm,

This turns out to be very simple.

Let's create myHeadingStyle:

? Select a cell (I chose the first cell in the range of my new heading)

On the menu bar:
? click Format,
? click Style,
? create a Style Name by typing 'myHeadingStyle' in the TextBox
? click Add

(Note you can click the arrow to see the DropList of styles that already exist. 'Normal' means no custom style was applied.)

There is one checkbox for each tab on the Format Cells dialog.
? Click MODIFY button,

Excel Opens the Format Cells dialog:
? Set the style choices that you want to use for your style.

I selected the following:
- Numbers: general (default)
- Alignment: Center horizontally, center vertically, aligned (word-wrap)
- Font: Arial 10, Bold
- Borders - no borders (default)
- Shading - no shading (default)
- Protection - locked (default)

? click OK

Now, you have your style established.

To use my new style in my VBA code, I used the line you gave me:

Range("I1").Resize(1, UBound(myHeaders) + (1 - LBound(myHeaders))).Style = "myHeadingStyle"

Note that the style name is enclosed with double quote marks. Without the quotes I get a compile error, variable not defined.

This seems to be all that there is to the process.