Command button naked range to copy into specific range

[Patriot2879]

Hi please can you help I have a command button1 with s list for example: shop, car, home. If home is selected then what I type into the text box I want this copied into sheet2 A2:A5. If car is selected I want the text in the textbox copied into sheet2 B2:B5 hope this makes sense please can you help? I have a button called update where I want the code to go.

 

[Patriot2879]

Re: Command button named range to copy into specific range

Hi yes thanks. Then what I wanted is if they chose shop for the data in each text box 1,2 and 3 to go into c1,c2 and c3 then home onto d1,d2 and d3 for example. Thanks for your time and help

 

[Yongle]

Re: Command button named range to copy into specific range

Then what I wanted is if they chose shop for the data in each text box 1,2 and 3 to go into c1,c2 and c3 then home onto d1,d2 and d3

car into B1:B3 , shop into C1:C3 , home into D1:D3 like this:

Private Sub CommandButton1_Click()
    Dim c As String
    Select Case ComboBox1.Value
        Case "car":     c = "[COLOR=#ff0000]B[/COLOR]"
        Case "home":    c = "[COLOR=#008080]D[/COLOR]"
        Case "shop":    c = "[COLOR=#0000ff]C[/COLOR]"
        Case Else:      Exit Sub        'prevents code crashing if nothing selected
    End Select
    With Sheets("Sheet2")
        .Cells(1, c) = TextBox1.Value
        .Cells(2, c) = TextBox2.Value
        .Cells(3, c) = TextBox3.Value
    End With
End Sub

 

[Patriot2879]

Re: Command button named range to copy into specific range

Thankyou shall try this out now

 

[Patriot2879]

HI I have the code working now thanks for your help. (see below)is there anyway I can have the data copied over to outgoings so it goes across and not down? for example energy supplier info I want the data to go in B2, B3, B4, B5, B6 and Phone supplier going into C2, C3, C4, C5, C6 for example

Private Sub CommandButton2_Click()
   Dim c As String
    Select Case ComboBox1.Value
          Case "Energy Supplier":     c = "B"
          Case "Phone Supplier":    c = "C"
          Case "Broadband Supplier":    c = "D"
          Case Else:      Exit Sub        'prevents code crashing if nothing selected
      End Select
    With Sheets("Outgoings")
         .Cells(1, c) = ComboBox1.Value
         .Cells(3, c) = TextBox1.Value
         .Cells(4, c) = TextBox2.Value
         .Cells(2, c) = TextBox3.Value
         .Cells(5, c) = TextBox4.Value
        .Cells(6, c) = TextBox5.Value

     End With
ActiveWorkbook.Save
End Sub

 

[Patriot2879]

sorry my mistake for example energy supplier info I want the data to go in B2, C2, D2, E2, F2 and Phone supplier going into B3, C3, D3, E3, F3 for example

 

[Yongle]

You have made many mistakes on this thread and (as you are now discovering) VBA is not as easy to fix as Excel

You need to understand how the code works, so it is better if you amend it yourself
You need to switch rows for columns and then it should do what you want

Replace

Dim c As String

with

Dim r As Long

Replace values under Select Case like this

Case "Energy Supplier":     r = 2
etc

and to send combobox and textbox values to the correct row(r) and column :

.Cells(r, "B") = ComboBox1.Value
etc

 

[shg]

Private Sub CommandButton2_Click()
  Select Case ComboBox1.Value
    Case "Energy Supplier"
      Range("B2:F2").Value = Array(ComboBox1.Value, TextBox1.Value, TextBox2.Value, _
                                   TextBox3.Value, TextBox4.Value, TextBox5.Value)
    Case "Phone Supplier"
      Range("B3:F3").Value = Array(ComboBox1.Value, TextBox1.Value, TextBox2.Value, _
                                   TextBox3.Value, TextBox4.Value, TextBox5.Value)
    Case "Broadband Supplier"
      ' ...
  End Select
  ActiveWorkbook.Save
End Sub

 

[Patriot2879]

Thank you for the help. I am still new to this and don't understand it fully yet. I do really enjoy learning vba and appreciate all your advise and help

 

[Patriot2879]

hi thank you how do I tell what sheet the data to be added to for example.. please can you advise where I add this

With Sheets("Outgoings")

 

[Patriot2879]

I have tried adding the line where I name what page for data to go into but get an error, please can you advise?

Private Sub CommandButton2_Click()
  Select Case ComboBox1.Value
    Case "Energy Supplier"
With Sheets ("Outgoings")
      Range("B2:F2").Value = Array(ComboBox1.Value, TextBox1.Value, TextBox2.Value, _
                                   TextBox3.Value, TextBox4.Value, TextBox5.Value)
    Case "Phone Supplier"
      Range("B3:F3").Value = Array(ComboBox1.Value, TextBox1.Value, TextBox2.Value, _
                                   TextBox3.Value, TextBox4.Value, TextBox5.Value)
    Case "Broadband Supplier"
      ' ...
  End Select
  ActiveWorkbook.Save
End Sub