transfer data from userform to sheet

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
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

<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
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:
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
how i can make from a23:g23
i try but gives me error

Lastrow = .Cells(Rows.Count, 1).End(23).Row + 1
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
how i can make from a23:g23

make what ?



i try but gives me error

Lastrow = .Cells(Rows.Count, 1).End(23).Row + 1

Try
Code:
[COLOR=#333333]Lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1[/COLOR]
 
Last edited:
Upvote 0
make what ?





Try
Code:
[COLOR=#333333]Lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1[/COLOR]
you seem don't understand me i would transfer data from the range a23:g23 if you're confusing i'll attach my file
 
Upvote 0
I understand : Transfer data from A23:G23

I do not understand : Where are you putting those 7 values ?
 
Upvote 0
Upvote 0
I am not able to download your files
I hope someone else is able to help you
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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