How do I activate the selected cell when switching sheets?

Huhenyo

Board Regular
Joined
Jun 11, 2008
Messages
138
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:
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
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
This should work for you, it goes in the Thisworkbook module, i have named 2 worksheets as you will see just change these for the actual sheet names that you want your code to work on!
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> "Sheet1" Or ActiveSheet.Name <> "Sheet2" Then Exit Sub
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
 
Upvote 0
First of all, thanks for the help. I deleted the code that I had for the sheets and just add the code that you suggested to the ThisWorkbook. Now there isn't any change in the autocomplete on either sheet. Any ideas what I need change?

Could you explain to me what these two lines mean in layman's terms please
Code:
If ActiveSheet.Name <> "Sheet1" Or ActiveSheet.Name <> "Sheet2" Then Exit Sub
If Target.Count > 1 Then Exit Sub
I'm not familiar with the <>. My guess is that the lines mean if the active sheet is not named sheet1 or sheet2 then exit the sub. The second line I'm not sure what it means. I just had it in my code because that is what someone suggested.


Thanks
 
Upvote 0
First of all, thanks for the help. I deleted the code that I had for the sheets and just add the code that you suggested to the ThisWorkbook. Now there isn't any change in the autocomplete on either sheet. Any ideas what I need change?

Could you explain to me what these two lines mean in layman's terms please
Code:
If ActiveSheet.Name <> "Sheet1" Or ActiveSheet.Name <> "Sheet2" Then Exit Sub
If Target.Count > 1 Then Exit Sub
I'm not familiar with the <>. My guess is that the lines mean if the active sheet is not named sheet1 or sheet2 then exit the sub. The second line I'm not sure what it means. I just had it in my code because that is what someone suggested.


Thanks
You are right on the first line, the second just says that if the target (selected cell) is more than one cell then do nothing! but of course nothing will happen if you don't have the sheet names correct!
 
Upvote 0
What sheet name do I need to use? In VB on the upper left where it shows all your different sheets, there is a name on the left which is usually sheet1, sheet2, sheet3, etc. and on the right the name that you gave the sheet. Which one do I need to use in the example above? Thanks

Nick
 
Upvote 0
Assuming you have code to change the selection when you switch sheets, then you can use the Worksheet_SelectionChange events in both sheets:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    With Application
        If Target.Address = "$A$35" Then
            .EnableAutoComplete = False
        Else
            .EnableAutoComplete = True
        End If
    End With
End Sub
 
Upvote 0
I've discovered that the problem is that the code for the worksheet or ThisWorkbook doesn't start running until something is done on the sheet. Just clicking the tab to change sheet doesn't cause the code to start running. Therefore when I switch sheets by clicking the tab and then type an "S" in A35 of sheet2 the code hasn't run yet so I get the autocomplete suggestion. Once I've done something in the cell then the code runs but by then it is too late. Is there anyway to force the code to run the second I switch sheets?

Thanks
 
Upvote 0
you could use the activate event as well:
Code:
Private Sub Worksheet_Activate()
   Application.EnableAutoComplete = Not (ActiveCell.Address = "$A$35")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top