Face ID Code not working for me
I have downloaded the code that appears on p206 of John Green's "Excel 2000 VBA Programmer's Reference" and I can't get it to work.
Absolutely nothing happens. The Do loop is not entered because Err.Number = 91.
When I comment out the "On Error Resume Next" and Debug>Step Into>F8...
the error 91: "Object variable or With block variable not set" occurs as soon as I press F8 to go from:
Set cbBar = CommandBars.Add(Position:=msoBarFloating, MenuBar:=False, temporary:=True)
to
Set cbCtl = cbBar.Controls.Add(Type:=msoControlButton, temporary:=True)
That's as far as I can go without assistance.
Can anybody spot the problem?
See code below...
Sub ListAllFaces()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim cbCtl As CommandBarControl
Dim cbBar As CommandBar
On Error Resume Next
Application.ScreenUpdating = False
Set cbBar = CommandBars.Add(Position:=msoBarFloating, _
MenuBar:=False, temporary:=True)
Set cbCtl = cbBar.Controls.Add(Type:=msoControlButton, _
temporary:=True)
k = 1
Do While Err.Number = 0
For j = 1 To 10
i = i + 1
Application.StatusBar = "FaceID = " & i
cbCtl.FaceId = i
cbCtl.CopyFace
If Err.Number <> 0 Then Exit For
ActiveSheet.Paste Cells(k, j + 1)
Cells(k, j).Value = i
Next j
k = k + 1
Loop
Application.StatusBar = False
cbBar.Delete
End Sub
Ken Johnson
|