Home>

After displaying the created user form (NForm), I want to write the data in the text box that is added when the button (adbtn) is pressed, and reflect this in the cell by pressing the command button Refbtn.

I created it with reference to the following site.
https://excel-ubara.com/excelvba3/EXCELFORM007.html

Currently, Refbtn's Cells (NRow, 1) .Value = Me.KT1.Text displays "Method or data member not found".

* Display NForm

Public NRow As Long
Sub new registration ()
Dim Tit As String
Tit = "default"
NRow = 2
 Do While Tit<>""
  Tit = Cells (NRow, 3)
  NRow = NRow + 1
 Loop
NRow = NRow --1
NForm.Show vbModeless
End Sub


* Added 2 lines of item and text

Public Sub adbtn_Click ()
itm = itm + 1
 With NForm
    With .Controls.Add ("Forms.Label.1", "KL", True)
        .Top = 154 + 48 * itm'Top position (move display position)
        .Left = 10'Left position
        .Height = 20'height
        .Width = 50'Width
        .BackColor = 25'Background color
        .BackStyle = 0
        .ForeColor = 1'Text color
        .Font.Name = "Meiryo"'Text Style
        .TextAlign = 2'Text position
        .FontSize = 16'Text size
        .Caption = "item"
    End With
    With .Controls.Add ("Forms.TextBox.1", "KT"&itm, True)'
       .Top = 154 + 48 * itm
       .Left = 70'Left position
       .Height = 20'height
       .Width = 250'width
       .BorderStyle = fmBorderStyleSingle'Border
       .BackColor = RGB (255, 255, 255)'Background color
       .ForeColor = RGB (0, 0, 0)'Text color
       .Font.Name = "Meiryo"'Text Style
       .TextAlign = 2'Text position
       .FontSize = 10'Text size
       .Text = ""'Display text
    End With
    With .Controls.Add ("Forms.Label.1", "PL", True)
        .Top = 178 + 48 * itm'Top position (move display position)
        .Left = 10'Left position
        .Height = 20'height
        .Width = 50'Width
        .BackColor = 25'Background color
        .BackStyle = 0
        .ForeColor = 1'Text color
        .Font.Name = "Meiryo"'Text Style
        .TextAlign = 2'Text position
        .FontSize = 16'Text size
        .Caption = "content"
    End With
    With .Controls.Add ("Forms.TextBox.1", "PT"&itm, True)
       .Top = 178 + 48 * itm
       .Left = 70'Left position
       .Height = 20'height
       .Width = 250'width
       .BorderStyle = fmBorderStyleSingle'Border
       .BackColor = RGB (255, 255, 255)'Background color
       .ForeColor = RGB (0, 0, 0)'Text color
       .Font.Name = "Meiryo"'Text Style
       .TextAlign = 2'Text position
       .FontSize = 10'Text size
       .Text = ""'Display text
    End With
       .ScrollBars = fmScrollBarsBoth'Show both vertical and horizontal
       .ScrollHeight = 202 + 48 * itm'height
       .ScrollWidth = 800'Width
 End With
End Sub

Public Sub Refbtn_Click ()
 With Worksheets ("password")
  Cells (NRow, 1) .Value = Me.KT1.Text * An error will occur here!
 End With
 MsgBox "Reflected."
End Sub


Refbtn is created so that only KT1 is input once.

I look forward to working with you.

vba
  • Answer # 1

    pointed out by ttyp03..I need to fix the point that there is no
    The cause of the "method or data member not found" error isMe.KT1.TextThat's the part.

    Controls dynamically added with Controls.AddMe.KT1Cannot be referenced. Only the ones placed at design time can be referenced with this.
    Me.Controls ("KT1"). TextThen the error will disappear.


    By the way, if you give a hint a little ahead of time,

    With the reference method called Me.Controls (), even if the number of text boxes increases, loop processing can be performed as shown below.

    For i = 1 to itm
        .Cells (NRow + i -1, 1) .Value = Me.Controls ("KT"&i) .Text
    Next