code needed to send to 2 sheets

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.....


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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi,
try using the index property of your combobox to select the correct range.

Could be done better but something like this maybe:

Code:
r = Me.ComBoBox4.ListIndex
    r = 18 + (18 * r) + 3
    'write data to Performance worksheet
    With Sheets("Performance")
        .Cells(.Cells(r, 4).Resize(, 6)).Value = Array(ComboBox1.Value, ComboBox3.Value, ComboBox2.Value, _
                                                       TextBox1.Text, TextBox2.Text, TextBox3.Text)
    End With

Dave
 
Upvote 0
Hi

This part of code sends data to 1 of 3 sheets called Unit A or Unit B or Unit C Depending on which optionbutton is selected (Unit A ,Unit B , Unit C)....How do I incorporate your code to send value in Combobox4 to sheet called Performance A , B or C
Code:
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)

Where it goes in sheet performance A , performance B , performance C will depend on Unit A,B or C,Date and Shift selected in useform

Thank you
 
Upvote 0
Hi,
sorry, I was only thinking a solution to index the different ranges you listed but as a guess, try placing code above:

Code:
r = Me.ComBoBox4.ListIndex
    r = 18 + (18 * r) + 3
    'write data to Performance worksheet
    With Sheets("Performance")
        .Cells(.Cells(r, 4).Resize(, 6)).Value = Array(ComboBox1.Value, ComboBox3.Value, ComboBox2.Value, _
                                                       TextBox1.Text, TextBox2.Text, TextBox3.Text)
    End With
    
    '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

Dave
 
Upvote 0
Thanks

This part of code gives error - subscript out of Range
Code:
With Sheets("Performance")

Maybe due to 3 sheets called Performance A , Performance B & Performance C
 
Upvote 0
Thanks

This part of code gives error - subscript out of Range
Code:
With Sheets("Performance")

Maybe due to 3 sheets called Performance A , Performance B & Performance C

You get that error as sheet name does not exist - your post however, stated "to another sheet called Performance" which is why I named it as such. You will need to amend code accordingly to which sheet you want the code to place the data or is this also dependant on your combobox selection?

Dave
 
Upvote 0
hI

There are 3 sheets called Performance A , Performance B & Performance C (sorry I did mention just performance)....I changed 1 sheet to just Performance and gave error - runtime error5

Code:
 .Cells(.Cells(r, 4).Resize(, 6)).Value = Array(ComboBox1.Value, ComboBox3.Value, ComboBox2.Value, _
                                                       TextBox1.Text, TextBox2.Text, TextBox3.Text)

There is 3 optionbuttons which choose which sheet
 
Last edited:
Upvote 0
hI

There are 3 sheets called Performance A , Performance B & Performance C (sorry I did mention just performance)....I changed 1 sheet to just Performance and gave error - runtime error5

Code:
 .Cells(.Cells(r, 4).Resize(, 6)).Value = Array(ComboBox1.Value, ComboBox3.Value, ComboBox2.Value, _
                                                       TextBox1.Text, TextBox2.Text, TextBox3.Text)

There is 3 optionbuttons which choose which sheet

You will need to use another variable to determine which option has been selected & then pass caption which I assume is the sheet name? to it to se in procedure (in similar way already doing.) Code below is an example, you will need to update accordingly to meet with you actual project need.

Code:
    r = ComboBox4.ListIndex
    r = 18 + (18 * r) + 3
    
    For i = 4 To 6
        If Me("OptionButton" & i) Then wsPerformance = Me("OptionButton" & i).Caption
    Next
    'write data to Performance worksheet
    With Sheets(wsPerformance)
        .Range(.Cells(r, 4), .Cells(r, 9)).Value = Array(ComboBox1.Text, ComboBox3.Text, ComboBox2.Text, _
                                                         TextBox1.Text, TextBox2.Text, TextBox3.Text)
    End With

I amended the range code to output the array & hopefully, will work for you - not sure why getting error as unable to test - I had just quickly lifted what you already had mainly to give an example for indexing the range.

Dave
 
Upvote 0
hI

I got a error so I changed to the following.Now no error and data transferred except Combobox4 to Perfomance sheets (not transferred)

Code:
r = ComboBox4.ListIndex
    r = 18 + (18 * r) + 3
    
    For i = 1 To 3
        If Me("OptionButton" & i) Then wsPerformance = Me("OptionButton" & i).Caption
    Next
    'write data to Performance worksheet
    With Sheets(wsPerformance)
        .Range(.Cells(r, 4), .Cells(r, 9)).Value = Array(ComboBox1.Text, ComboBox3.Text, ComboBox2.Text, _
                                                         TextBox1.Text, TextBox2.Text, TextBox3.Text)
    End With
 
Upvote 0
Code:
For i = 1 To 3
        If Me("OptionButton" & i) Then wsPerformance = Me("OptionButton" & i).Caption
    Next

You are using the same optionbuttions for wsPerformance variable as whatsheet variable which is why nothing happens in Performance worksheet.

as a guess, try this amendment to to for loop code:

Code:
For i = 1 To 3
        If Me("OptionButton" & i) Then
            whatsheet = Me("OptionButton" & i).Caption
            wsPerformance = "Performance " & _
                            Application.WorksheetFunction.Choose(i, "A", "B", "C")
        End If
    Next

I have assumed worksheets are name "Performance A" etc with a space?

Hope helpful

Dave.
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,503
Members
448,575
Latest member
hycrow

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