• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Worksheet Deactivation error.

Frncis

Member
I am trying to have excel return to a certain cell prior to the sheet being deactivated. I have this code:
Code:
Private Sub Worksheet_Deactivate()

 Range("I23").Select
End Sub
It hangs on Range("I23").Select. I have googled, & have found deactivating workbook, but not sheet. What am I missing?
 

Chihiro

Excel Ninja
It will work as is, if you are switching between sheets in same workbook.

But it will select I23 on the newly activated worksheet. Since you can't select range of inactive worksheet.
 

Frncis

Member
It will work as is, if you are switching between sheets in same workbook.

But it will select I23 on the newly activated worksheet. Since you can't select range of inactive worksheet.
Ok. Thanks for explain that. Is it possible to have it go to I23 on the old sheet, prior to switching to a new sheet?
 

Chihiro

Excel Ninja
Oh, I may have misread your initial code. I assumed it was in Workbook_SheetDeactivate event. Your code hangs because, it's on Worksheet module.

... prior to switching to a new sheet?
Why? As soon as it goes out of focus, you will lose your selection anyways.

If you need to have I23 selected when you go back to the sheet. Just use Worksheet_Activate() event.
 

Frncis

Member
Oh, I may have misread your initial code. I assumed it was in Workbook_SheetDeactivate event. Your code hangs because, it's on Worksheet module.


Why? As soon as it goes out of focus, you will lose your selection anyways.

If you need to have I23 selected when you go back to the sheet. Just use Worksheet_Activate() event.
I also misread your reply. When a user selects work sheet that has Meeting_#_1 in it's name, they have 2 choices. Yes = B63, No = I23. The curser stay at the last selected cell. Is it possible to have it go to I23 prior to choosing? That way the curser stay hidden. I had it on Worksheet_Activate & it wasn't allowing the user to choose Yes or No.
 

Chihiro

Excel Ninja
Hmm, don't think you can using event codes. I'm not aware of any method to intercept before sheet is deactivated.

What you could do, is create your own module/logic for sheet navigation and place the code there.

Ex: All but the current sheet & the front page sheet being hidden, when user clicks on link/button execute code to select i23, before showing new sheet and moving to it.
 

Frncis

Member
Hmm, don't think you can using event codes. I'm not aware of any method to intercept before sheet is deactivated.

What you could do, is create your own module/logic for sheet navigation and place the code there.

Ex: All but the current sheet & the front page sheet being hidden, when user clicks on link/button execute code to select i23, before showing new sheet and moving to it.
Thanks. it was worth a try.
 
Top