Macro - needs to end on starting screen shot

[msarnie]

I have a large excel spreadsheet that the user starts a macro when they double click a cell. The macro jumps around the sheet. Is there an easy way to make sure that the macro ends at the same screen shot it began with. I think the user will be confussed if I don't return to the beginning screen shot.

 

[boblarson]

Store the starting location in a variable and then set the range back to that cell at the end of the rest of it.

 

[msarnie]

If I store the starting location in a large spreadsheet and return to that starting location it will bring the location to the top left corner.

Is there a way to capture the top left corner?

As if I took a snap-shot of the screen they are viewing in excel - do my macro - and return to the same snap-shot.

In a very large spreadsheet - If I store the starting location which may be in the middle of the viewed screen. When I return to this starting location - which was in the middle of the screen - it is now in the top left corner.

 

[boblarson]

Not sure exactly what you are doing. You mention screenshots. What do screenshots and Excel have to do with each other. Are you placing screenshots within Excel? What is the purpose? You say you double click in a cell. If that is true then the cell you double clicked in is the starting point (Activecell.Address), is it not?

 

[msarnie]

No - not sure how to describe

I have a very large excel spreadsheet - 1000+ rows and 24+ columns
I set up a macro to run in column 9 when the user double clicks the row column.

So the user could be only seeing a part of the whole spreasheet on his desktop (screen shot). So the top corner cell might be A121 and he double clicks the macro in row 133 column 9. The macro does its stuff and skips around the spreadsheet. When I am done I want to end on the same screen shot - the top corner cell will still be A121.

 

[boblarson]

So in the double click event use

Dim strAddress As String

strAddress = Activecell.Address


'  Then all of your other code which jumps around here


Activesheet.Range(strAddress).Select

 

[msarnie]

Can you try your code for me.

Have the top right corner on A232 and have your active cell on I261 and run your code - stop it and change the view of the screen - and run the end - let me know if the top right corner is on A232

It doesn't work for me

 

[boblarson]

Of course that won't work because it can't be stopped and then restarted. If you do that the variable value is lost. Doesn't your code run all the way through?

 

[msarnie]

By stopping - I'm saying use F9 to put a break in your code.

Use F8 to step through your code - have the top right corner on your screen at A232 and have the active cell on I261. Step through your code to capture the activecell address. Stop and change the screen view by hitting page down a few time and then run the remaining part of your code. The screen shot will not return to the top right corner at A232

 

[chergh]

Put

application.screenupdating = false

at the start of your code

and

application.screenupdating = true

at then end

 

[msarnie]

Thanks - I ended up doing that - using application.screenupdating.
But I didn't want to - but no other way.

The thing though is this group of users like seeing the macro in action - they like knowing that the macro is working and it helps them trouble shoot.

 

[chergh]

Thanks - I ended up doing that - using application.screenupdating.
But I didn't want to - but no other way.

The thing though is this group of users like seeing the macro in action - they like knowing that the macro is working and it helps them trouble shoot.

If your users find the screen ending up in a different postion to confusing then I don't see that they will have much to offer to troubleshooting.

My users ask for all sort of stupid things and when I am feeling diplomatic I tell them that there requests are to advanced for me to implement. When I don't feel diplomatic I tell them to write the macro themselves.

Turning screenupdating off is a good idea for any macro that takes more than a second to run and if your users don't like tell them "to bad".