I've been working on this problem for some time now but to no avail. I have two sheets. The code for one sheet is the same as the code for the second sheet. In column A I have a vertical word "Status." Below the word "Status" (in cell A35) I have a blank cell that is set up to only allow the letters a, c, n, or s. Since there is a word that starts with "S" above this (the word Status) it wants to autocomple but I just want to simple enter "S" and not have the autocomplete come up. With this code I think I've solved the problem on the first sheet:
As stated above I have two sheets. Both sheets are identical. The second sheet has the same code and the sheet is set up so that when you change from sheet1 to sheet two the selected cell is A35. If you switch sheets and then type "S" the autocomplete displays "status." As I stated above, the second sheet has the same code as above yet the autocomplete is still active right after switching sheets.
I know it is a little comfusing, but hopefully someone knows what I need to do to get the autocomplete to be deactivated in both sheets in cell A35.
Thanks a million
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
With Application
If ActiveCell.Address = "$A$35" Then
.EnableAutoComplete = False
Else
.EnableAutoComplete = True
End If
End With
End Sub
I know it is a little comfusing, but hopefully someone knows what I need to do to get the autocomplete to be deactivated in both sheets in cell A35.
Thanks a million