Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

    <pre>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")) 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
    </pre>

    Legare Coleman

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    Legare,

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

    Andrew

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

    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

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

    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

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

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

    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

Posting Permissions

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