Link to home
Create AccountLog in
Avatar of mike637
mike637Flag for United States of America

asked on

vba update labels in FormBox

Hello Experts,

I have this code to populate the labels in a form box.

Private Sub UserForm_Initialize()
   Dim i As Integer
   Dim ctrl As Control
   
   i = 2
   For Each ctrl In Me.Controls
      If ctrl.Name = "Label" & i Then
         ctrl.Caption = ThisWorkbook.Sheets("Data").Range("A" & i + 17)
         i = i + 1
      End If
   Next
     
   i = 2
   For Each ctrl In Me.Controls
        If ctrl.Name = "TextBox" & i Then
        ctrl.ControlSource = ThisWorkbook.Sheets("Data").Range("B" & i + 17)
        i = i + 1
        End If
   Next
   
End Sub

But when I import new changes to affected cell range on the Sheets("Data"), the Form Box does not reflect these changes when I initialize it.

How can I get the FormBox to populate the label names when changes have been made?

Thanks
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

That code will only run when the form is loaded. Any changes you make thereafter will not be reflected unless you unload and reload it - is that what you are doing?
Avatar of mike637

ASKER

rorya,

Yes, I have a button with an assigned macro to open it upon demand.  However, when It opens - it does not load the new changes from the "Data" sheet.

meck637
Are you sure your form is getting unloaded and reloaded, and not just hidden and reshown? If it is the latter, you need to use the Activate event instead.
ASKER CERTIFIED SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mike637

ASKER

Thank you for being the experts you are!!
mike637 - Thanks for the grade - Patrick