Update Outlook Userform Label Captions based on Data from Excel

[animartis]

Hello,

**Windows 7, Excel/Outlook 2010**

I'm using a Userform in Outlook that will create a new calendar event and code it to a pre-determined category. It works great, but the problem is that I want to roll it out to other users who would have different categories and I'm trying to figure out a way to easily update the label caption.

My idea was to read category names from an Excel sheet and update the label captions accordingly, but I'm not sure how. Do I pull data from Excel to Outlook or can I push the data and reference the Outlook userform in Excel?

Here is as far as I've gotten, but receive an error "Subscript out of range" when I run it. (highlighted in red)

Sub Example()


Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rng As Range

Set xlApp = Excel.Application
Set wb = Workbooks("Time Tracking Analyzer.xlsm")
Set ws = wb.Worksheets("Logic")
Set rng = ws.Cells(1, 1)
MsgBox rng.Value


UserForm1.Label1.Caption = "Test"

End Sub

Thanks in advance.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Are you looking for the user's category list? If so the following should help:. It is an Outlook macro:

Private Sub ListCategories()

    Dim objNameSpace As NameSpace
    Dim objCategory As Category

    Set objNameSpace = Application.GetNamespace("MAPI")

    If objNameSpace.Categories.Count > 0 Then

        For Each objCategory In objNameSpace.Categories
            Debug.Print objCategory.Name, objCategory.Color
        Next
        
    End If

End Sub

The categories are listed in the Immediate Window but you could change that to display the categories another way.

 

[animartis]

This is EXACTLY what I was looking for! Thanks Rick! Stupid question, but is there a rhyme or reason to the order of the output? I'm assuming it is the order in which the categories were entered by time/date since it is not alphabetical like the GUI.

Hi and welcome to the MrExcel Message Board.

Are you looking for the user's category list? If so the following should help:. It is an Outlook macro:

Private Sub ListCategories()

    Dim objNameSpace As NameSpace
    Dim objCategory As Category

    Set objNameSpace = Application.GetNamespace("MAPI")

    If objNameSpace.Categories.Count > 0 Then

        For Each objCategory In objNameSpace.Categories
            Debug.Print objCategory.Name, objCategory.Color
        Next
        
    End If

End Sub

The categories are listed in the Immediate Window but you could change that to display the categories another way.

 

[RickXL]

Hi,

Sorry, I don't know the order. I suspect that is is chronological but it is probably not completely reliable anyway.

If you need them sorted try this:

Private Sub ListCategories()

    Dim objNameSpace As NameSpace
    Dim objCategory As Category
    Dim sl As Object
    Dim i As Long

    Set sl = CreateObject("System.Collections.SortedList")
    Set objNameSpace = Application.GetNamespace("MAPI")

    If objNameSpace.Categories.Count > 0 Then
        For Each objCategory In objNameSpace.Categories
            sl.Add objCategory.Name, objCategory.Color
        Next
    End If
    
    For i = 0 To sl.Count - 1
        Debug.Print sl.GetKey(i), sl.GetByIndex(i)
    Next
    
End Sub

That is another Outlook macro (rather than an Excel one.)

 

[animartis]

This worked perfectly. Thank you so much!

Hi,

Sorry, I don't know the order. I suspect that is is chronological but it is probably not completely reliable anyway.

If you need them sorted try this:

Private Sub ListCategories()

    Dim objNameSpace As NameSpace
    Dim objCategory As Category
    Dim sl As Object
    Dim i As Long

    Set sl = CreateObject("System.Collections.SortedList")
    Set objNameSpace = Application.GetNamespace("MAPI")

    If objNameSpace.Categories.Count > 0 Then
        For Each objCategory In objNameSpace.Categories
            sl.Add objCategory.Name, objCategory.Color
        Next
    End If
    
    For i = 0 To sl.Count - 1
        Debug.Print sl.GetKey(i), sl.GetByIndex(i)
    Next
    
End Sub

That is another Outlook macro (rather than an Excel one.)

 

[RickXL]

No problem, I am just pleased to help

Regards,