Solved

vba update labels in FormBox

Posted on 2011-02-15
6
520 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now