transfer data from userform to sheet

[abdelfattah]

hi guys
i have userform contains 35 textbox and 18 combobox i would transfer data to sheet i try make the code but it gives me error this line

<tbody>
</tbody>

.Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i) the error message could not find the specified object

<tbody>
</tbody>

this is my code

Private Sub CommandButton1_Click()
  Dim My_sh As Worksheet
  Set My_sh = Worksheets("sheet1") 
 Dim Lastrow As Integer  
Dim i%  Application.ScreenUpdating = False 
 With My_sh    Lastrow = .Cells(Rows.Count, 1).End(3).Row + 1  
  .Range(.Cells(Lastrow - 1, 1), .Cells(Lastrow - 1, 7)).Copy    
.Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteFormats  
  For i = 1 To 7     
 .Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)    
  Me.Controls("TextBox" & "combobox" & i) = ""   
 Next 
 End With 
 Application.ScreenUpdating = True  
Application.CutCopyMode = False  
MsgBox "ok"
end sub

 

[Yongle]

It is no surprise that VBA cannot find the objects with this line - the names of the two objects have been mashed together

.[COLOR=#333333]Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)[/COLOR]

Try this line to understand what you are telling VBA to use as the name of the object

MsgBox [COLOR=#333333]"TextBox" & "combobox" & 6[/COLOR]

are you trying to concatenate the values ?

[COLOR=#333333]Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)[/COLOR]

 

[abdelfattah]

It is no surprise that VBA cannot find the objects with this line - the names of the two objects have been mashed together

.[COLOR=#333333]Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)[/COLOR]

Try this line to understand what you are telling VBA to use as the name of the object

MsgBox [COLOR=#333333]"TextBox" & "combobox" & 6[/COLOR]

are you trying to concatenate the values ?

[COLOR=#333333]Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)[/COLOR]

it' doesn't work buddy the same problem

 

[Yongle]

you did not answer my question

 

[abdelfattah]

you did not answer my question

if you mean concatenate the values of course i would so

 

[Yongle]

if you mean concatenate the values of course i would so

The code I provided works and does concatenate the values - but it needs the . at the beginning to make sure it goes in correct worksheet

[SIZE=4][COLOR=#ff0000].[/COLOR][/SIZE]Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)

it' doesn't work ... the same problem

Are you sure that the code is not failing on the next line down
- BOTH these lines are BAD in your original code for the same reason

 .Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)    
  Me.Controls("TextBox" & "combobox" & i) = ""

If still failing on the first line .. then VBA is NOT finding either a TextBox or a ComboBox and that is why your code is failing

Add a command button to your userform and use code below
- run the userform and click on the new command button
- the message box will tell you which textbox(es) or combobox(es) are not on the form

Private Sub [COLOR=#ff0000]CommandButton2[/COLOR]_Click()
    Dim Ctrl As Control, T As String, C As String, Tb As String, Cb As String, i As Long
    
    For i = 1 To 7
        T = "TextBox" & i
        C = "Combobox" & i
        
        On Error Resume Next
            Set Ctrl = Me.Controls(T)
            If Err.Number <> 0 Then Tb = Tb & vbCr & T
            On Error GoTo 0
    
        On Error Resume Next
            Set Ctrl = Me.Controls(C)
            If Err.Number <> 0 Then Cb = Cb & vbCr & C
        On Error GoTo 0
    Next i
        
    MsgBox Tb & vbCr & Cb, vbInformation, "Objects not found"
End Sub

 

[abdelfattah]

The code I provided works and does concatenate the values - but it needs the . at the beginning to make sure it goes in correct worksheet

[SIZE=4][COLOR=#ff0000].[/COLOR][/SIZE]Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)

Are you sure that the code is not failing on the next line down
- BOTH these lines are BAD in your original code for the same reason

 .Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)    
  Me.Controls("TextBox" & "combobox" & i) = ""

If still failing on the first line .. then VBA is NOT finding either a TextBox or a ComboBox and that is why your code is failing

Add a command button to your userform and use code below
- run the userform and click on the new command button
- the message box will tell you which textbox(es) or combobox(es) are not on the form

Private Sub [COLOR=#ff0000]CommandButton2[/COLOR]_Click()
    Dim Ctrl As Control, T As String, C As String, Tb As String, Cb As String, i As Long
    
    For i = 1 To 7
        T = "TextBox" & i
        C = "Combobox" & i
        
        On Error Resume Next
            Set Ctrl = Me.Controls(T)
            If Err.Number <> 0 Then Tb = Tb & vbCr & T
            On Error GoTo 0
    
        On Error Resume Next
            Set Ctrl = Me.Controls(C)
            If Err.Number <> 0 Then Cb = Cb & vbCr & C
        On Error GoTo 0
    Next i
        
    MsgBox Tb & vbCr & Cb, vbInformation, "Objects not found"
End Sub

thanks but i need helping the part of important it begins from a 17:a25 if these ranges are empty i don't need it i can many ways transfer data from userform to sheet i only want the filling rows when i fill in userform and transfer to sheet

 

[Yongle]

Please post your amended code

 

[abdelfattah]

Please post your amended code

here you are

Private Sub CommandButton1_Click()
  Dim My_sh As Worksheet 
 Set My_sh = Worksheets("sheet1") 
 Dim Lastrow As Integer 
 Dim i%  
Application.ScreenUpdating = False  
With My_sh    
Lastrow = .Cells(Rows.Count, 1).End(3).Row + 1  
  .Range(.Cells(Lastrow - 1, 1), .Cells(Lastrow - 1, 7)).Copy    
.Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteFormats  
  For i = 1 To 7    
  .Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)         
 Next 
 End With
  Application.ScreenUpdating = True 
 Application.CutCopyMode = False  
MsgBox "TextBox" & "combobox" & 6
end sub

 

[Yongle]

Replace your code with this
- the code should run without failing and provide details of missing TextBoxes and ComboBoxes in message box

Private Sub CommandButton1_Click()
    Dim My_sh As Worksheet
    Set My_sh = Worksheets("sheet1")
    Dim Lastrow As Integer, i As Integer
    Dim t As String, c As String
    Application.ScreenUpdating = False
    With My_sh
        Lastrow = .Cells(Rows.Count, 1).End(3).Row + 1
        .Range(.Cells(Lastrow - 1, 1), .Cells(Lastrow - 1, 7)).Copy
        .Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteFormats
        For i = 1 To 7
            On Error Resume Next
                Debug.Print Me.Controls("TextBox" & i).Name
                If Err.Number <> 0 Then t = t & vbCr & "TextBox" & i
                On Error GoTo 0
            On Error Resume Next
                Debug.Print Me.Controls("ComboBox" & i).Name
                If Err.Number <> 0 Then c = c & vbCr & "ComboBox" & i
                On Error GoTo 0
            On Error Resume Next
            .Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)
            On Error GoTo 0
        Next
     End With
      
     Application.CutCopyMode = False
    MsgBox t & vbCr & c
End Sub