PDA

View Full Version : A better way to enter heading captions?



RonMcK
02-22-2008, 07:08 PM
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
02-22-2008, 10:31 PM
This works for me
myHeaders = Array("Name", "Address", "Phone")
Range("A1").Resize(1, UBound(myHeaders) + (1 - LBound(myHeaders))).Value = myHeaders

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
02-23-2008, 05:00 AM
As for formatting, create your own Header Style and apply it

Range("A1").Resize(1, UBound(myHeaders) + 1).Style = MyHeaderStyle

RonMcK
02-23-2008, 01:18 PM
Malcolm,

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

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
to:

... = myHeaderStyle

??

Thanks,

Ron
Orlando, FL

RonMcK3
02-23-2008, 02:32 PM
Mike and Malcolm,

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

mdmackillop
02-23-2008, 05:02 PM
Glad to help Ron. Personally, I've never used styes, but as I understand them, this is what they were designed for.

RonMcK3
02-23-2008, 05:32 PM
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
02-23-2008, 05:41 PM
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
02-23-2008, 07:21 PM
I use styles sometimes. Temporary highlights is another use for them.

Range("A1:c10").Style = "HeaderFormat"

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
02-23-2008, 07:50 PM
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.