Disable AutoComplete For Specific Cell

  • Hi all,
    I would like to know if anyone knows how to disable autocomplete for a specific cell. There is only one cell (A11) that I don't want to use auto complete but I want every other cell to be able to still autocomplete. I think it may have something to do with matchentry being set to 2, but I'm not sure how to do this. If you could provide a code example I would greatly appreciate it.

    Thanks[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I don't know what sintax I need to use. I've tried this line of code but it doesn't work

    A11.MatchEntry = fmMatchEntryNone

    Please help.


  • Re: Disable Autocomplete For A Specific Cell

    Maybe an event macro. Right click the sheet tab > select view code > paste in the below

    VBA Noob

  • Re: Disable Autocomplete For A Specific Cell

    VBA noob,
    Thanks. I thought it was going to work, but it doesn't see to work. Can this be placed just on my sheet1 code or do I have to make a module?

  • Re: Disable Autocomplete For A Specific Cell

    VB Noob,
    I'm going to attach my spreadsheet. The code you gave me above works but not until something is entered into cell A35 (sorry, it was A11 but not it is A35). You may first want to protect the sheet so you can see what the problem is. The user needs to first enter a date, then hit tab. This takes the user to cell A35. If the user enters "s" the word Status will try to autocomplete. I need the spreadsheet to not do that. If I then enter just an "S" into cell A35 and hit tab or enter, the autocomplete will then be deactivated. What I need to have happen is for autocomplete to be deactivated the second the cell A35 is highlighted and before anything is entered. I'm sure that I'm just doing something wrong, but I messed with it quite awhile now and I can't seem to get it to work. I can change the code to use
    N1 instead of A35 and then after the user enters the date and hits tab or enter it will turn off the autocomplete and then "S" can be entered into cell A35 without problem. This is fine for sheet1, but Sheet2 starts the user right on A35. Does this make sense? Sorry if I'm being confusing.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Sorry, VB Noob, I didn't realize that my file didn't attach. I guess it is too big and so I won't be able to attach it. Do you understand what I was trying to say above? I'm sure there is a way to make VB make the change to the autocomplete before anything is added to the cell, but I don't know how to do it. My file is 78Kb so I can't attach it. Sorry.

    Thanks for all the help,

  • Re: Disable Autocomplete For A Specific Cell

    I would use data validation with an error note and just put what you want the user to put in the cell

    "The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

  • Re: Disable Autocomplete For A Specific Cell

    I actually already have validation set up so that the user can only enter "a","c","n", or "s", but above A35 there is a vertical text in the column "A" that says "status." So what ends up happening is that when the user enters an "s" it tries to auto complete with "status." I need autocomplete to be on in every other cell but this one. I wish I could upload my spreadsheet but it is too big (78Kb) and I guess we are only allowed 40Kb or something like that.

    Thanks anyway.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I've also tried this. What does VB call the cell that you just tabbed to but you haven't done anything with yet? Is that considered the active cell? For instance I type something in A34 and hit enter. The cell that is highlighted then becomes A35. What is that cell considered at the very moment it becomes highlighted and before I do anything with it?

    With Application
            If ActiveCell.Address = "$A$35" Then
                .EnableAutoComplete = False
                .EnableAutoComplete = True
            End If
        End With
  • Re: Disable Autocomplete For A Specific Cell

    You must not have the error message set. If it is set then only the letters can be selected, should not autocomplete because "status" would not be a valid entry

    "The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

  • Re: Disable Autocomplete For A Specific Cell

    I actually do have an error set already. The user can only imput "s" but I just want to prevent the autocomplete from coming up so the user doesn't have to take an extra step to hit backspace to get rid of the unwanted "astus." I'm making this for a health care team and I want it to not have little problems like this. Thanks for your help.

    I think the problem is that activecell only references the cell once you try to do something with it. I tried target earlier and that didn't work either. What in the world is the cell considered before anything is done to it?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Here is an bit of my workbook that I'm having trouble with. The first sheet seems to work fine now. The problem I'm still having is that when you go to the second sheet and the cursor is on the column in question and you type an "s" it tries to autocomplete. I think this is because the cell isn't activated yet or something. I hope this helps to make what I'm saying a little more clear.

    Thanks so much for the patience. I'm just learning here.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!