Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    About a week ago I posted a question about combo boxes, I am going to try again and this time make it clearer and attach the Excel file.

    I have values on one sheet called "Display"
    Description/Color/Size/Fabric Group

    The other worksheet is called "Order". This will be an order sheet, so just like any regular order form, the customer will click a drop down list and see the products, as above: Description, Color, Size, Fabric Group. When they select a product that they want I want it to populate the cells accordingly: For example, the first cell the drop down (combo box) is in is in cell A1, so the combo box has 4 columns: cell A1 would show the description, cell B1 would show the Color and C1 the Size and D1 the fabric group.

    The customer now wants to select another product to order, so now they click in the cell underneath, cell A2 and repeat the above procedure: cells A2, B2, C2 and D2 should be populated and so forth.

    It is a piece of cake in Access, but I really want this to be possible in Excel without using a User Form.

    Thank you if you are able to help at all.

    Tina
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want 4 columns, you should set the ColumnCount property to 4.

    You could use the following code:

    Code:
    Private Sub ComboBox1_Change()
      Dim r As Long
      Dim c As Long
      r = Cells(Rows.Count, 1).End(xlUp).Row + 1
      For c = 1 To 4
    	Cells(r, c) = Me.ComboBox1.Column(c - 1)
      Next c
    End Sub
    The combo box will remain over cell A1. See the attached version.
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='778865' date='08-Jun-2009 16:56']If you want 4 columns, you should set the ColumnCount property to 4.

    You could use the following code:

    Code:
    Private Sub ComboBox1_Change()
      Dim r As Long
      Dim c As Long
      r = Cells(Rows.Count, 1).End(xlUp).Row + 1
      For c = 1 To 4
    	Cells(r, c) = Me.ComboBox1.Column(c - 1)
      Next c
    End Sub
    The combo box will remain over cell A1. See the attached version.[/quote]

    Hans,

    Thanks so much for this. I tried it briefly and it looked great. I am going to populate the first sheet with real data and test it out.

    Cheers!

    Tina

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •