|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Apr 20th, 2002, 02:05 PM
|
#1 |
|
New Member
Join Date: Apr 2002
Posts: 1
|
Can I rename the column normal names A,B,C,D etc., into words of my choice ? - or
If I have a datasheet with tons of rows, how can I name the column headings so they always stay at the top row (visible) no matter how far down I scroll on the screen ? |
|
|
|
Apr 20th, 2002, 02:09 PM
|
#2 |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
If I have a datasheet with tons of rows, how can I name the column headings so they always stay at the top row (visible) no matter how far down I scroll on the screen ? Try: Window|Freeze Panes from the Menu bar. |
|
|
|
|
Apr 20th, 2002, 02:15 PM
|
#3 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: Austin, Texas USA
Posts: 11,654
|
Quote:
|
|
|
|
|
|
Apr 20th, 2002, 03:54 PM
|
#4 |
|
Board Regular
Join Date: Feb 2002
Posts: 3,064
|
Can I rename the column normal names A,B,C,D etc., into words of my choice ?
No Excel does not allow rename of defaut col A B C or ROWS 1 2 3 but will change in tools options to R1C1 or R1C2 etc... But use row 1 to hane headers to A1 = Jacks Data A2 = Date The follow advice posted above this is common and exceptable,.,,, freeze panes... sounds good as said. _________________ If you can help a guy in trouble - If you can sort that nagging problem - Pease try, in home, atwork or on a message board. Others help you! So PLEASE help if you can - If only the once. Thank you - Rdgs ======= [ This Message was edited by: Jack in the UK on 2002-04-20 16:01 ] |
|
|
|
|
Apr 20th, 2002, 04:31 PM
|
#5 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
MrExcel MVP
Join Date: Feb 2002
Location: Auckland, New Zealand
Posts: 4,209
|
1) Tools > Options >View
Deselect Row & column headings 2) Put your new names @ top 3) Freez pane 4) VBA Editor > select sheet > properties 5) Set scroll area to B2:IV65536 setting the scroll area so that the user cannot get to the Titles You now have your own column titles Note: The column headings have been left in in this example to show how it really looks and the positions;
_________________ Kind Regards, Ivan F Moala http://www.gwds.co.nz/excel_files.html - Under Constru [ This Message was edited by: Ivan F Moala on 2002-04-20 15:33 ] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
Jul 1st, 2005, 01:19 AM
|
#6 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Central Florida, USA
Posts: 7,541
|
Add this code to the "ThisWorkbook" module!
If you select Sheet1 the code erases Excel's Row and Column Headers and replaces them with custom Headers. If you select any other Sheet, Excel's normal Headers apear. Note: You must reference each cell in Code or Formula by Normal Cell References, even if you have the custom ones in place! When using this code, the Sheet with the custom headers must not use or reference: Row:1 or Column: A. The code is automatic. Note: The "Set" Ranges should be set to the number of Row and Columns you will be needing as to do the whole sheet as in the commented out "Set" statements in the code below will make the code take a long time to relabel all those rows! The second Sub will return "Sheet1" to normal, until another Sheet is selected and you return to Sheet1! To see how this works just copy the code below to the "ThisWorkbook" module in a default Workbook! Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'ThisWorkbook code! Dim c&, r& If Sh.Name = "Sheet1" Then ActiveWindow.DisplayHeadings = False 'Set myColRng = Sheets("Sheet1").Range("B1:IV1") Set myColRng = Sheets("Sheet1").Range("B1:AA1") For Each Cellc In myColRng c = c + 1 Cellc.Value = "Column: " & c Next Cellc 'Set myRowRng = Sheets("Sheet1").Range("A2:A65536") Set myRowRng = Sheets("Sheet1").Range("A2:A120") For Each Cellr In myRowRng r = r + 1 Cellr.Value = "Row: " & r Next Cellr Sheets("Sheet1").Cells.Select Selection.Columns.AutoFit Selection.Rows.AutoFit Columns("A:A").Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlInsideHorizontal).Weight = xlThin Selection.Interior.ColorIndex = 34 Rows("1:1").Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlInsideVertical).Weight = xlThin Selection.Interior.ColorIndex = 34 Sheets("Sheet1").Range("A1").Select Else ActiveWindow.DisplayHeadings = True End If End Sub Sub reSetHeaders() ActiveWindow.DisplayHeadings = True Sheets("Sheet1").Columns("A:A").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.ClearContents Sheets("Sheet1").Rows("1:1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.ClearContents Sheets("Sheet1").Range("A1").Select End Sub
__________________
JSW: Try and try again: "The way of the Coder!" |
|
|
|
|
Jul 1st, 2005, 11:15 AM
|
#7 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Central Florida, USA
Posts: 7,541
|
See the post above for all information!
The only change in the code below is an adjustment to the label counters, they now match the actual Row & Column headers in placement. Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'ThisWorkbook code! Dim c&, r& If Sh.Name = "Sheet1" Then ActiveWindow.DisplayHeadings = False 'Set myColRng = Sheets("Sheet1").Range("B1:IV1") Set myColRng = Sheets("Sheet1").Range("B1:AA1") c = 1 For Each Cellc In myColRng c = c + 1 Cellc.Value = "Column: " & c Next Cellc 'Set myRowRng = Sheets("Sheet1").Range("A2:A65536") Set myRowRng = Sheets("Sheet1").Range("A2:A120") r = 1 For Each Cellr In myRowRng r = r + 1 Cellr.Value = "Row: " & r Next Cellr Sheets("Sheet1").Cells.Select Selection.Columns.AutoFit Selection.Rows.AutoFit Columns("A:A").Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlInsideHorizontal).Weight = xlThin Selection.Interior.ColorIndex = 34 Rows("1:1").Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlInsideVertical).Weight = xlThin Selection.Interior.ColorIndex = 34 Sheets("Sheet1").Range("A1").Select Else ActiveWindow.DisplayHeadings = True End If End Sub Sub reSetHeaders() ActiveWindow.DisplayHeadings = True Sheets("Sheet1").Columns("A:A").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.ClearContents Sheets("Sheet1").Rows("1:1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.ClearContents Sheets("Sheet1").Range("A1").Select End Sub
__________________
JSW: Try and try again: "The way of the Coder!" |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
