combobox in exel

Microsoft Office 2007 home and student
June 16, 2010 at 10:17:25
Specs: Windows XP
i would like to have a combobox that when you select a line in the box the 4 first value are copied to the cell of my choosing(any cell on the worksheet).


See More: combobox in exel

Report •


#1
June 17, 2010 at 07:48:34
Hi,

If you have a ComboBox named ComboBox1 on "Sheet1"
and
the combobox is linked to a list in cells A1 to A20, then add this code to the combobox's change event
(In design mode, right-click the box and select view code)

Private Sub ComboBox1_Change()
Worksheets("Sheet1").Range("B1") = Worksheets("Sheet1").Range("A1") _
                                    .Offset(ComboBox1.ListIndex, 0)
Worksheets("Sheet1").Range("B2") = Worksheets("Sheet1").Range("A1") _
                                    .Offset(ComboBox1.ListIndex + 1, 0)
Worksheets("Sheet1").Range("B3") = Worksheets("Sheet1").Range("A1") _
                                    .Offset(ComboBox1.ListIndex + 2, 0)
Worksheets("Sheet1").Range("B4") = Worksheets("Sheet1").Range("A1") _
                                    .Offset(ComboBox1.ListIndex + 3, 0)
End Sub

Exit design mode.

Now when you select a value in the combobox, that value and the next three values in the list will be displayed on "Sheet1" in cells B1 to B4.

Hope this is what you were looking for, or that you can use this as a base for modifications.

Regards


Report •

#2
June 17, 2010 at 08:23:10
I would like to be able to click on any cell on the worksheet and the first 4 value of my combobox are now transfer to the active cell(the one i just click not the linkcell) on my worksheet.
Thanks for your help.

Report •

#3
June 17, 2010 at 09:34:48
Hi,

You can do that with the change selection event.

While you can do this for the whole worksheet, it can be difficult because then you can't change any cell's data - click it and bang! you get your list.

What I suggest is define a range where this action works - you will see in the code I have designated a range B1:H50
Change as appropriate.

Make sure you test this on a backup workbook as changes made by macros cannot be undone with the Undo button.

As before the linked list for the combobox is in cells A1 to A20
The combobox is CombBox1

Right-click on the name tab of the worksheet containing the combobox
Enter this code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'disable events to stop changes made by this code from re-triggering it
Application.EnableEvents = False

Dim objIsect As Range

On Error GoTo ErrHnd

'test if selection is in range "B1:H50"
'change as required
Set objIsect = Intersect(Target, ActiveSheet.Range("B1:H50"))

On Error Resume Next
If Not objIsect Is Nothing Then
    On Error GoTo ErrHnd
    With ActiveSheet
        Target.Value = .Range("A1").Offset(Me.ComboBox1.ListIndex, 0).Value
        Target.Offset(1, 0).Value = _
                    .Range("A1").Offset(Me.ComboBox1.ListIndex + 1, 0).Value
        Target.Offset(2, 0).Value = _
                    .Range("A1").Offset(Me.ComboBox1.ListIndex + 2, 0).Value
        Target.Offset(3, 0).Value = _
                    .Range("A1").Offset(Me.ComboBox1.ListIndex + 3, 0).Value
    End With
End If
On Error GoTo ErrHnd
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Save workbook in a macro-enabled format.
Use Alt + f11 to return to main Excel window.

Select a value from the drop-down combo box.
Click any cell in the specified range and four values are pasted in.

If you want all 4 values in One cell just use:

    With ActiveSheet
        Target.Value = .Range("A1").Offset(Me.ComboBox1.ListIndex, 0).Text & _
                .Range("A1").Offset(Me.ComboBox1.ListIndex + 1, 0).Text & _
                .Range("A1").Offset(Me.ComboBox1.ListIndex + 2, 0).Text & _
                .Range("A1").Offset(Me.ComboBox1.ListIndex + 3, 0).Text
    End With

in place of the four cells

Regards


Report •

Related Solutions

#4
June 17, 2010 at 10:04:20
I wanted the four first value of the line selected not of the list and i wanted to click the cell first and then select the value in the combobox to be then pasted in the cell
Thanks!!

Report •

#5
June 17, 2010 at 15:18:50
Hi,

You will have to explain what you mean by: the four first value of the line selected not of the list

