AdvancedCommandBars

[Guest]

Question is why (IsEmptyWorksheet) can hang up the running of the application
does this statement need a Dim
This statement produces all the system faces and takes 3 minutes to run.

Dim iFaceId As Integer ' Tracks Current FaceID
Dim iColumn As Integer ' Tracks Current column in worksheet
Dim iRow As Integer ' Tracks current Row in worksheet
Dim ctl As CommandBarControl
Dim cbr As CommandBar
If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub
On Error GoTo Recover
Application.ScreenUpdating = False
Set cbr = CommandBars.Add(Position:=msoBarFloating, MenuBar:=False,
temporary:=True)
Set ctl = cbr.Controls.Add(Type:=msoControlButton, temporary:=True)
iRow = 1
Do
For iColumn = 1 To 10
iFaceId = iFaceId + 1
Application.StatusBar = "FaceID = " & iFaceId
ctl.FaceId = iFaceId
ctl.CopyFace
ActiveSheet.Paste Cells(iRow, iColumn + 1)
Cells(iRow, iColumn).Value = iFaceId
Next iColumn
iRow = iRow + 1
Loop
Recover:
If Err.Number = 1004 Then Resume Next
Application.StatusBar = False
cbr.Delete



End Sub

 

[Dave Peterson]

It doesn't need a dim statement, but you do need to have a function with that
name.

Do you have a function like that that the calling sub can find?

If no, maybe you can use this one:

Function IsEmptyWorksheet(wks As Worksheet) As Boolean
If wks.UsedRange.Address = "$A$1" _
And IsEmpty(wks.Range("A1").Value) Then
IsEmptyWorksheet = True
Else
IsEmptyWorksheet = False
End If
End Function

Here are some alternatives, too:

Doug Glancy's:
http://www.dicks-blog.com/archives/2004/11/23/faceid-viewer-addin/

John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech's:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech

Jim's version for the Office2007 button images can be found on Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
or directly from:
http://www.rondebruin.nl/files/BtnImages.zip