How to turn Automatic Completion on/off in Excel 2007

**jarhtmd** · 02-11-2018, 03:31 AM

My worksheets don't auto complete as I type text into cells. I have not (knowingly) turned that off. It seems to be the same for all workbooks.

Googling yields . . . "Click the Microsoft Office Button, and then click Excel Options".

I don't see Excel Options. I only see New, Open, Save, SaveAs, Print, Prepare, Send, Publish, Close

Right clicking gives toolbar & ribbon choices.

I'm using Windows 10 and Excel 2007.

Maybe I've gone brain-dead . . . I don't understand the "Code Tags" instructions above.

**DocAElstein** · 02-11-2018, 11:42 PM

Hello jarhtmd

Code Tags:
If you want to post a code in a Forum such as excelfox, then it is usual to use code tags.

That means that if this is your code:

Sub
‘ Anyfink
End Sub

Then instead of posting that, you post this

[code]
Sub
‘ Anyfink
End Sub
[/code]

If you do that, then in the final posted post, you will actually see the code inside a code window like this

Code:

Sub
‘ Anyfink
End Sub

The code tags tell the forum software to put your code in a code window.
The advantage of the code window is that all indent formatting is preserved, and you get scroll bars similar to what you have in the VB Editor code window.

If you want to practice doing that sort of thing, then you should start a Thread here
http://www.excelfox.com/forum/forumd...p/17-Test-Area
Give the thread a title such as “Just Testing Posting techniques. No Reply needed”

_.________________

Excel Options

This is how I get that for Excel 2007

_1 Click the Microsoft Office Button,
_2 Click Excel Options
XL2007MicrosoftButton_1Options_2.JPG https://imgur.com/2kY2rVx
XL2007MicrosoftButton_1Options_2.JPG

This is how I get that for Excel 2010
_1 Click Home Tab
_2 Click Excel Options
XL2010Home_1Options_2.JPG https://imgur.com/uyauCRP
XL2010Home_1Options_2.JPG

_.________________________________

This is a simple code I often use to “turn everything on”.
I am not too familiar with what auto complete actually means. I am not sure if that is “turned on” with Application.Calculation = xlCalculationAutomatic.

Code:

Sub Oops()
 Let Application.ScreenUpdating = True
 Let Application.Calculation = xlCalculationAutomatic
 Let Application.EnableEvents = True
 Let Application.DisplayAlerts = True
End Sub

If you need help on how to run that code then let me know

Alan

**jarhtmd** · 02-12-2018, 01:15 AM

Thanks, Alan. I was looking right past it. As my Mom used to say, "If it had been a snake it would have bitten me". Thanks also for explaining code tags.

Using the (newly found) Excel options, I see that "Enable AutoComplete for cell values" is checked, but still isn't working.

Auto-complete seems to have limited capabilities. It appears (to me) that the number of cells checked is limited and all must be adjacent. Is that correct?

My worksheet has data in colA, but only in alternating rows. Is there a way to auto-complete based on all the rows above (no matter how many) and even if some cells are empty?

**DocAElstein** · 02-12-2018, 01:53 AM

Hi jarhtmd ,
Thanks for the feedback
Sorry I have no experience with auto complete ( to be honest I have no idea what it is .. lol.. ).
I mostly use VBA for anything I do. I learnt to run ( badly ) before I learnt to walk, and missed out on a lot of spreadsheet things
Hope someone else might pop by that can help further on that one.
Alan