Update userform controls stored in model via VBA

[peejay]

Hi
I've not been able to find anything on this via Google, so I'm posted a question on it here in search of some help.
I'm not very conversant with using VBA on userforms so please bare with me if this is something trivial.

I have set up a userform with a set of text labels, some for column names & some for row names for a matrix.
Rather than having to manually update the captions for each textbox I have written a looping macro which will update the captions, however it only updates them in memory and not in the actual stored user form.

If I run the code then display the user form, the changes display (yah!) however I would like these changes to be made 'permanent' to my actual user form (so I don't have to run the code each time I show the user form).

So, I'm hoping (a) this is possible to do and (b) someone could guide me in how to do this please.

This is to allow me to maintain the form in the Excel file independently from displaying the form to the user.

I hope this makes sense?

Here's a copy of my code, which will display the form with updates OK (but leaves the actual form unchanged in my VBA model).

Sub UsrRiskMatrixSetup()
  Dim ctl As Control
  Dim frm As UserForm
  Dim aryDimTable() As Variant
  Dim rng As Range
  Dim r, c As Integer
  Dim i, j As Integer
  
  Set rng = Dimensions.ListObjects("tblConsequence").DataBodyRange
  Set frm = usrRiskMatrix
  
  aryDimTable = rng.Value
  For i = 1 To UBound(aryDimTable, 1)
    frm.Controls("txtLabelColId" & Format(i, "00")).Caption = Format(aryDimTable(i, 1), "0")
    frm.Controls("txtLabelColDesc" & Format(i, "00")).Caption = aryDimTable(i, 2)
  Next i
  
  usrRiskMatrix.Show
End Sub

I've used debug.print to confirm the FRM control labels have been changed successfully, but after the macro completes, the VBA userform is unchanged.

Many thanks in advance.
PJ

 

[sykes]

https://www.mrexcel.com/forum/excel...ange-userform-label-captions-through-vba.html

 

[peejay]

Many thanks. Just what I needed.

 

[sykes]

Pleasure! Glad to help, and thanks for the feedback.