A B C = Jan Feb March

[cmpgeek]

hey yall,
This is a really dumb question, but i always feel better if i at least ask if something is possible instead of assuming that it is not...

With that in mind, is there coding that makes it possible to change the names of the columns in Excel - at least sa far as the end user is concerned? Also, is it possible to change the width of the gray bar where the names are located?

i am working on a calendar of sorts, i have a different worksheet for each month, and thought it would cause a lot less confusion if the row number actually coincided with the date... however, i also have to breakdown the different medical services within our hospital that the call covers... each column would indicate a different service (ex: general surgery / urology / OBGYN / Orthopedics / etc...) i was hoping there was a way to "name" the column headings for whatever service will be listed there.

I want Column A to read NEURO when the end-user looks at it - even if when Excel's brain sees the same column, it still sees it as Column A . Is this even possible - or should i say reasonably possible? lol

as with most of my questions, this is paramount to the application doing what i want it to; it is geared toward making things as easy as possible for the end user. Not only do i want the program to be easy for them to use, but considering they will spend a good bit of their day looking at it, i want it to look as simple and uncluttered as possible...

thanks for taking the time to read this...

[Zack Barresse]

Hey there cmp,

You can't change the row/column headings. Only in one case and that is switching to R1C1 reference style, which would make the columns into numbers starting with 1 as A and counting upwards. A workaround I'd suggest would be to hide the column/row headers (Tools -> Options -> View (tab) -> uncheck Row and Column headers) and create labels/headers yourself. This would probably be done (switching off the headers) easiest in a workbook_open event, then turn back upon close. I do this with someworksheets with other options as I only want them for that individual workbook.

And what do you mean by this...

Also, is it possible to change the width of the gray bar where the names are located?

Do you mean where you see the cell reference/named ranges? If so, yes. Let me know if that's what you mean.

[cmpgeek]

thanks for replying so fast firefytr... yes, i was talking about the width of the bar where the cells are named/referenced... it sounds like i will be hiding the headers in this workbook, but if you want to go ahead and explain how to do that i will make sure to bookmark it so i can reference it another time... <smile>

thanks again!

[cmpgeek]

by the way, do you happen to know where i can get the coding to hide the headers within the workbook? i went through the traditional manner and noticed that it only does it on the tab you are working on, so i am guessing it probably wont do it at all on someone else's PC unless i do it via the VBA coding...

(if i only had a few more hours in my day i could sit down and hammer out this "Teach Yourself Visual Basic 6" book in a week or so... then at least i would have a working knowledge of the basics... maybe once i finish my bachelors in nursing i can go back for a bachelors in programming or something LOL...)

[Zack Barresse]

Put this into your ThisWorkbook module (Alt + F11, 2x click ThisWorkbook)...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWindow.DisplayHeadings = True
End Sub

Private Sub Workbook_Open()
    ActiveWindow.DisplayHeadings = False
End Sub

As for your named range box width, this is from Chip Pearson's site...

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hwnd As Long, ByVal wMsg As Long, _
    ByVal wParam As Long, lParam As Any) As Long
 

Sub WidenNameBoxDrop2()
    Dim Res As Long
    Const CB_SETDROPPEDWIDTH = &H160
    Const cWidth = 400 
    Res = SendMessage( _
            FindWindowEx( _
                FindWindowEx( _
                    FindWindow("XLMAIN", Application.Caption) _
                , 0, "EXCEL;", vbNullString) _
            , 0, "combobox", vbNullString), _
          CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub

This routine can be found at: http://www.cpearson.com/excel/NameBox.htm

HTH

[cmpgeek]

you are awesome!

thanks a $million!

[Zack Barresse]

Glad it helped!