Search code examples
excelvba

Modify userform's caption by code without trust center permission

I need to change the captions of buttons (or other controls) in an Excel UserForm by code. However, I want to avoid enabling the "Trust access to the VBA project object model" option due to security concerns. Is there an alternative approach or workaround that doesn't require enabling this option?

UserForm1.CommandButton1.Caption = "New Caption"

Thank you for your suggestions.

Solution

  • Here is the principle how you can do it. It is only the idea!

    If you have your languages like:

    enter image description here

    (First row == language number, First Col language string number)

    First in all your userforms give the controls you want to change a tag with the corresponding number. In every userform have something like that:

    Option Explicit
Dim Activated As Boolean

Private Sub UserForm_Activate()
Dim g As Control
If Activated Then Exit Sub
Activated = True
For Each g In Me.Controls
    If g.Tag <> "" Then
        g.Caption = GiveMeCaption(Val(g.tag))
    End If
Next g
End Sub
Private Sub UserForm_Initialize()
Activated = False
End Sub

    And in a common module something like

    Option Explicit
Public LanguageNumber As Long ' you can set this at program start to the number you need
Public Function GiveMeCaption(CaptionNumber)
' Here a code to get the string like
' Row = WorksheetFunction.Match(TheRange, CaptionNumber, True)
' GiveMeCaption = TheRange.cells(row,LanguageNumber+2)
End Function

    I leave it to you to write this function to your situation. It would be better to load the language into a multidimensional array on start and get the language from there, but again, this is only the idea how to handle multiple languages. You may use this also for messages etc... And a bit of error handling would also be recommended :-)