Consulting

Results 1 to 7 of 7

Thread: Solved: A B C = Jan Feb March

  1. #1
    VBAX Tutor cmpgeek's Avatar
    Joined
    Jun 2004
    Location
    Athens, Ga USA
    Posts
    204
    Location

    Question Solved: A B C = Jan Feb March

    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...



    Life is like a jar of jalepenos... What you do with it today might burn your butt tomorrow....

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    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.

  3. #3
    VBAX Tutor cmpgeek's Avatar
    Joined
    Jun 2004
    Location
    Athens, Ga USA
    Posts
    204
    Location
    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!



    Life is like a jar of jalepenos... What you do with it today might burn your butt tomorrow....

  4. #4
    VBAX Tutor cmpgeek's Avatar
    Joined
    Jun 2004
    Location
    Athens, Ga USA
    Posts
    204
    Location
    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...)



    Life is like a jar of jalepenos... What you do with it today might burn your butt tomorrow....

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    Put this into your ThisWorkbook module (Alt + F11, 2x click ThisWorkbook)...

    [vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWindow.DisplayHeadings = True
    End Sub
    Private Sub Workbook_Open()
    ActiveWindow.DisplayHeadings = False
    End Sub[/vba]


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

    [vba] 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[/vba]

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

    HTH

  6. #6
    VBAX Tutor cmpgeek's Avatar
    Joined
    Jun 2004
    Location
    Athens, Ga USA
    Posts
    204
    Location
    you are awesome!

    thanks a $million!



    Life is like a jar of jalepenos... What you do with it today might burn your butt tomorrow....

  7. #7
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    Glad it helped!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •