Using a combo box to enter a cell value (2000)

Topic

New Reply

I have a worksheet in which the user needs to enter numeric values in a column. The numeric values represent a certain type of something. EX: 0 would equal Credit and 1 would equal debit. What I would like is to use a combo box in the cell that lists Credit and Debit as the choices but enters in that cells value the numeric equivalent.

Reply | Quote

Replies

If you have a ComboBox named ComboBox1 (from the Control Toolbox) then the following code might help

Private Sub ComboBox1_Change()
Range(“A1”).Value = Me.ComboBox1.ListIndex
End Sub

Where A1 is the cell you want to fill. If your ComboBox has a LinkedCell value, remove it.

Just right click on the appropriate Sheet tab, select View Code, and enter the above code in the codepane.

Andrew C

Reply | Quote

Thanks for the info. Now another Q. This is the first time I’ve tried using a combobox in Excel and I can’t figure what property to set for the values in the combobox. In VB this would be the rowsourse.

Also. If I want this same type of control to appear over ever row in this column when the user selects a cell in that column how would I do so?

Reply | Quote

To set the source for a worksheet combobox, you use the ListFillRange. To access the properties of the selected control click on the second icon from the left on the Control Toolbox. The first icon is a toggle (design/run) which when depressed allows you set the properties etc of the controls.

I think you might be better off using the Data Validation function rather than using a ComboBox for you current requirements. See attached workbook with the Data Validation coupled with some Worksheet_Change event code.

Andrew C

Reply | Quote

One small suggestion. If you change the Change Event routine as below, you will save a little overhead by only executing the routine once for each change instead of twice.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Set oCell = Target.Cells(1)
    Application.EnableEvents = False
    If Not Intersect(oCell, Range("D:D")) Is Nothing Then
        Select Case oCell.Value
            Case Is = "Credit"
                oCell = 0
            Case Is = "Debit"
                oCell = 1
            End Select
    End If
    Application.EnableEvents = True
End Sub

Reply | Quote

Legare,

You are correct (as ever) and thank you. I forever forget to toggle events on off and on in these circumstances.

Andrew

Reply | Quote

Andrew,

I was interested in this problem so I downloaded your sample spreadsheet. 2 questions:

1. Did you change your attachment to reflect what Legare mentioned about toggling the events? I couldn’t find any difference between the code in your workbook (both the version in the textbox on the worksheet and in the VBE) and Legare’s posted code.

2. It didn’t run in Excel 97. When I chose an item from the listbox, I got that value and not 0 or 1. The code looks pretty straightforward except for one thing: why do you set EnableEvents=False? Doesn’t this stop the sub from running due to a change in the workbook (like reselecting a cell in D1 and changing the value from the dropdown)? Even if I commented that line out, I still got the same result (the word, not the number).

TIA

Fred

Reply | Quote

Andrew,

I think I answered part of my own question 2. I guess the EnableEvents=False prevents the worksheet_change from running a 2nd time (Legare’s point) when the code executes one of the oCell= statements. However, the code still doesn’t run in Excel 97 even tho 97 does seem to support a worksheet_change event. I put a msgbox in your sub right after the Dim stmt and even set a breakpoint. I went back and altered one of the cells and nothing seemed to be triggered in the code to stop (no break, no msgbox).

Fred

Reply | Quote

Fred,

Would it be possible that on your System, EnableEvents are set to False. In the immediate window type Application.EnableEvents = True hit return and try again.

I did indeed edit the attachment to incorporate Legare’s suggestion, as I figured that ecarden had not yet downloaded it.

Andrew

Reply | Quote

Fred,

You are correct as I just tested it in XL97. It seems that changes made using DataValidation prompts do not trigger the Sheet_Change event. Typing in Credit or Debit in the normal way does trigger the code. Perhaps somebody has come across a work around for this problem.

Andrew

Reply | Quote

Andrew,

I think we have the whole story now.

EnableEvents was already true per the immediate window.

Seems DV does NOT trigger Worksheet_Change, as you guessed.

Moreover, if you type the item, you have to make sure you obey the case of the letter in the Select Case statement.

There is also a Worksheet_SelectionChange event. I observed the following behvior with your same code in that event sub:
-click on a cell in col D
-the event sub runs but nothing happens since the cell is empty (assume it is)
– now click the drop-down and make your selection
– now click on another cell
– click back on the cell you just were working in
Result is that your choice of “Credit” or “Debit” from the 3rd dash is converted to 0 or 1. So if there was a way to know what cell you came from, which does NOT appear to be an argument in this event, you’d be able to have the workaround. Since this doesn’t seem possible, I give up.

Fred

Reply | Quote