View Full Version : Transferring Userform data to excel
trishgyrl
01-12-2018, 11:45 AM
Hi. Please help.
I am trying to use code that says for every textbox control where visible property is true, then, transfer the value in the textbox to rows in Column A of Sheet2 in Excel. Can someone please let me know where I am going wrong?
If bComplete Then
Dim nextrow As Long
Dim ctrl As Control
Sheets("sheet2").Activate
'determine the next empty row
nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
'transfer the control values
For Each ctrl In Me.Controls
If ctrl.Visible = True Then
If TypeName(ctrl) = "TextBox" Then
Cells(nextrow + 1, 1) = ctrl.Value
End If
End If
Next ctrl
Unload Me
End If
JKwan
01-12-2018, 12:07 PM
try this:
Private Sub CommandButton1_Click()
Dim nextrow As Long
Dim ctrl As Control
Dim ws As Worksheet
If bcomplete Then
Set ws = Worksheets("Sheet2")
ws.Select
'determine the next empty row
nextrow = FindLastRow(ws, "A") + 1
'transfer the control values
For Each ctrl In Me.Controls
If ctrl.Visible Then
If TypeName(ctrl) = "TextBox" Then
ws.Cells(nextrow, 1) = ctrl.Value
nextrow = nextrow + 1
End If
End If
Next ctrl
Unload Me
End If
End Sub
Function FindLastRow(ByVal ws As Worksheet, ColumnLetter As String) As Long
FindLastRow = ws.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function
trishgyrl
01-12-2018, 12:37 PM
Wow! That worked perfectly! Thanks soooo much :clap:
Function FindLastRow(ByVal ws As Worksheet, ColumnLetter As String) As Long
FindLastRow = ws.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function
Slick. I like.
JKwan
01-13-2018, 01:08 PM
Thanks, Sam.
i like it too
trishgyrl
01-17-2018, 02:38 PM
Thanks, Sam.
i like it too
If I want to add the caption of each visible label to the worksheet, as well, how would I go about that?
I would like the caption of each visible label to show in rows of column A and the value of the textbox to go in rows of column B.
I added another variable but, I'm getting an error that say "object does not support this property or method." Please help. Thanks in advance.
Dim nextrow As Long
Dim nextrows As Long
Dim ctrl As Control
Dim ws As Worksheet
If bComplete Then
Set ws = Worksheets("Sheet2")
ws.Select
'determine the next empty row
nextrow = FindLastRow(ws, "A") + 1
nextrows = FindLastRow(ws, "B") + 1
'transfer the control values
For Each ctrl In Me.Controls
If ctrl.Visible Then
If TypeName(ctrl) = "TextBox" Then
ws.Cells(nextrow, 1) = ctrl.Value
nextrow = nextrow + 1
Else
If TypeName(ctrl) = "Label" Then
ws.Cells(nextrows, 2) = ctrl.Value
nextrows = nextrows + 1
End If
End If
End If
Next ctrl
Unload Me
End If
JKwan
01-18-2018, 07:46 AM
Instead of
ws.Cells(nextrows, 2) = ctrl.Value
use
ws.Cells(nextrows, 2) = ctrl.Caption
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.