Referring to textbox in userform (VBA)

[Tim_Excel_]

Hi Forum,

I've written a piece of code that looks in a column and places all unique values in seperate textboxes in a userform.
The next step would be referring to these textboxes in the userform code. Whatever I try however (Userform4.Textbox("box1").Value, or Userform.Textbox1.Value), nothing actually returns the values of the textboxes in the userform. How do I refer to the textboxes?

Public Sub ytj()

Dim NewBox As MSForms.TextBox


With Workbooks("0700.0006.000.21_01_01").Worksheets(3)

k = 0
l = 0

For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If Left(Cells(i, 1).Value, 4) <> "" And Left(Cells(i, 1).Value, 4) <> Left(Cells(i - 1, 1).Value, 4) Then
Set NewBox = UserForm4.Controls.Add("Forms.textbox.1")
With NewBox
If i > 2 Then 'the if statement makes sure the first box is placed 30 from the top, and the next boxes 30 + 25 * k
    .Name = "box" & k & ""
    .Top = 30 + 25 * k
    .Left = 10
    .Locked = True
    k = k + 1
Else
    .Name = "box" & k & ""
    .Top = 30
    .Left = 10
    .Locked = True
End If
NewBox.Value = Left(Cells(i, 1).Value, 4)
End With
Set NewBox = UserForm4.Controls.Add("Forms.textbox.1") 'another set of boxes on the right of the other boxes
With NewBox
If i > 2 Then
    .Name = "boxinvul" & k & ""
    .Top = 30 + 25 * l
    .Left = 160
    l = l + 1
Else
    .Name = "boxinvul" & k & ""
    .Top = 30
    .Left = 160
End If
End With
End If
Next i
 
UserForm4.Show vbModeless
 
 
 
End With
End Sub

 

[gallen]

You tried Textbox1.Text?

 

[Tim_Excel_]

MsgBox UserForm4.TextBox1.Text or similar produces a "Method or data member not found" error, indicating the textbox doesn't exist.

I renamed the textboxes in the code with the .Name property, maybe that helps?

 

[gallen]

That would do it! The name of the textbox should appear in intellisense when typing your code... If it doesn't then there's your problem. Unless you are creating textboxes dynamically I can't think of a reason to change a textbox's name in code. To be honest I didn't think you could.

 

[Tim_Excel_]

I have disabled the textbox renaming pieces of code without effect.
Intellisense doesn't even give TextBox as an option when typing 'Userform4.'

 

[gallen]

OK I just read your code a little more thoroughly. You are dynamically creating textboxes and renaming them. Have you stepped through line by line ensuring the names are generated as you expect? I will try and recreate your scenario myself too

 

[Tim_Excel_]

I tried doing that, but I can't refer to the textboxes, making it virtually impossible to check. The boxes are created however, which I can see when the userform opens. However, the textboxes are temporary (only last when the code still runs) so upon closing the code I can't check the properties from the userform either to check the boxes' names. It's a difficult one really

 

[Tim_Excel_]

MsgBox Me.Controls("box1").Value

That's how easy it was.

Why didn't I find this earlier?



eitherway, thanks for the help.

 

[gallen]

Having read through and done some testing you've really got yourself in a tizz

As you name the Textboxes dynamically, their names can be different depending on the values in the variables:

[COLOR=#333333] .Name = "box" & k & ""[/COLOR]

(btw I can't see a valid reason for the & "" part)

I'm guessing this is so you can loop through them later?

Also k is set to 0 so the first box's name would be 'box' as k is empty

[COLOR=#574123].Name = "box" & k & ""[/COLOR]

so you'd need :

[COLOR=#574123].Name = "box" & Cstr(k)[/COLOR]

This would give the box the name 'box0'

I got confused with what you were trying to achieve with 'k' as it only gets increased in certain situations. Make sure you understand

So finally I'm guessing you have more code that references these textboxes? Can you show that too?


Below is some rewritten code with comments/questions see if it makes sense or if I've got confused

Public Sub ytj()

Dim NewBox As MSForms.TextBox


    With Workbooks("0700.0006.000.21_01_01").Worksheets(3)
    
        k = 0
        l = 0
        
        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Left(Cells(i, 1).Value, 4) <> "" And Left(Cells(i, 1).Value, 4) <> Left(Cells(i - 1, 1).Value, 4) Then
                Set NewBox = UserForm4.Controls.Add("Forms.textbox.1")
                With NewBox
                    'name is set whichever outcome so just one line needed:
                    .Name = "box" & CStr(k)
                    If i > 2 Then 'the if statement makes sure the first box is placed 30 from the top, and the next boxes 30 + 25 * k
                        .Top = 30 + 25 * k
                        .Left = 10
                        .Locked = True
                        k = k + 1
                    Else
                        .Top = 30
                        .Left = 10
                        .Locked = True
                        'why is k not increased here?
                    End If
                    .Value = Left(Cells(i, 1).Value, 4)
                End With
                Set NewBox2 = UserForm4.Controls.Add("Forms.textbox.1") 'another set of boxes on the right of the other boxes
                With NewBox2
                    '???Shouldn't this be cstr(l) not k???
                    .Name = "boxinvul" & CStr(k)
                    If i > 2 Then
                        .Top = 30 + 25 * l
                        .Left = 160
                        l = l + 1
                    Else
                        .Top = 30
                        .Left = 160
                        'why is l not increased here?
                    End If
                End With
            End If
        Next i
        
        UserForm4.Show vbModeless
         
    End With
End Sub

 

[gallen]

ahh you've sussed it. Good to hear