Transfering Data From Userform To Sheet Under Relevant Column And Row

[paul_pearson]

Hi

I need some help please with the code transferring data from userform to sheet.The sheet will be protected == password is 1357
The userform looks at set Number and then finds it in the sheet and transfers the data.

Thanks

Paul

[Excel Fox]

I've made some modifications to your sheet. Notice the rngToCopy named range. You can move it around if required, but just keep whatever format you need. Also, revisions in the code are as follows


Private Sub UserForm_Initialize()

Dim i As Long

'.
'.
'.
'.
'.

With Worksheets("LENTICULAR-MEMBRANES")
.Unprotect Password:="1357"
.Protect UserInterfaceOnly:=True, Password:="1357"
End With

End Sub

Private Sub CommandButton1_Click()

Dim lngCol As Long
Dim ctl As Object
For Each ctl In Me.Controls
If ctl.Tag <> vbNullString Then
If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
End If
Next
With Sheets("LENTICULAR-MEMBRANES")
lngCol = .Rows("3:3").Find(What:="SET " & ComboBox2.Text, LookAt:=xlWhole).Column
With .Cells(.Rows.Count, lngCol).End(xlUp).Offset(1).Cells(1)
Sheets("LENTICULAR-MEMBRANES").Range("rngToCopy").Copy .Cells(1)
.Resize(6).Value = Application.Transpose(Array("DATE", "LENTICULARS CHANGED", "MEMBRANES CHANGED", "VARIETY", "VOLUME", TextBox2.Text))
.Offset(, 1).Resize(5) = Application.Transpose(Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text))
End With
End With

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
Next

End Sub

[paul_pearson]

I`ve changed the layout to the data capture sheet.I have set up the sheet so data goes down the sheet for each set.With the userform can it look for the heading for the set and place the information going down the sheet.If I select C2 in the Userform Lenticular set then it places that info into C2 data capture sheet

Thanks

Paul

[paul_pearson]

This is altered to a set per sheet.On the userform depending which set selected depends on which sheet data goes...if C1 selected then data goes to C1 sheet etc..etc..

Paul

[paul_pearson]

bump

[Excel Fox]

Here's the revised code



Private Sub CommandButton1_Click()

Dim lngCol As Long
Dim ctl As Object
For Each ctl In Me.Controls
If ctl.Tag <> vbNullString Then
If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
End If
Next
With Sheets("LENTICULAR-MEMBRANES")
lngCol = .Rows("2:2").Find(What:="SET " & ComboBox2.Text, LookAt:=xlWhole).Column
With .Cells(.Rows.Count, lngCol).End(xlUp).Offset(1)
.Resize(, 6) = Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text, TextBox2.Text)
End With
End With

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
Next

End Sub

[paul_pearson]

Thanks

I get an error - subscript out of range

I think it may have to do with the sheet names....

I changed the sheet names in code but still error

Thanks

Paul

[bakerman]

Makes sence. Where is this sheet.

With Worksheets("LENTICULAR-MEMBRANES")
.Unprotect Password:="1357"
.Protect UserInterfaceOnly:=True, Password:="1357"
End With

Also you use the Tag-property to check for empty objects but in not one object you've filled in the Tag-property. In this case this will never work properly.

[paul_pearson]

Thanks

That sheet was removed...there are 5 sheets C1,C2,C3,C4,C5....I did enter these sheets in place of the LENTICULAR-MEMBRANES but it still gave error

I will fill in the Tag property

I will see how this goes

Paul

[bakerman]

But that part of code is still in your Userform_Initialize procedure so it will always throw an error when starting-up your UF.
The way you filled in your sheets is also wrong. I suppose you will have to use the value of your Lenticular set -object to write to the correct sheet.
It will become something like this


With Sheets(ComboBox2.Value)
lngCol = .Rows("2:2").Find(What:="SET " & ComboBox2.Text, LookAt:=xlWhole).Column
With .Cells(.Rows.Count, lngCol).End(xlUp).Offset(1)
.Resize(, 6) = Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text, TextBox2.Text)
End With
End With

[paul_pearson]

Ok I will try again

Paul

[paul_pearson]

I filled in the tag property,changed the sheets names but still get error

What have I missed.Combobox2 entry decides on which sheet to enter data....is this the wrong approached?

Thanks

Paul

[Excel Fox]

Use this code


Option Explicit

Private Sub UserForm_Initialize()


Dim i As Long
Dim vList As Variant
Dim vSht As Variant

For i = CLng(Date - 2) To CLng(Date + 7)
vList = vList & Format(i, "dd/mm/yyyy") & ","
Next i
vList = Left(vList, Len(vList) - 1)
ComboBox1.List = Split(Mid(vList, 1), ",")

vList = ("C1,C2,C3,C4,C5")
ComboBox2.List = Split(Mid(vList, 1), ",")

vList = vbNullString
vList = "-,"
For i = CLng(Date - 2) To CLng(Date + 7)
vList = vList & Format(i, "dd/mm/yyyy") & ","
Next
vList = Left(vList, Len(vList) - 1)
ComboBox3.List = Split(Mid(vList, 1), ",")

vList = vbNullString
vList = "-,"
For i = CLng(Date - 2) To CLng(Date + 7)
vList = vList & Format(i, "dd/mm/yyyy") & ","
Next
vList = Left(vList, Len(vList) - 1)
ComboBox4.List = Split(Mid(vList, 1), ",")

vList = ("AA,BB,CC,DD,EE")
ComboBox5.List = Split(Mid(vList, 1), ",")
For Each vSht In Array("C1", "C2", "C3", "C4", "C5")
With Worksheets(vSht)
.Unprotect Password:="1357"
.Protect UserInterfaceOnly:=True, Password:="1357"
End With
Next


End Sub


Private Sub CommandButton1_Click()

Dim lngCol As Long
Dim ctl As Object
For Each ctl In Me.Controls
If ctl.Tag <> vbNullString Then
If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
End If
Next
With Sheets(ComboBox2.Value)
With .Cells(.Rows.Count, 2).End(xlUp).Offset(1)
.Resize(, 6) = Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text, TextBox2.Text)
End With
End With

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
Next

End Sub


By the way, please correct the sheet name from "C1 " to "C1"

[regc]

Thanks Excel fox

[paul_pearson]

Ok I got it now.That works
I modified another sheet and it now works as well
Thanks for all those who helped
Paul