• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Maintaining active cell after a sheetchange event

I have this sheet change event handler which places shapes on the sheet. On exit from the handler the last shape placed is selected. I have added a "target.select" at the end of the handler which works fine if I just enter a value in a cell.

If, however I move the cursor (up, down, left or right, or even pageup or pagedown) instead of pressing enter after changing the value I do not ended up in the expected destination cell. Instead it brings it back to target.

How can I end up where my cursor key is supposed to take me to.

Saqib
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 4
  • 4
  • 4
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Is it conceivable that the "target" gets shifted/re-assigned by the rest of the code?

You could use something like

sub change.event(target as bla)
dim ttarget as range
set ttarget = target

' rest of your code

ttarget.select
end sub

Open in new window


cheers, teylyn
0
 
Chris BottomleyCommented:
One option is to stop moving the cursor ... add your code and perhaps we can help, (and make it execute faster in the process.

Another is save the active cell on entry and move to the saved range on exit from the sub.

Chris

sub ish
dim origRange as range

    origrange = activecell
.
.
.
    origrange.activate
end sub

Open in new window

0
 
Chris BottomleyCommented:
Too slow on the code approach ... and missed the set, but if you want to try and stop the selection movements we could still use your code!

Chris
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Sorry, scrap the above, I did not read your question properly.

This behaviour is by design. If you have target.select as the last line of the change event, then the initial cell that triggered the change event will be selected. If you want another cell to be the active cell after the code, then you need to specifically code that.

Try to omit target.select altogether. The active cell should then be the one triggered by the enter, left, right, up, or down key, unless your macro selects another cell.

cheers, teylyn
0
 
Chris BottomleyCommented:
ALso if you have a selection made and then move the cursor you will deselect the active cell within the selection so the following resets both the selection and the activecell.


Chris
Sub ish()
Dim origRange As Range
Dim origSelection As Range

    Set origRange = ActiveCell
    Set origSelection = Selection
'.
'.
'.
    origSelection.Select
    origRange.Activate
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerAuthor Commented:
I got it by replacing

target.select
by
activecell.select

Can I say that Chris's #35715046 is a similar solution?
0
 
Chris BottomleyCommented:
>>>
I got it by replacing

target.select
by
activecell.select

In Teylyns code it was ttarget.select not target.select

Strictly speaking the first post counts so since 35715046 was the last then if Teylyns code worked for you then that should be solution.  I think that last one is more comprehensive to reflect the possible changes within your sub in respect of active cell and selection, making sure both are re-instated on exit but as I said what works works and is a solution.

Chris
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hang on, I'm still confused at what you are trying to achieve.

target.select will select the cell that was the target cell when the event was fired.

If you use code to change the ACTIVE cell, then activecell.select will put the cursor on the cell that is the active cell when the code ends.

If you do not manipulate the active cell, then you can omit a command like target.select and the active cell will be the one that is selected upon confirming the target cell, like left arrow, mouse click etc.

See attached for three sheets with three different scenarios on Worksheet.Change

What exactly do you want to achieve?


target.xls
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Teylyn, the code is doing nothing to the active cell.

When I type something in a cell and press up, down, left or right, or even pageup or pagedown (instead of the enter key) the active cell is no more the target cell. So the command "Target.Select" was causing problem as I was expecting it to come up elsewhere.

If, instead, I use the "Activecell.select" then I get what I expect.

I think only Chris's #35715046 comes close to this.

Saqib
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
ssaqibh,

the file I attached has three sheets with three different behaviours and three different macros.

Sheet1 - the active cell stays on the target cell that triggered the macro
Sheet2 - the active cell moves to the cell activated by the confirmation. If you use up arrow, the next cell up will be activated. If you click with your mouse to a completely different cell, that cell will be the active cell.
Sheet3 - the active cell will be two cells below the target cell.

Have you tried all?
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Teylyn, Sheet2 of your file shows the effect I am trying to achieve. What you missed is the first paragraph of my question. --> The sub adds a shape in the sheet which is what is selected on exit and not the active cell. That is what I achieved by adding the activecell.select command.

Actually I had already found the answer and was about to delete the question when I saw a number of responses. Chris's response comes closest so I shall award him the points.

Saqib
0
 
Saqib Husain, SyedEngineerAuthor Commented:
This is what I have used
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now