Auto-fill information while typing

[Oriana]

Is there a way to get cells to auto-fill while a person is typing? For
example, if I have a cell that has a drop-down list with the options:

Apple
Artichoke
Banana
Cantaloupe
Cucumber
Celery

If I start to type "C" into the cell, I want it to realize, "Hey, there are
three C-options in this list. Perhaps I should choose one of those." Or, in
this case, since there are more than one, then if a person were to type "Ca,"
I would want Excel to realize they are typing Cantaloupe and fill in the rest
of the word for them.

I have created a huge spreadsheet template for a client and she wants to be
able to quickly enter information without having to type the whole word (some
of these cells contain options with 8-10 words per selection) and without
having to resort to using the drop-down menus constantly.

Also, in cases where I have a list like:

Yes
No
Maybe
N/A

....when she starts to type in "Yes," the cell does not pick that up. If she
types in "yes" with the lower-case Y, Excel still accepts it and will leave
it lower-case, even though the Data Validation cell has a capital Y. Is
there a way to fix that?

Thank you!

 

[Oriana]

Also, I forgot to mention that even with AutoComplete on, this is still
happening to me. She is working in 2003 and I'm working in 2007 with the
Compatibility Mode on.

 

[Gord Dibben]

DV dropdowns do not have the autocomplete feature.

You would have to use a combobox for that.

See Debra Dalgeish's site for help

For the combobox solution see

http://www.contextures.on.ca/xlDataVal10.html


Gord Dibben MS Excel MVP

 

[Oriana]

Are there instructions on how to do this in 2007? I cannot find half of the
toolbars in the instructions. I was able to sort've locate the combo box,
but I can't find the properties option.

 

[Oriana]

Well, I found the properties, but it doesn't offer font changes, I can't name
it, etc. The ListRows and all that stuff isn't available, either. Perhaps
they have disabled this for 2007?

 

[Oriana]

I'm sorry for all of the messages.

I found that I have to use the ActiveX combo box rather than the form
control ones.

However, I have done everything those instructions told me to and I don't
see how that helped. The combo box just sits there and doesn't seem to serve
a purpose? Whenever I type in a cell that has data validation in it, it
still doesn't autocomplete while I type.

 

[Gord Dibben]

Works fine when I follow the steps on Debra's site.

Did you copy all the worksheet code to the sheet module?

Have you exited Design Mode?

Have you double-clicked on the the DV dropdown cell?

Did you download the sample file to have a look at a working model?


Gord

 

[Oriana]

I messed around with the sample file on that website and I can see how it
works a little better. However, whenever I try to type in one of the combo
boxes, I get an error in the code. For example, I tried typing "B" to start
typing "Behavior" and it popped up the macro screen with the error: "Compile
error: Only comments may appear after End Sub, End Fuction, or End Property"
I click OK and it then highlights the following near the very end of the code:

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

 

[Gord Dibben]

You should have copied all the code including this last bit.

Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub

Sounds to me like you have missed some of the above or entered some text below
the End Sub line.


Gord

 

[Oriana]

Aha! I think I found the problem. There was an extra End Sub and something
else extra at the beginning. According to the site, I was instructed to just
insert the code where the cursor was. I didn't realize I also needed to erase
everything that was already preset. I'm not very familiar with macro-stuff.

Though, I have another problem now.

Column B is a place to enter a date, Column C is a place to enter a name,
and then Column D is a validated cell with a list of the course names I
provided earlier. When I'm tabbing through from B, to C, to D, it does not
pick up on the combo box. I have to physically double-click on the cell for
it to come up. That kinda defeated the purpose of doing this whole thing. Is
that just how a combo box works?

 

[Gord Dibben]

With Debra's code that's how it works.

Try this amended code for Tabbing to cells and having the ComboBox available as
get to the cell with the DV list/combobox.

Delete all current code in the sheet module then copy/paste everything down to
but not including Gord

Option Explicit
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
'Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.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)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub


Gord

 

[Oriana]

I think that worked! You are my hero! Thank you so much!! My client will
be super-happy.

 

[Oriana]

I spoke too soon. >.<

My client is running Microsoft 2002. There is an area in the spreadsheet
(from columns Y to Z) where Excel is shutting down when she tabs over. It is
tabbing from a cell that contains the combo box drop down to a completely
empty cell (no formulas, etc.). I'm hoping maybe if I take out the column
with the blank cells (I was using them as section separators), that it might
fix the problem. I hate for her to be entering a bunch of data, though, and
suddenly have Excel shut down and lose it all. Any idea why 2002 might be
doing this and if there's a fix?

 

[Oriana]

I wish this thing would let me edit my previous posts.

It turns out she's not running 2002, but *2000.* And, the error is, indeed,
whenever she tabs from a combo box cell over to a completely blank cell. Is
there a fix I could insert into the VB code?

 

[Gord Dibben]

I cannot replicate that problem with 2003 version and the code I posted.

Could be something in the code that is not valid in 2000 but I am not familiar
enough to say.

Hang in there.........someone more knowledgeable may respond.


Gord