Quote:
Originally Posted by Jennifer Murphy
Now, is there a way to pass parameters to the macro? My search suggests that there is not. I have to have the macro ask for parameters using InputBox or a Form.
A couple of parameters that would make this macro more general are:
- The number of the column where the text is
- Whether the table has a header row or not
- The highlight color(s)
|
Passing parameters is quite easy. The usual way is to use code like:
Sub Macro(Parameter1 As Long, Parameter2 As String, Optional Parameter3 As Boolean)
where:
• 'Parameter' 1-3 are the parameter names (more than3 are allowed);
• Long, String & Boolean are the parameter types; and
• Optional indicates that the parameter need not be supplied (these must only come
after all the mandatory parameters).
Thus, you might use something along the lines of:
Code:
Sub TblHiLite(ColNum As Long, Hdr As Boolean, Shading As String)
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) = False Then
MsgBox "The selection is not in a table!", vbOKOnly, "TblHiLite"
Exit Sub
End If
Application.ScreenUpdating = False
Dim h As Long, n As Long, r As Long, s As Long, StrTitle As String
Const w As Long = RGB(255, 255, 255)
'Determine the start row, according to whether there's a header
If Hdr = True Then
n = 3
Else
n = 2
End If
'Get the applicable colour constant
Select Case Trim(LCase(Shading))
Case "pale blue": s = RGB(198, 217, 241)
Case "pale green": s = RGB(153, 255, 153)
Case "pale yellow": s = RGB(255, 255, 153)
Case "pink": s = RGB(255, 153, 153)
Case Else: s = w
End Select
'process the table
With Selection.Tables(1)
StrTitle = Split(.Cell(n - 1, ColNum).Range.Text, vbCr)(0)
.Rows(2).Shading.BackgroundPatternColorIndex = 0
h = w
For r = n To .Rows.Count
If Split(.Cell(r, ColNum).Range.Text, vbCr)(0) <> StrTitle Then
If h = w Then h = s Else h = w
StrTitle = Split(.Cell(r, ColNum).Range.Text, vbCr)(0)
End If
.Rows(r).Shading.BackgroundPatternColor = h
Next
End With
Application.ScreenUpdating = True
End Sub