Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > function question??
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
IF function question scubadiver Excel Worksheet Functions 2 September 8th 08 11:37 AM
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? SteveM Excel Programming 1 January 3rd 08 03:45 PM
Function Question Rubix Excel Worksheet Functions 5 February 7th 06 01:55 AM
Question Regarding Name Function Tom Ogilvy Excel Programming 0 December 4th 03 02:54 PM
Help: Question Regarding Name Function Dustin Schofield Excel Programming 0 December 4th 03 02:16 PM


All times are GMT +1. The time now is 04:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement