We help IT Professionals succeed at work.

Excel ActiveX ComboBox enters a 0 if a list item isn't selected

189 Views
Last Modified: 2017-04-10
I'm new to this site and somewhat new to Excel VBA and comboboxes.  I've hit a wall as far as finding a solution to this problem.  I've had to adapt code from a few different sites (this site, Contextures, OzGrid, Mr. Excel, etc.) to get this worksheet to perform as I've been requested so far.  I've tried to build a worksheet that has several comboboxes based on data validation lists (as well as 1 dependent list).  I have the comboboxes working as a SelectionChange event.  When the user begins data entry on the sheet, the hope is that he/she can simply Tab or Enter through the comboboxes and cells.  So I have to have the comboboxes appear and display some rows of the list when the cell is selected by keyboard or mouse.  When the user selects another cell that has a combobox the same thing happens.  The problem I've now encountered is that if a user selects a cell with a combobox  and then selects another cell without selecting an entry from the previously selected combobox before doing so, a 0 is entered into the cell and is added to the list the combobox is based on (the sheet was set up to allow new entries into the data validation lists--and that's a feature that is wanted for some column cells).  I apologize for the novel-length question, but I'm just trying to give as much background as possible.  Here is the code I have for the worksheet (this includes all the code because I'm not sure if I have conflicting code or if it's isolated to the combobox code):
-------------------------------------------------------
Option Explicit
---------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 3 Or Target.Column = 4 Then
            My_Sub_B Target
            Else
            My_Sub_A Target
            End If
End Sub
---------------------------------------------------------
Public Sub My_Sub_A(ByVal Target As Range)
 On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range

If Target.Count > 1 Then Exit Sub
Set ws = Worksheets("Lists")
 
If Target.Row > 1 Then
  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub
 
  If Intersect(Target, rngDV) Is Nothing Then Exit Sub
   
  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
  On Error Resume Next
  Set rng = ws.Range(str)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub
 
  If Application.WorksheetFunction _
    .CountIf(rng, Target.Value) Then
    Exit Sub
  Else
    i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
    ws.Cells(i, rng.Column).Value = Target.Value
    rng.Sort Key1:=ws.Cells(1, rng.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
  End If

End If
End Sub
----------------------------------------------------
Public Sub My_Sub_B(ByVal Target As Range)
 
If Target.Column = 3 Then
  On Error Resume Next
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
 
End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
-----------------------------------------------
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)

'move to next cell on Enter and Tab
Dim varVal As Variant
On Error Resume Next

varVal = --ActiveCell.Value
If IsEmpty(varVal) Then
  varVal = ActiveCell.Value
End If

Select Case KeyCode
  Case 9  'tab
    ActiveCell.Value = varVal
    ActiveCell.Offset(0, 1).Activate
  Case 13 'enter
    ActiveCell.Value = varVal
    ActiveCell.Offset(1, 0).Activate
  Case Else
    'do nothing
End Select

End Sub
------------------------------------------------------------
Private Sub TempCombo_LostFocus()
  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub
----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Dim lSplit As Long
Set ws = ActiveSheet
Set wsList = Sheets("Lists")
Set cboTemp = ws.OLEObjects("TempCombo")
 
If Target.Count > 1 Then GoTo errHandler
 
  On Error Resume Next
    With cboTemp
    .Visible = False
   End With

On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
       
If Left(str, 4) = "INDI" Then
lSplit = InStr(1, str, "(")
str = Right(str, Len(str) - lSplit)
str = Left(str, Len(str) - 1)
str = Range(str).Value
End If
   
With cboTemp
Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address

End With
cboTemp.Activate
Me.TempCombo.DropDown               'show combobox automatically
End If
 
errHandler:
 Application.ScreenUpdating = True
 Application.EnableEvents = True
 
End Sub
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
A sample workbook would help us solve the problem, but if you can disable the second combobox until the ListIndex of the first one is > -1 then that should fix the problem.

Author

Commented:
I've attached a sample workbook that is formatted the same, includes the same code, etc.  So as the comboboxes are generically named "TempCombo" and appear only where a validation list is, I'm not sure how to change the combobox properties of any of the individual comboboxes.  Something else I noticed is that only the first combobox will display the list it's based on whereas I need every combobox to display their respective lists.  Any help on this is greatly appreciated!
Sample-Work.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How do I reproduce the problem(s)?

Author

Commented:
On the sample sheet, select cell A2.  The combobox list should appear.  If you press Tab to go to the next cell.  You'll see a zero entered into A2, and in B2 the combobox will appear but not the list.  If you press Tab, or use the mouse to select a different cell without selecting an entry from the dropdown list, a zero will be entered into the cell (again).  This zero is also added to the list the combobox is pulling from on the Lists sheet.  I can't have a zero in the cell if a user decides to enter data in a different cell.  Also, I need the combobox to expand when the cell is selected.  Does that make sense?
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Martin, I read your article and played around with the workbook you sent.  The new code seems more restrictive for what I need but I think the code behind it is a step closer.  I think I am responsible for that because I tried to give a quick description on what I needed without giving much detail to what I had done so far.  I was trying to avoid writing an even longer comment.  I think it would've made more sense if I'd been able to upload my actual file, but I am prohibited from doing that.  I'll try to explain here, though, so forgive me the essay that this will become!

The intent of the file is to record data of an inventory nature that includes cost.  This isn't a normal inventory, though, it's called a replacement reserve.  The spreadsheet will come to the user with some variables built-in, such as choices for locations, yes/no fields, categories that the item being recorded is grouped into, along with a description of the category (a sub-category), what the item is, whether the item is inside or outside, the capacity and recordable units of the item, the expected life of the item, and the cost of the item.

I built the file so that either I or someone else could provide some of the choices by recording those in the Lists sheet prior to going to a user.  Some of those lists could be added to by the user (such as entering a new item) by typing the data in the related field (and then it is automatically added to the list).  So I used data validation (based on the Contextures instruction) for the fields based on any of the lists.  I made the Descriptions field a dependent list based on selection made in the Category field.  This worked great.  I thought it was finished, but I've been urged to go further.  The worksheet needs to be more user-friendly with these things in mind:

the list font needs to be bigger
there needs to be auto-suggestion/complete for the lists
the user needs to be able to tab (or press Enter or use an arrow key, etc.) from field to field (as they can if the lists are regular data validation)
the user must be allowed to move out of a field without selecting an entry (without making the combobox enter 0 for no entry)
some fields must allow or restrict new entries as it did with the normal data validation lists[/list]

So I started working on it and making the data validation lists into comboboxes is the only thing that met those needs.  That was two weeks ago.  I've had many late nights wrestling with this.  When I finally got it working, I hit this issue--where a 0 is entered into the list if you select the combobox and then move to a different cell without selecting an entry from the list.  All I really need to figure out is how to get the combobox to accept a non-entry.  If I could figure out how to get the combobox to see that is there is not a selected entry then it accepts the default "0" entry but does not record that entry into the list as an item.  It seems like it would be as easy as an IF-THEN statement, but I tried something to that nature and it didn't change anything.

I attached the sample workbook again but this time I placed comments in the column headers to explain what the fields need to do.  Again, I am sorry for the novel I wrote here, but I think it helps better explain what it is I am trying to do.  Also, thank you so much for helping me!  I only hope I didn't confuse you (any more)!
Sample-Work-wirh-comments.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I see that in the workbook you just attached that you went back to your code. That's fine but there are problems with your code that mine overcomes and so I'd rather use my code as a base and have you tell me what was wrong with it. Or is that what you did when you said the following?
the list font needs to be bigger
there needs to be auto-suggestion/complete for the lists
the user needs to be able to tab (or press Enter or use an arrow key, etc.) from field to field (as they can if the lists are regular data validation)
the user must be allowed to move out of a field without selecting an entry (without making the combobox enter 0 for no entry)
some fields must allow or restrict new entries as it did with the normal data validation lists

Author

Commented:
Sorry!  I had a few workbooks open at the same time and used the wrong one!  Please use your code.  What you refer to in quotes is what I need the sheet to do--some of that stuff was solved like the font size and auto-complete just by using comboboxes.  I understand that my code had problems (no doubts there), but the sheet was working except for this whole 0 entry thing the combobox does.  I did notice that with your code the dependent combobox wasn't working correctly, but that was on me failing to tell you that it was supposed to be a dependent list.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
  1. Do you need the font size for the drop-downs to be bigger? If so what size?
  2. For the drop-downs that allow new entries, should the new entry also be added to the Named Range for the given list?

Author

Commented:
1. No--the font size is fine as it is

2. Yes
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Just an FYI. I'm still working on this but I'm currently "stuck" trying to figure out why those dropdown lists that should require that an entry be from the list don't trigger the data validation error message if an invalid value is entered.

Author

Commented:
I cannot thank you enough for helping me out Martin.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome. Try the attached workbook and let me know what I've missed or if something isn't correct.

In my article you may have noticed a mention of the difficulties of maintaining Data validation when floating a control over a cell. In any case I added a Sub called "Maintenance" to Module1 which will toggle a flag to allow/disallow maintenance of the Data Validation. In other words if you want to change the existing data validation for a cell or cells, run the "Maintenance" sub and run it again when you're done.
29013992a.xlsm

Author

Commented:
When I opened it an Error 91 dialog box opened.  Also, when I selected an entry from the Descriptions field (so awesome you got the dependent list working!), an Invalid Entry popped up.  I've attached pix of those.  I won't have time to get to the code for a little bit to see if I can find the issue.  Other than those, it's great!
Error-91.png
Invalid-Entry.png

Author

Commented:
I think the Error 91 dialog box was a fluke error because it hasn't occurred again since that first time I opened the file.  I started going through the code step by step and discovered that this block of code seems to be causing the invalid entry loop:

Case Is < 16 ' These columns don't allow new entries. 16 is "P"
                If rngFound Is Nothing Then
                    If Not mrngPrevCell Is Nothing Then
                        ' Fake the data validation error message
                        With mrngPrevCell.Validation
                           MsgBox .ErrorMessage, vbOKOnly, .ErrorTitle
                      End With
                        mrngPrevCell.Select
                       'ShowAutocomplete mrngPrevCell
                        Application.EnableEvents = True
                        Exit Sub
                    End If
                End If

Open in new window


I commented the code out and everything seems to be working.  Before doing that, I went through and removed the Stop warning that I previously had enabled for the data validation lists because I noticed the dialog box message matched what I had written into the Stop Warning.  It seems to be working OK without the above code, although the case select code only has 1 case now.  I'm trying to figure out how you enabled the combo boxes to allow new entries that would add to the lists.  I think the Loc column fields are not adding the entries to the list for some reason, and that's something I need it to do.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this version which I think fixes the problems you mentioned. One problem that it doesn't fix is that when Category "D" is selected, the list in Descriptions is truncated. I'll work on that. Note that I added a "TestingReset" sub which you can execute directly from the code or from the Immediate Window to reset some things when there's an error and you have to stop the execution.
29013873b.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Ignore the Category "D" comment above. I confused it with the list for LifeTime.

Author

Commented:
The file you sent is empty except for some numbering down the first column and some arrows drawn above & below a yellow highlighted box...
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Oh, sorry. I attached a workbook from a different thread.
29013992b.xlsm

Author

Commented:
That's OK.  This is awesome!  I really need to study your code because I'm still uneducated.  I do want to ask how I can, if I need to, re-code a list to either allow or restrict new entries?  I tried removing the column number from the Case range that allows new entries and although it didn't allow the entry (with the invalid entry error), if I tried to do it again the code shorted out when it hit this line:

MsgBox .ErrorMessage, vbOKOnly, .ErrorTitle

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I think I understand the arrays.  Basically, the varrNRCols array determines the range of the list entries for whichever column you're using the combobox in.  I think the 2nd array (or first since it comes before varrNRCols) is the column range where the combobox is linked, is that correct?

Also, I tried removing column 11 from line 10, and when I attemped a new entry a second time, that's when the code broke and when I clicked Debug it highlighted line 33--the message box line code.... I know I tried something that maybe wouldn't actually occur, but with the way this project has been going, I wouldn't be surprised if it did.  Is there a way I can prevent that?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
the varrNRCols array determines the range of the list entries for whichever column you're using the combobox in.
Yes, column1 on the sample sheet uses "E" on the Lists sheet for Data Validation, column 2 uses "J",  column 3 uses "A", and column 4 doesn't have any data validation.
I think the 2nd array...
There's only one array in that code.
Also, I tried removing column 11 from line 10, and when I attemped a new entry a second time, that's when the code broke...
I'll test that out tomorrow.

Author

Commented:
Hi Martin.  I was recreating the anomaly I mentioned where I tried entering a new value into column 11 more than once (after removing that column from the fields that allow new entries) and noticed some code that had been commented out around the message box code.  I turned the code on from lines 4, 5, 18, & 19, and that seems to fix the issue.  I'm not sure why.  I know Application.EnableEvents is used to avoid infinite loops and obviously Exit Sub quits the code.  I don't understand how it was caught in a loop, I guess.  Also, I noticed some other code that's been turned off -- line 15 ( 'mrngPrevCell.Select).  Could that be related to why once I select a correct entry (after trying a new entry) and pressing Tab it skips a couple columns instead of moving just to the next cell?

 
 'mrngPrevCell.Select
                        ShowAutocomplete mrngPrevCell
                        bAutoCompleteDone = True
'                        Application.EnableEvents = True
'                        Exit Sub
                    End If
                End If
            Case Is < 16 ' These columns don't allow new entries. 16 is "P"
                If rngFound Is Nothing Then
                    If Not mrngPrevCell Is Nothing Then
                        ' Fake the data validation error message
                        With mrngPrevCell.Validation
                            MsgBox .ErrorMessage, vbOKOnly, .ErrorTitle
                        End With
                        'mrngPrevCell.Select
                        ShowAutocomplete mrngPrevCell
                        bAutoCompleteDone = True
'                        Application.EnableEvents = True
'                        Exit Sub

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Re lines 4, 5, 18 and 19. At first I had Exit Sub in those two places but I quickly found that I also needed to turn Application.EnableEvents back on (it gets turned off at the start of the Sub) so I added that in both places. I then I needed to add the If Target.Row <= WorksheetFunction code that you'll find at the bottom of the Sub and always have it executed, so instead of duplicating that code under 4 and 18, I added the bAutoCompleteDone Boolean flag, removed 4, 5, 18 and 19 and added an If Not bAutoCompleteDone Then further down. The net result of all of that is that is that the code should run smoothly without Exit Sub from those sections without having ShowAutocomplete run twice. Long story short, I think the problem you (and now I) are seeing is caused by the handling of TempCombo and I'm looking at that now.

BTW if in the real world the "sample" sheet has a different name, please let me know what it is,

Author

Commented:
Is there a way to private message you?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Click my picture in this post and select "Message".
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this. I had to make another small change but you were essentially right about lines 4, 5, 18 and 19.

I've meant to ask you. Do you know why I get messages like this when I try to reopen your workbooks?
????29013992c.xlsm

Author

Commented:
Yeah, sorry about that.  I keep forgetting to remove that protection.  When you save a file by using Save As, you cick the Tools button next to the Save button, then click General Options.  Once there, you can click a checkbox to enable a Read-Only recommendation.

Author

Commented:
OK, I think this is done.  I also think you are a freakin' genius!!!  Can you remind me what the Testing Reset does?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I use Excel 2010 and, believe it or not, Windows XP and I don't see what you describe. Could you do me a favor and remove the read-only restriction and reattach my 29013992c.xlsm workbook?

Author

Commented:
I attached some screen shots of where it is in Excel 2016.  I don't think it's different, but could be.  I re-saved it and attached the file, also.
Recommend-ReadOnly.png
Protection-General-Options.png
29013992c.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thanks for the pictures and the wb. 2010 doesn't show me anything like that but maybe I just need to do some research.

When you are satisfied with the wb, please don't forget to close this question. Also If there's anything that you still don't understand, please let me know.

Author

Commented:
You helped me a great deal with this Martin.  I probably would've never had this working as well as it does now.  My code was all bits and pieces pasted together and there is little doubt that had I got it to work somehow, I would've been inundated with errors and crashes.  So thank you very much.  This is also the very first time I've ever reached out for help, and I was very uncertain about doing it.  You made it a great experience.  I appreciate your patience and all the time you spent on this.  I know for a fact I won't hesitate to ask you or anyone else here for help in the future.  Thank you so much!

Author

Commented:
I was cautious about going online for help, but Martin really helped me solve my problems in Excel.  I'm sure had I received help from any of the other experts it would have gone well, but I don't know that it would've been as great.  Because of Martin, I experienced a pleasant, patient, and encouraging answer!  No doubt I will seek him out for any future questions!!!
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thank you for the kind words. I'm also glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016

Author

Commented:
Definitely going to check those out!

Author

Commented:
Marty,

I've run into an issue in adapting the code into the second template I mentioned.  In that template, I have 8 column lists that are "descriptions" that are displayed based on what is chosen in the "category" selection; however, 4 of those column headings contain names that are more than 1 word, like "A and B," or "AB and CD."  I tried inserting a substitution line of code into the ShowAutoComplete module, but it didn't do anything.  I also tried inserting a Split code into the same module that would address the validation formula that has indirect in the code.  That I took from Contextures, but all that did was stop everything from working.  May I have your help one more time concerning this monster?

Thanks
Randy
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sure, but please provide a sample workbook that shows the problematic column layout.

Author

Commented:
Just sent it your way via pm.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.