Face ID in column with their Names in Excel sheet using vba

[sanits591]

Hi,

I have an excel sheet with Face ID icons of the command bars.

Request to help me i arranging in the column Face ID icons in columns A, and their Names in Col B using some VBA codes.

Anticipatory thanks!

 

[sanits591]

Request to help on the above.

 

[Weaver]

try

Sub test()
    With Application.CommandBars("Standard")
        For r = 1 To .Controls.Count
            Cells(r, 1) = .Controls(r).Caption
            Cells(r, 2) = .Controls(r).ID
        Next
    End With
End Sub

Not exactly what you mentioned, but it may help.

 

[GTO]

I missed the part about two columns, but if you want the FaceID icons, maybe try CopyFace. Something similar to:

Sub exa()
Dim CB          As CommandBar
Dim ctl         As CommandBarButton
Dim strCBName   As String
Dim wbTemp      As Workbook
Dim wks         As Worksheet
Dim rngInput    As Range
Dim i           As Long
    
    '// Add a temp commandbar, make it a popup (which we won't show); add a temp control //
    Set CB = CommandBars.Add(Position:=msoBarPopup, MenuBar:=False, Temporary:=True)
    
    Set ctl = CB.Controls.Add(Type:=msoControlButton, Temporary:=True)
    strCBName = CB.Name
    
    Set wbTemp = Workbooks.Add(xlWBATWorksheet)
    Set wks = wbTemp.Worksheets(1)
    Set rngInput = wks.Range("B:B")
    
    rngInput.Offset(, -1).ColumnWidth = 3
    rngInput.ColumnWidth = 18
    rngInput.HorizontalAlignment = xlRight
    
    '// Change range of FaceID's you want to return to suit.//
    For i = 1 To 50
        ctl.FaceId = i
        ctl.CopyFace
        rngInput.Cells(i).PasteSpecial
        rngInput.Cells(i).Value = i
    Next
    
    '// just so the last image pasted doesn't stay selected//
    rngInput.Cells(1).Select
    
    '// Kill the temp cbar and ctrl //
    On Error Resume Next
    Set CB = CommandBars(strCBName)
    On Error GoTo 0
    
    If Not CB Is Nothing Then
        CB.Delete
    Else
        MsgBox "ACK!  I lost a toolbar!", 0, vbNullString
    End If
End Sub

Hope that helps,

Mark

 

[sanits591]

Thanks! A Lot, this really helps.