Assign Userform Label Caption A Value In A Cell More Than Once During Runtime

[bgthree]

I have a userform that adds multiple textboxes to a frame during runtime upon a user's selection from a listbox. The user will update the textboxes with values, and once complete, click a button on the userform that saves the textbox values to a worksheet, creates a label, and assigns the sum of the worksheet values from the textboxes to the label's caption. The first time the button is clicked everything works perfectly and the sum of textbox values is assigned to the label caption. When a new selection from the listbox is made, my code removes the label that was created from the button click, and also clears all of the textboxes. When new values are entered into the textboxes and the button is clicked a second time to create the total and labels, the labels are created but they still hold the values assigned from the first listbox selection. I don't know if this makes any sense, but ideally I need a way to completely clear out the value assigned to the label caption every time the button is clicked so that it updates the sum of the textboxes once they are updated after a second, third, etc... listbox selection. I have also tried adding the label to the form in the design mode and just updating the caption during runtime but have the same issue. My code so far looks like this and works only on the first listbox selection only...

Button Click Code To create label and assign sum of values on worksheet to label's caption:

Dim CstTotal1 As Object
Set CstTotal1 = UserForm2.ProjectInfo.Add("Forms.Label.1", "CstTotal1", True)


With CstTotal1
.Caption = Format(Application.WorksheetFunction.Sum(Sheet5.Range("D2:D1000")), "$#,##0")
.Font.Size = 9
.Font.Bold = True
.Left = 368
.Width = 50
.Height = 15
.Top = 342

End With


Code in the listbox double-click event that clears the worksheet values and removes the label. lbExists() is a function that checks to see whether or not the labels have been created yet.

Sheet5.Range("A2:I10000").Clear

Dim i3 As Integer
For i3 = 1 To 6
If lbExists(i3) Then Me.Controls.Remove ("CstTotal" & i3) Else
Next i3

Please let me know of any suggestions or if more information is needed. Thank you very much.

 

[brucef2112]

If you don't have a really good reason for creating these object (labels) on the fly, it would be MUCH better to create the form with the labels on the form.
size it, font it, format it. etc.

You then use the .visible property of the label to make it visible when you want to present data. and then not visible when your done.
Much easier to deal with a single property that a rat's nest of them to build an object in code.

This is all Pseudo code.

Fill label and make visible.

sub FillLabels
   MyLabel_01.Caption = MySheet.Cells(i + 1, i).Value
   MyLabel_01.visible = true

end sub

Clear(or just hide) label

sub clearLabels
   MyLabel_01.visible = false
  ' If you want to clear the caption you can.
  ' Maybe you just want to make it not visible 
  ' You may need to show the same data again so you can then use the next sub below to toggle the label visible property and it will already have the last caption in place,
end sub

Toggle a control visibility

sub ToggleLabelsVisible
   MyLabel_01.visible = not MyLabel_01.visible
   MyLabel_02.visible = not MyLabel_02.visible

end sub

If you have a series that is always refreshed together you can use a name scheme to work through a loop.
Preplaning control names make it easy at runtime to work with.

Dim i As Integer 
For i = 1 To 30 
    Controls("LblInfo_" & i).visible = True
Next i

OR maybe

Need to do something to all Textboxes.

Dim cCont As Control
    For Each cCont In Me.Controls
      If TypeName(cCont) = "TextBox" Then
         'DO STUFF HERE
     End If
     Next cCont

 

[bgthree]

You are the man brucef2112! Your last bit about needing to do something to the textboxes did the trick. I used Me.Controls.Clear on all of the textboxes and now everything runs perfect! I cannot thank you enough. Also followed your link in your signature about how to post sample code correctly and I will definitely follow going forward. Thank you again good sir!