The list (A1:A20 in my example) is the list that is displayed in the combo box.

So if you had the letters A to T in cells A1 to A20, then the ComboBox will show the letters A to T and you can select one of them.

If you select the letter D for example, the code I provided displays the letters D, E, F and G.

Obviously this isn't what you want - so can you provide a description along those lines.

Regards


Report •

#6
June 17, 2010 at 15:23:03
Hi,

To get data from your combo box into the cell you selected before using the combo box, add this code to the combo box change event:

Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1.Value
End Sub

It should then be a simple step to display additional data related to the selected line.

Regards


Report •

#7
June 21, 2010 at 05:11:57
What i mean is let say that the listing in the combobox is
pal1 is the name of mary
pal2 is the name of george
pal3 is the name of lucie
exetera.....
I would like only pal1,pal2 or pal3 to be copied to the activecell
Thanks for all your help!!!

Report •

#8
June 21, 2010 at 06:01:13
Hi,

Sorry, but I still don't get it.

You have a combo box containing a list:

1. What is the source of the list ? In other words, where is the list stored
2. What is displayed in the combo box - is it pal1, pal2, pal3
or is it mary, lucie, george or is it something else - does the combo box display more than one column
3.You say you want only pal1, pal2, pal3 copied to the active cell - but previously you said I wanted the four first value of the line selected
4. It would help if you gave an actual example along these lines:
The combo box is a list of 10 items, which are stored in cells X1 to X10.
The list contains 10 names mary, lucie, george etc.
When I select mary from the combo box I want "mary" copied to the active cell, or whatever it is that you want.

Thanks


Report •

#9
June 21, 2010 at 06:13:27
My combobox is in let said a other sheet call "cat"
it is listed as "Cat!I2:I78" Listfillrange in the property of the combobox.
in the listfillrange i have
"1PRS normale work day"
"4MAE sick leave"
"4VNC holiday leave"
"ecetera...."
now when i click a cell in my worksheet and then go to my combobox and select a line in the combobox i would like just the 4 first number and letter to be copied to the activecell ex:1PRS

Report •

#10
June 21, 2010 at 07:27:06
Hi,

This code in the change event will paste the first four characters of the line selected in the ComboBox to the active cell - the cell selected before you used the ComboBox.

Private Sub ComboBox1_Change()
ActiveCell.Value = Left(ComboBox1.Value, 4)
End Sub

Hope I've got it right this time!

Regards


Report •

#11
June 21, 2010 at 07:34:26
Yes now that works for me
Thanks!!!
My code is now

Private Sub ComboBox1_Change()
If ComboBox1.Value > 0 Then
ActiveCell.Value = Left(ComboBox1.Text, 4)
Else
ComboBox1.Value = 0
End If
Sheet(Application.ActiveSheet.Name).Select
ComboBox1.Value = 0
End Sub

And it all works fine now
Thank very much for all your help i could not have done it without you!!!!


Report •

#12
June 21, 2010 at 12:21:24
Hi,

Thanks for letting us know that you have got a working solution.

You might like to try this version which puts a message in the combo box 'Select from List' (make it anything you like), and after selecting a line and transferring data to the active cell, it shows the message again.

Private Sub ComboBox1_Change()
'disable events
Application.EnableEvents = False
'test if combo box selection is not the row 1 message
If ComboBox1.Value <> Worksheets("Sheet1").Range("A1").Value Then
    'put first four characters of selected line in active cell
    ActiveCell.Value = Left(ComboBox1.Text, 4)
End If
'move focus off combo box
ActiveCell.Select
'set combo box to the row 1 message
'(Although this macro runs again despite enableevents=False
' the value in the active cell is not changed as the
' combo box value is now the row 1 message)
ComboBox1.ListIndex = 0
're-enable events
Application.EnableEvents = True
End Sub

You will need to change the addresses for the list. The 'message' is a new item before the main list. In the example it is in cell A1.

From a technical point of view, I was unable to get ListIndex=0 to work - which would have been the logical way to keep the display on the first row. Without a dummy first row message ListIndex=0 causes the macro to re-run, and the active cell is set to the 1st 4 characters of the row 1 message. Without a dummy 1st row message, there is no way to stop the active cell being changed - in the code the dummy first row message is used to stop the active cell being changed.

