Solved

vba update labels in FormBox

Posted on 2011-02-15
6
521 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:mike637
  • 2
  • 2
  • 2
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34898114
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
 

Author Comment

by:mike637
ID: 34898392
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34898441
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 34899157
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
 

Author Closing Comment

by:mike637
ID: 34899852
Thank you for being the experts you are!!
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34900510
mike637 - Thanks for the grade - Patrick
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question