Referencing renamed textboxes

Question

I have a userform with labels and textboxes that is used to update information on a worksheet. When viewing the userform in VB, I cannot distinguish between the textboxes without flipping back and forth to the worksheet. So, I renamed each textbox.

Obviously the NAME for each textbox is different now. Wondering how to reference each textbox when using a FOR/NEXT loop. Below is the code before changing the names of each textbox.

Private Sub CommandButton1_Click() 'Update Demographics
Dim k As Integer
Dim rng As Range
Dim ans As String
Dim cktxtbx7 As Integer

If TextBox7.Value = "" Then
TextBox7.Value = Label7.Caption
cktxtbx7 = 1
End If
MsgBox TextBox8.Value & " " & TextBox7.Value
If TextBox8.Value <= TextBox7.Value Then
MsgBox "check"
ans = MsgBox("End Date occurs on/before Start Date." & vbNewLine & vbNewLine & "Please enter an End Date that occurs after the Start Date", vbOK + vbExclamation, "WARNING - DATE ERROR")
If cktxtbx7 = 1 Then
TextBox7.Value = ""
End If
Exit Sub
End If

'Loop to update cells if there is a value to be updated
Set rng = Worksheets("Setup").Range("B1")
For k = 1 To 11
If Controls("Textbox" & k).Value <> "" Then
rng.Offset(k, 0) = Controls("Textbox" & k).Value
End If
Next k

Unload Me

End Sub

Answer 1

With my lack of knowledge using the commands/functions listed below, I am guessing that each "Case" has the name of each textbox. If that is true, is there a different way to write the code that can loop through each renamed textbox, checking to see if there is data in each and updating the worksheet if there is data.

I don't understand.

Ctl.Name returns the name of the control. If you know the name is "txtMyDate" you know that is the textbox that should contain your date.

In this case you can check if the content of the textbox (which is a string) can be converted into a real date (using CDate) and write the real date into the sheet. In this case "see if there is data" makes sense.

If the name of the textbox is "txtMyDescription" you know that is the description. In this case "see if there is data" makes no sense. If I want to remove the description from the sheet I would leave the textbox empty...

Using a loop is the right way, because it reduces the code a lot.

Take a look into this file:

https://www.dropbox.com/s/x0o1wazwtfo4fcm/Simple_Userform.xlsm?dl=1

As you can see, there are many ways to solve the problem, it's just up to your imagination.

Andreas.

Answer 2

With my lack of knowledge using the commands/functions listed below, I am guessing that each "Case" has the name of each textbox. If that is true, is there a different way to write the code that can loop through each renamed textbox, checking to see if there is data in each and updating the worksheet if there is data. If not, then I may just go back and rename the textboxes to their default.

For Each Ctl In Me.Controls
If TypeOf Ctl Is MSForms.TextBox Then

Select Case Ctl.Name
Case "txtMyDate"

Answer 3

Option Explicit
Option Compare Text

Private Sub CommandButton1_Click()
Dim Ctl As MSForms.Control

For Each Ctl In Me.Controls
If TypeOf Ctl Is MSForms.TextBox Then
Select Case Ctl.Name
Case "txtMyDate"
'Do something here
Case Else
'Unknown box, show the name
Debug.Print Ctl.Name
End Select
End If
Next
End Sub