Also it is interesting to note that the combo box change code runs a second time despite enableevents=false
This meant that you couldn't just change the value in the combo box after moving the initial selection to the active cell.

Any comments/suggestions on these issues would be welcome.

Regards


Report •

#13
June 22, 2010 at 06:09:08
I try your code but it give me a error when i close the sheet.I have a other question for you how do i keep the first line of my combobox always listed,when i save and close the worksheet then i reopen it the combobox is blank.I would like it to open on the first entry of the combobox (like a title)

Report •

#14
June 22, 2010 at 08:02:17
Hi,

What error message do you get.
I don't get one when I close the workbook.

When I open the workbook, the Comb box is displayed with my message line shown.

In the Example the message was in the first row of the range containing the drop-down list.

Make sure the the ListFillRange in the Properties box starts with the cell containing the message.

Also in the code, this line must point to the cell containing the message:

If ComboBox1.Value <> Worksheets("Sheet1").Range("A1").Value Then

Regards


Report •

#15
June 22, 2010 at 08:26:11
The line
If ComboBox1.Value <> Worksheets("Sheet1").Range("A1").Value is in yellow in my case "If ComboBox1.Value <> Worksheets("Cat").Range("B2")".when i close the worksheet
i did change the "sheet" for mine and also the reference cell .
it give me a error and now it only paste the four letter of cell "A1" in my case cell "B2"

Report •

#16
June 22, 2010 at 08:56:25
Hi,

A line highlighted in Yellow shows that the code has stopped running at that line.

Try clicking the f8 function key and see if the highlight moves to the next line.
If it does - keep hitting f8 until it gets to the end.
If you get an error message please post the exact message and the line of code highlighted in yellow.

Here is the code using a worksheet named "Cat" and a list starting at Cell B2

Private Sub ComboBox1_Change()
On Error GoTo ErrHnd
'disable events
Application.EnableEvents = False
'test if combo box selection is not the row 1 message
If ComboBox1.Value <> Worksheets("Cat").Range("B2").Value Then
    'put first four characters of selected line in active cell
    ActiveCell.Value = Left(ComboBox1.Text, 4)
End If
'move focus off combo box
ActiveCell.Select
'set combo box to the row 1 message
ComboBox1.ListIndex = 0
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
MsgBox "Error"
're-enable events
Application.EnableEvents = True
End Sub

I added a pop-up message to show if the code encounters an error.

I suggest you paste the new code in.
Then ensure that the Properties box ListFillRange is Cat!B2:B10
Save the workbook, close it, close Excel then reopen it.

This code works fine in my Workbook, so I am missing some difference that you have.

Regards


Report •

#17
June 22, 2010 at 09:20:18
Sorry i does not work and now it always paste the 4 fisrt letter of my title in the active cell not the information i want
my previous code works better but i have no title in the combobox.Harrrrrr.....
On Error GoTo ErrHnd
'disable events
Application.EnableEvents = False
'test if combo box selection is not the row 1 message
If ComboBox1.Value <> Worksheets("Cat").Range("B2").Value Then
'put first four characters of selected line in active cell
ActiveCell.Value = Left(ComboBox1.Text, 4)
End If
'move focus off combo box
ActiveCell.Select
'set combo box to the row 1 message
ComboBox1.ListIndex = 0
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
MsgBox "Error"
're-enable events
Application.EnableEvents = True
End Sub
Does not work for me!!!!!!
Sorry if i im not getting this!!!!!


Report •

#18
June 22, 2010 at 11:58:09
Hi,

From the Ribbon Select developer and select Design Mode
Click on your combo box
Look in the cell address/name box above column A and to the left of the formula bar.

What name do you see ?

If it is not ComboBox1, you need to change the code to match.
If the name is ComboBox3, then these three lines change like this:

If ComboBox3.Value <> Worksheets("Cat").Range("B2").Value Then
...
ActiveCell.Value = Left(ComboBox3.Text, 4)
...
ComboBox3.ListIndex = 0

Regards


Report •

#19
June 22, 2010 at 12:38:06
No it is combobox1 that i see,plus in this worksheet i only have one combobox. I am leaving pretty soon and will be back only next tuesday. I will try something else next week if you have any other ideas,could it be in the properties of my combobox that i have something not right !!! So have a good one!!!!!
And thank you for your time.

Report •

Ask Question