Saturday, May 30, 2009

Crosshatching in VBA for Microsoft Office 2007


The new versions of Microsoft Office don't natively support cross hatching anymore, not in Word or Powerpoint anyway.

So if you want to get lovely crosshatched diagrams and different patterns you can use this script I wrote. Just press view, macro, create a new one, paste in the code, then run it on your selected object. A great way to make venn diagrams with overlapping crosshatched regions in Powerpoint 2007!

Code:
Sub crosshatch()

Dim Sh As Shape
Dim oldFColor As Long

If Not ActiveWindow.Selection.Type = ppSelectionShapes Then
MsgBox "Select something, then try again"
Exit Sub
End If

Set Sh = ActiveWindow.Selection.ShapeRange(1)

strMenu = "Select pattern: 1. Horizontal, 2. Vertical, 3. Upward Diagonal, 4. Downward Diagonal "
rc = InputBox(strMenu, "Menu", 1)
If IsNumeric(rc) Then
Select Case rc
Case 1
p = msoPatternHorizontal
Case 2
p = msoPatternVertical
Case 3
p = msoPatternUpwardDiagonal
Case 4
p = msoPatternDownwardDiagonal
End Select
Else
MsgBox "Invalid Selection"
Exit Sub
End If

With Sh
With .Fill
.Transparency = 0.5
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.BackColor.RGB = RGB(255, 255, 255)
.Patterned p
End With
End With

End Sub

4 comments:

mam1 said...

Hi,

I have used your script as the basis for adding a pattern fill to some shapes on an Excel 2007 worksheet.

Works beautifully except that I cannot seem to make the ***spaces***s between the pattern fill elements transparent.

Playing with the value for .Transparency seems to adjust both the pattern elements ***and*** the spaces between them.

can you possibly help

many thanks

Steven said...

Thanks a lot.

Dee W said...

Thank you! This works beautifully. Powerpoint should natively support this feature!

RBabin said...

Looking for help on the transparency. Was a solution found to keep the hatching dark and the background translucent?