| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
function question??
Can a function call another function? Here is the function I have. I am tring
to apply a page break (which works fine) then tring to call my format page funtion after it applies the page break?? Sub setPage() Dim iRow As Long Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim searchFor As String searchFor = FrmCreate.CbxDept.Text Set wks = Worksheets(searchFor) With wks .ResetAllPageBreaks Set myRng = Nothing On Error Resume Next Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No visible cells!" Exit Sub End If iRow = 0 For Each myCell In myRng.Cells iRow = iRow + 1 If iRow 1 Then If iRow Mod 19 = 1 Then .HPageBreaks.Add befo=myCell Call FormatHeaders End If End If Next myCell End With End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
hi
yes. use the call command. but you already have a call command in your code. call FormatHeaders ??? regards FSt1 "Mekinnik" wrote: Can a function call another function? Here is the function I have. I am tring to apply a page break (which works fine) then tring to call my format page funtion after it applies the page break?? Sub setPage() Dim iRow As Long Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim searchFor As String searchFor = FrmCreate.CbxDept.Text Set wks = Worksheets(searchFor) With wks .ResetAllPageBreaks Set myRng = Nothing On Error Resume Next Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No visible cells!" Exit Sub End If iRow = 0 For Each myCell In myRng.Cells iRow = iRow + 1 If iRow 1 Then If iRow Mod 19 = 1 Then .HPageBreaks.Add befo=myCell Call FormatHeaders End If End If Next myCell End With End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
Yes I know, but it does not seem to work the way I want it to. maybe that the
problem? I guess another post for that issue, unless you want to try to help within this post? "FSt1" wrote: hi yes. use the call command. but you already have a call command in your code. call FormatHeaders ??? regards FSt1 "Mekinnik" wrote: Can a function call another function? Here is the function I have. I am tring to apply a page break (which works fine) then tring to call my format page funtion after it applies the page break?? Sub setPage() Dim iRow As Long Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim searchFor As String searchFor = FrmCreate.CbxDept.Text Set wks = Worksheets(searchFor) With wks .ResetAllPageBreaks Set myRng = Nothing On Error Resume Next Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No visible cells!" Exit Sub End If iRow = 0 For Each myCell In myRng.Cells iRow = iRow + 1 If iRow 1 Then If iRow Mod 19 = 1 Then .HPageBreaks.Add befo=myCell Call FormatHeaders End If End If Next myCell End With End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
hi
what is it not doing or how do you want it to work? FSt1 "Mekinnik" wrote: Yes I know, but it does not seem to work the way I want it to. maybe that the problem? I guess another post for that issue, unless you want to try to help within this post? "FSt1" wrote: hi yes. use the call command. but you already have a call command in your code. call FormatHeaders ??? regards FSt1 "Mekinnik" wrote: Can a function call another function? Here is the function I have. I am tring to apply a page break (which works fine) then tring to call my format page funtion after it applies the page break?? Sub setPage() Dim iRow As Long Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim searchFor As String searchFor = FrmCreate.CbxDept.Text Set wks = Worksheets(searchFor) With wks .ResetAllPageBreaks Set myRng = Nothing On Error Resume Next Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No visible cells!" Exit Sub End If iRow = 0 For Each myCell In myRng.Cells iRow = iRow + 1 If iRow 1 Then If iRow Mod 19 = 1 Then .HPageBreaks.Add befo=myCell Call FormatHeaders End If End If Next myCell End With End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
What I have is a sheet that is created a populated with data and then the
created sheet is formated through code, and I want to insert a page break at the end of the data, then apply the format page function again to create a new page and apply the rest of the data so on and so forth. Here is the code that starts it. Private Sub BtnGo_Click() Dim rgMatch As Range '''' range of matches Dim searchFor As String ''' string to search for Dim wsh As Worksheet ''' where to search Dim rgToSearch As Range ''' where to search Dim RgFrom As Range Dim n As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'copies all data that matches 'searchFor' to new sheet searchFor = Me.CbxDept.Text Set wsh = Sheets("Procode") Set rgToSearch = wsh.Range("M:M") Set RgFrom = wsh.Range("A1:M1").EntireColumn n = Int(56 * Rnd + 1) ''' Search all matches Set rgMatch = FindAll(rgToSearch, searchFor & "*", xlValues, xlWhole) ''' Process matches If Not rgMatch Is Nothing Then ''' copy specific columns to new sheet On Error Resume Next Application.DisplayAlerts = False wsh.Parent.Worksheets(searchFor).Delete Application.DisplayAlerts = True On Error GoTo 0 With wsh.Parent.Worksheets.Add ''' copy second column: B-B Application.Intersect(rgMatch.EntireRow, wsh.Range("B:B")).Copy ..Range("B5") ''' copy third column : C-H Application.Intersect(rgMatch.EntireRow, wsh.Range("C:C")).Copy ..Range("H5") ''' copy forth column : D-I Application.Intersect(rgMatch.EntireRow, wsh.Range("D:D")).Copy ..Range("I5") ''' copy fifth column: E-J Application.Intersect(rgMatch.EntireRow, wsh.Range("E:E")).Copy ..Range("J5") ''' copy sixth column: F-K Application.Intersect(rgMatch.EntireRow, wsh.Range("F:F")).Copy ..Range("K5") ''' copy seventh column : G-L Application.Intersect(rgMatch.EntireRow, wsh.Range("G:G")).Copy ..Range("L5") ''' copy eighth column: H-M Application.Intersect(rgMatch.EntireRow, wsh.Range("H:H")).Copy ..Range("M5") ''' copy ninth column: I-N Application.Intersect(rgMatch.EntireRow, wsh.Range("I:I")).Copy ..Range("N5") ''' copy tenth column : J-O Application.Intersect(rgMatch.EntireRow, wsh.Range("J:J")).Copy ..Range("O5") ''' copy eleventh column: K-P Application.Intersect(rgMatch.EntireRow, wsh.Range("K:K")).Copy ..Range("P5") ''' copy twelveth column: L-Q Application.Intersect(rgMatch.EntireRow, wsh.Range("L:L")).Copy ..Range("Q5") ''' copy last column: M-A Application.Intersect(rgMatch.EntireRow, wsh.Range("M:M")).Copy ..Range("A5") Call FormatHeaders '''change the tab color randomly and rename sheet .Tab.ColorIndex = n .Name = searchFor Call setPage 'Call InsertBreak End With End If With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Public Function FindAll(where As Range, what As Variant, lookIn As XlFindLookIn, lookAt As XlLookAt) As Range Dim rgResult As Range Dim cell As Range Dim firstAddr As String With where Set cell = .Find(what, lookIn:=lookIn, lookAt:=lookAt) If Not cell Is Nothing Then firstAddr = cell.Address Do ''' add cell to result range If rgResult Is Nothing Then Set rgResult = cell Else Set rgResult = Application.Union(rgResult, cell) End If ''' find next match Set cell = .FindNext(cell) Loop While Not cell Is Nothing And cell.Address < firstAddr End If End With Set FindAll = rgResult End Function "FSt1" wrote: hi what is it not doing or how do you want it to work? FSt1 "Mekinnik" wrote: Yes I know, but it does not seem to work the way I want it to. maybe that the problem? I guess another post for that issue, unless you want to try to help within this post? "FSt1" wrote: hi yes. use the call command. but you already have a call command in your code. call FormatHeaders ??? regards FSt1 "Mekinnik" wrote: Can a function call another function? Here is the function I have. I am tring to apply a page break (which works fine) then tring to call my format page funtion after it applies the page break?? Sub setPage() Dim iRow As Long Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim searchFor As String searchFor = FrmCreate.CbxDept.Text Set wks = Worksheets(searchFor) With wks .ResetAllPageBreaks Set myRng = Nothing On Error Resume Next Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No visible cells!" Exit Sub End If iRow = 0 For Each myCell In myRng.Cells iRow = iRow + 1 If iRow 1 Then If iRow Mod 19 = 1 Then .HPageBreaks.Add befo=myCell Call FormatHeaders End If End If Next myCell End With End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
Here are the function:
Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("M4").FormulaR1C1 = "React" End With With Range("N4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("N4").FormulaR1C1 = "Specific" End With With Range("O3:O4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("O3").FormulaR1C1 = "Disposal Code R/Y/G" End With With Range("P3:P4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("P3").FormulaR1C1 = "Quantity on Hand" End With With Range("Q3:Q4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("Q3").FormulaR1C1 = "Date of Inventory" End With With Range("A5:A21") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous End With With Range("B5:E5,B6:E6,B7:E7,B8:E8,B9:E9,B10:E10,B11:E 11,B12:E12,B13:E13,B14:E14,B15:E15,B16:E16,B17:E17 ,B18:E18,B19:E19,B20:E20,B21:E21") .Font.Name = "Arial" .Font.Size = 9 .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("F5:G5,F6:G6,F7:G7,F8:G8,F9:G9,F10:G10,F11:G 11,F12:G12,F13:G13,F14:G14,F15:G15,F16:G16,F17:G17 ,F18:G18,F19:G19,F20:G20,F21:G21") .Font.Name = "Arial" .Font.Size = 8 .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("H5:H21") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous End With With Range("I5:J21") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous .Borders(xlInsideVertical).LineStyle = xlContinuous .NumberFormat = """Yes"";""Yes"";""No""" End With With Range("K5:M21") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous .Borders(xlInsideVertical).LineStyle = xlContinuous .NumberFormat = "0" End With With Range("N5:O21") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous .Borders(xlInsideVertical).LineStyle = xlContinuous .NumberFormat = "0" End With With Range("P5:P21") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous .NumberFormat = "0" End With With Range("Q5:Q21") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous .NumberFormat = "[$-409]d-mmm-yy;@" End With End Function Sub setPage() Dim iRow As Long Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim searchFor As String searchFor = FrmCreate.CbxDept.Text Set wks = Worksheets(searchFor) With wks .ResetAllPageBreaks Set myRng = Nothing On Error Resume Next Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No visible cells!" Exit Sub End If iRow = 0 For Each myCell In myRng.Cells iRow = iRow + 1 If iRow 1 Then If iRow Mod 19 = 1 Then .HPageBreaks.Add befo=myCell Call FormatHeaders End If End If Next myCell End With End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
hi
change... public function formatheaders() to.. sub formatheaders() and change end function at the bottom to end sub. it should work now. subs are code that do things. functions create custom formulas and such and are not ment to perform direct actions like subs. Regards FSt1 "Mekinnik" wrote: Here are the function: Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("M4").FormulaR1C1 = "React" End With With Range("N4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("N4").FormulaR1C1 = "Specific" End With With Range("O3:O4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("O3").FormulaR1C1 = "Disposal Code R/Y/G" End With With Range("P3:P4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("P3").FormulaR1C1 = "Quantity on Hand" End With With Range("Q3:Q4") .HorizontalAlignment = xlCenter |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
Thank you for pointing that out. The problem I am having is with the
formatHeaders sub, and it works for the first time, however it will not format after the page break because of the fact that I have it formatting particular cells and rows and I need the sub to determine how many rows of data and then format however many pages it needs. I could if you would like send you what I have and maybe it would help to understand what it that I am trying to accomplish. "FSt1" wrote: hi change... public function formatheaders() to.. sub formatheaders() and change end function at the bottom to end sub. it should work now. subs are code that do things. functions create custom formulas and such and are not ment to perform direct actions like subs. Regards FSt1 "Mekinnik" wrote: Here are the function: Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("M4").FormulaR1C1 = "React" End With With Range("N4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("N4").FormulaR1C1 = "Specific" End With With Range("O3:O4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("O3").FormulaR1C1 = "Disposal Code R/Y/G" End With With Range("P3:P4") .HorizontalAlignment = xlCenter |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
Maybe you should pass the worksheet to be worked upon as parameters to the
called functions, and qualify the ranges with the worksheet object. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Thank you for pointing that out. The problem I am having is with the formatHeaders sub, and it works for the first time, however it will not format after the page break because of the fact that I have it formatting particular cells and rows and I need the sub to determine how many rows of data and then format however many pages it needs. I could if you would like send you what I have and maybe it would help to understand what it that I am trying to accomplish. "FSt1" wrote: hi change... public function formatheaders() to.. sub formatheaders() and change end function at the bottom to end sub. it should work now. subs are code that do things. functions create custom formulas and such and are not ment to perform direct actions like subs. Regards FSt1 "Mekinnik" wrote: Here are the function: Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("M4").FormulaR1C1 = "React" End With With Range("N4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("N4").FormulaR1C1 = "Specific" End With With Range("O3:O4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("O3").FormulaR1C1 = "Disposal Code R/Y/G" End With With Range("P3:P4") .HorizontalAlignment = xlCenter |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
hi
i just ran your code. slick. chemicals. i use to work with that stuff. anyway your formating only goes down to row 21 which is your problem. so to find the last row of your data add this to your code somewhere near the top. Dim lr As Long lr = Cells(Rows.Count, "A").End(xlUp).Row then go through your code and replace lines line this..... With Range("Q5:Q21") with this.. With Range("Q5:Q" & lr) that should format to the bottom of the data. good luck FSt1 "Mekinnik" wrote: Thank you for pointing that out. The problem I am having is with the formatHeaders sub, and it works for the first time, however it will not format after the page break because of the fact that I have it formatting particular cells and rows and I need the sub to determine how many rows of data and then format however many pages it needs. I could if you would like send you what I have and maybe it would help to understand what it that I am trying to accomplish. "FSt1" wrote: hi change... public function formatheaders() to.. sub formatheaders() and change end function at the bottom to end sub. it should work now. subs are code that do things. functions create custom formulas and such and are not ment to perform direct actions like subs. Regards FSt1 "Mekinnik" wrote: Here are the function: Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("M4").FormulaR1C1 = "React" End With With Range("N4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("N4").FormulaR1C1 = "Specific" End With With Range("O3:O4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
Sorry Bob,
That is just a tad bit beyond me, I am learning as I go and I do not quite understand how to do what you suggest and am not sure then end result would be what I have visioned. "Bob Phillips" wrote: Maybe you should pass the worksheet to be worked upon as parameters to the called functions, and qualify the ranges with the worksheet object. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Thank you for pointing that out. The problem I am having is with the formatHeaders sub, and it works for the first time, however it will not format after the page break because of the fact that I have it formatting particular cells and rows and I need the sub to determine how many rows of data and then format however many pages it needs. I could if you would like send you what I have and maybe it would help to understand what it that I am trying to accomplish. "FSt1" wrote: hi change... public function formatheaders() to.. sub formatheaders() and change end function at the bottom to end sub. it should work now. subs are code that do things. functions create custom formulas and such and are not ment to perform direct actions like subs. Regards FSt1 "Mekinnik" wrote: Here are the function: Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("M4").FormulaR1C1 = "React" End With With Range("N4") .HorizontalAlignment = xlCenter |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
Tried what you suggested, however that is not what I am trying to do. I need
to make the formatHeaders sub fire starting in the row after so many rows, in this case due to page constraints it has to fire after every page break which is set to the top of every 19th row. I think it does or hope it is set right. Any suggestions? I believe I posted my setupPage sub in a previous reply "FSt1" wrote: hi i just ran your code. slick. chemicals. i use to work with that stuff. anyway your formating only goes down to row 21 which is your problem. so to find the last row of your data add this to your code somewhere near the top. Dim lr As Long lr = Cells(Rows.Count, "A").End(xlUp).Row then go through your code and replace lines line this..... With Range("Q5:Q21") with this.. With Range("Q5:Q" & lr) that should format to the bottom of the data. good luck FSt1 "Mekinnik" wrote: Thank you for pointing that out. The problem I am having is with the formatHeaders sub, and it works for the first time, however it will not format after the page break because of the fact that I have it formatting particular cells and rows and I need the sub to determine how many rows of data and then format however many pages it needs. I could if you would like send you what I have and maybe it would help to understand what it that I am trying to accomplish. "FSt1" wrote: hi change... public function formatheaders() to.. sub formatheaders() and change end function at the bottom to end sub. it should work now. subs are code that do things. functions create custom formulas and such and are not ment to perform direct actions like subs. Regards FSt1 "Mekinnik" wrote: Here are the function: Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function question??
Well, for instance,
Public Function FormatHeaders(ByRef ws As Worksheet) 'format row and columns ws.Rows("1:1").RowHeight = 45 ws.Rows("2:2").RowHeight = 15.75 ws.Rows("3:3").RowHeight = 21.75 ws.Rows("4:4").RowHeight = 13 ws.Rows("5:21").RowHeight = 33 ws.Columns("A:A").ColumnWidth = 6.57 ws.Columns("E:F").ColumnWidth = 8.43 ws.Columns("G:G").ColumnWidth = 15 ws.Columns("H:H").ColumnWidth = 2.96 ws.Columns("L:M").ColumnWidth = 6.14 etc., an when calling it use Call FormatHeaders(ws:=Worksheets("the name of the worksheet to be formatted") and so on with the other functions. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Sorry Bob, That is just a tad bit beyond me, I am learning as I go and I do not quite understand how to do what you suggest and am not sure then end result would be what I have visioned. "Bob Phillips" wrote: Maybe you should pass the worksheet to be worked upon as parameters to the called functions, and qualify the ranges with the worksheet object. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Thank you for pointing that out. The problem I am having is with the formatHeaders sub, and it works for the first time, however it will not format after the page break because of the fact that I have it formatting particular cells and rows and I need the sub to determine how many rows of data and then format however many pages it needs. I could if you would like send you what I have and maybe it would help to understand what it that I am trying to accomplish. "FSt1" wrote: hi change... public function formatheaders() to.. sub formatheaders() and change end function at the bottom to end sub. it should work now. subs are code that do things. functions create custom formulas and such and are not ment to perform direct actions like subs. Regards FSt1 "Mekinnik" wrote: Here are the function: Public Function FormatHeaders() 'format row and columns Rows("1:1").RowHeight = 45 Rows("2:2").RowHeight = 15.75 Rows("3:3").RowHeight = 21.75 Rows("4:4").RowHeight = 13 Rows("5:21").RowHeight = 33 Columns("A:A").ColumnWidth = 6.57 Columns("E:F").ColumnWidth = 8.43 Columns("G:G").ColumnWidth = 15 Columns("H:H").ColumnWidth = 2.96 Columns("L:M").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 6.29 Range("C:C,O:O,P:P").ColumnWidth = 6.86 Columns("Q:Q").ColumnWidth = 8.71 Range("B:B,D:D,I:K").ColumnWidth = 6 'formats row 1 for heading With Range("A1:Q1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 36 .Font.Bold = True Range("A1").FormulaR1C1 = "Hazardous Material Inventory" End With 'formats row 2 With Range("A2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("A2").FormulaR1C1 = "Unit:" End With With Range("B2:E2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Font.Name = "Arial" .Font.Size = 12 End With With Range("A2:E2") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous End With With Range("F2:G2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("F2").FormulaR1C1 = "Department/Division:" End With With Range("H2:L2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With With Range("M2") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .Font.Name = "Arial" .Font.Size = 12 .Font.Bold = True Range("M2").FormulaR1C1 = "Date:" End With With Range("N2:Q2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With 'format coulmn 3 With Range("A3:A4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("A3").FormulaR1C1 = "MSDS#" End With With Range("B3:E4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("B3").FormulaR1C1 = "Product Name" End With With Range("F3:G4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number" End With With Range("H3:H4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("H3").FormulaR1C1 = "A / I" End With With Range("I3:I4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("I3").FormulaR1C1 = "EHS (302) YES/NO" End With With Range("J3:J4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .WrapText = True .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("J3").FormulaR1C1 = "Toxic (313) YES/NO" End With With Range("K3:N3") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Merge .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 9 .Font.Bold = True Range("K3").FormulaR1C1 = "NFPA/HMIS Rating" End With With Range("K4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("K4").FormulaR1C1 = "Fire" End With With Range("L4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("L4").FormulaR1C1 = "Health" End With With Range("M4") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeLeft).LineStyle = xlContinuous .Font.Name = "Arial" .Font.Size = 8 .Font.Bold = True Range("M4").FormulaR1C1 = "React" End With With Range("N4") .HorizontalAlignment = xlCenter |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF function question | Excel Worksheet Functions | |||
| User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
| Function Question | Excel Worksheet Functions | |||
| Question Regarding Name Function | Excel Programming | |||
| Help: Question Regarding Name Function | Excel Programming | |||
Linear Mode
