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
mike637Asked:
Who is Participating?
 
patrickabConnect With a Mentor Commented:
mike637,

Try the attached file - code below

Patrick
Private Sub CommandButton1_Click()
UserForm1.Hide
Unload UserForm1
End Sub

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 = "Data!" & "B" & i + 17
            i = i + 1
        End If
   Next
   
End Sub

Open in new window

mike637-01.xls
0
 
Rory ArchibaldCommented:
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?
0
 
mike637Author Commented:
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rory ArchibaldCommented:
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.
0
 
mike637Author Commented:
Thank you for being the experts you are!!
0
 
patrickabCommented:
mike637 - Thanks for the grade - Patrick
0
All Courses

From novice to tech pro — start learning today.