transfer data from userform to sheet

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,489
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
[TABLE="width: 574"]
<tbody>[TR]
[TD="class: xl65, width: 574, colspan: 7"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 574"]
<tbody>[TR]
[TD="class: xl65, width: 574, colspan: 7"].Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)
the error message
could not find the specified object[/TD]
[/TR]
</tbody>[/TABLE]
this is my code
HTML:
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
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
It is no surprise that VBA cannot find the objects with this line - the names of the two objects have been mashed together
Code:
.[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
Code:
MsgBox [COLOR=#333333]"TextBox" & "combobox" & 6[/COLOR]
are you trying to concatenate the values ?
Code:
[COLOR=#333333]Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)[/COLOR]
 
Last edited:
Upvote 0
It is no surprise that VBA cannot find the objects with this line - the names of the two objects have been mashed together
Code:
.[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
Code:
MsgBox [COLOR=#333333]"TextBox" & "combobox" & 6[/COLOR]
are you trying to concatenate the values ?
Code:
[COLOR=#333333]Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & Me.Controls("ComboBox" & i)[/COLOR]
it' doesn't work buddy the same problem
 
Upvote 0
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

Code:
[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
Code:
 .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

Code:
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
 
Last edited:
Upvote 0
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

Code:
[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
Code:
 .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

Code:
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
 
Upvote 0
Please post your amended code

here you are
HTML:
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
 
Last edited:
Upvote 0
Replace your code with this
- the code should run without failing and provide details of missing TextBoxes and ComboBoxes in message box

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top