Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Faceid's (XL2000)

    Hi all,

    When code listed below is run the result consists of only 5 faceid's.
    The listing is obtained from John Green's book Excel 2000 VBA Programmer's Reference (page 206) and should show over 5500 faces.

    Sub ListAllFaces()
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim cbCtl As CommandBarControl
    Dim cbBar As CommandBar

    If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub
    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


    Function IsEmptyWorksheet(Sht As Object) As Boolean
    If TypeName(Sht) = "Worksheet" Then
    If WorksheetFunction.CountA(Sht.UsedRange) = 0 Then
    IsEmptyWorksheet = True
    Exit Function
    End If
    End If
    MsgBox "Please make sure that an empty worksheet is active"
    End Function


    All suggestions are appreciated.
    TIA
    Joop

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Faceid's (XL2000)

    Hi,
    I just copied your code into a module (in Excel 2002), ran it and it worked as expected - i.e. thousands of icons. I also can't see anything wrong with it as it stands. Have you tried stepping through it to see why it doesn't work for you?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Faceid's (XL2000)

    I did the same thing in XL97 and it works as expected. I can't imagine a difference in XL2000 from both XL97 and 2002, but anyone with XL2000 try to give it a try?

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Faceid's (XL2000)

    I just dropped it into XL2000 and it runs from a Module, but doesn't run from the ThisWorkbook Object. (Guess how I found out?) Could that be the problem? I have the Excel and Office Object Libraries checked under VBE Tools, References; see if that helps.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Faceid's (XL2000)

    Thanks for the replies,

    Still not sure why/who/what....
    Code runs normally without applying any changes.

    Thanks
    Joop

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •