Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Combobox multiple cell cange???

This is a discussion on Combobox multiple cell cange??? within the Excel Questions forums, part of the Question Forums category; Hello, Lets say i have a combobox on 'Sheet1'!B2 which lists the values in range 'Sheet2'A1: 'Sheet2'!A50. Sheet 2 looks ...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    25

    Default

    Hello,

    Lets say i have a combobox on 'Sheet1'!B2
    which lists the values in range 'Sheet2'A1:
    'Sheet2'!A50.

    Sheet 2 looks someting like this














    Name Company Number Account
    Fred fredscomp 12345 128


    the user can select the combobox on sheet1. In this case this would be fred.

    Now comes the question:

    How can i make excel (when fred is selected) to copy the cells 'Sheet2'!A2:'Sheet2'!D2
    to 'Sheet1'!C2:'Sheet1'!F2 ????

    Thus what i want to know is how to copy a range of cells when a certain item in the listbox is selected.

    Anyone that has excel wisdom enough to break this problem?

    Thanks.



    [ This Message was edited by: friso on 2002-04-04 07:07 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285

    Default

    Yep, put this in your Combobox change event:

    application.screenupdating=false
    If ComboBox1.Value = "Fred" Then
    Worksheets("sheet2").Range("a2:d2").Cut
    Worksheets("sheet1").Range ("c2:f2").Select
    ActiveSheet.Paste
    End If
    application.screenupdating=true

    End Sub

    There's probably an easier way as well but I am very, very drunk

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    25

    Default

    Thanks,

    However i have a randge of 50 items in the listbox which names are not known in advance.

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    Try the following:

    Make Sheet1!C2 the linked cell to the combobox and make the input range Sheet2!A1:A50. Then in cell Sheet1!D2 put the following formula: =VLOOKUP(Sheet1!$C$2,Sheet2!$A$1:$D$50,2,FALSE)

    Then in cell Sheet1!E2 put the following formula: =VLOOKUP(Sheet1!$C$2,Sheet2!$A$1:$D$50,3,FALSE)

    Then in cell Sheet1!F2 put the following formula: =VLOOKUP(Sheet1!$C$2,Sheet2!$A$1:$D$50,4,FALSE)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    25

    Default

    Hi Al,

    The function gives an error..any suggestions. With error i mean excel does not accept the statement at all.

    thanks

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    25

    Default

    Al this code is accepted but it gives an #NA
    in cellD2:
    =VLOOKUP(C2;'Sheet2'!A2:A50;2;FALSE)

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    25

    Default

    sorry i meant i put this code in:


    =VLOOKUP(C2;'Sheet2'!A2:D50;2;FALSE)

    still get an #NA though

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    Make sure that C2 is the comboboxes linked cell. Go into design mode and right click on the combobox. Fill in C2 in the LinkedCell field.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    25

    Default

    it is the linked cell, still nothing i'm affraid. Value of C2 = 1 (which i selected of course.

  10. #10
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802

    Default

    try using =INDEX(Sheet2!A2:D50,C2,1) in D2 instead of VLOOKUP, does that help?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com