Combobox multiple cell cange???
Combobox multiple cell cange???
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Combobox multiple cell cange???

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

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