sam__gleeson
Board Regular
- Joined
- Oct 11, 2012
- Messages
- 102
Hi
The following userform code sends data to sheets called Items 1 , Items 2 , Items 3 depending on which optionbutton selected..option Buttons for Item 1 , Item 2 , Item 3 ..when option button selected for an Item it transfers all info to that sheet.... if i add a Combobox to the code below how can i have it transfer to another sheet called Performance...if Day selected in userform it transfer to the cell D21:Z21,If afternoon selected it transfers to the cell D39:Z39 and if night selected it transfers to D57:Z57......
It will use the date from the userform to find the same date in the sheet Performance.....it will also use the Shift to select which range to populate.....it will also use the sheet selected to send the number to the correct sheet (item 1,item 2 , item3.....
The following userform code sends data to sheets called Items 1 , Items 2 , Items 3 depending on which optionbutton selected..option Buttons for Item 1 , Item 2 , Item 3 ..when option button selected for an Item it transfers all info to that sheet.... if i add a Combobox to the code below how can i have it transfer to another sheet called Performance...if Day selected in userform it transfer to the cell D21:Z21,If afternoon selected it transfers to the cell D39:Z39 and if night selected it transfers to D57:Z57......
It will use the date from the userform to find the same date in the sheet Performance.....it will also use the Shift to select which range to populate.....it will also use the sheet selected to send the number to the correct sheet (item 1,item 2 , item3.....
Code:
Private Sub CommandButton1_Click()
For Each ctl In Frame1.Controls
If TypeName(ctl) = "OptionButton" Then
If ctl.Value = False Then x = x + 1
End If
Next
If x = 3 Then MsgBox "Select a machine": Exit Sub
'check user input
For Each ctl In Me.Controls
If ctl.Tag <> vbNullString And ctl.Enabled Then
If ctl.Value = vbNullString Then MsgBox ctl.Tag: Exit Sub
End If
Next
For i = 1 To 3
If Me("OptionButton" & i) Then whatsheet = Me("OptionButton" & i).Caption
Next
'write data to worksheet
With Sheets(whatsheet)
.Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Resize(, 6) = Array(ComboBox1.Value, ComboBox3.Value, ComboBox2.Value, _
TextBox1.Text, TextBox2.Text, TextBox3.Text)
End With
'Clear all fields
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
If TypeName(ctl) = "OptionButton" Then ctl.Value = False
Next
MsgBox "Data Transferred"
End Sub