Prev: Scrollbar Repeats
Next: How to send email with no outlook and SMTP server ?
From: Helmut Meukel on 26 Apr 2010 13:55
"GS" <GS(a)discussions.microsoft.com> schrieb im Newsbeitrag
news:uodX6JW5KHA.3576(a)TK2MSFTNGP05.phx.gbl...
> Nobody wrote on 4/26/2010 :
>> "GS" <GS(a)discussions.microsoft.com> wrote in message
>> news:Ogkl48V5KHA.6132(a)TK2MSFTNGP05.phx.gbl...
>>> To not lose the user's current selection, store its address in a variable
>>> first, then activate the cell to set position for scroll lock, then restore
>>> the user's selection. Here's the code:
>>
>> In the OP's case, there is no need to save and restore the selection, there
>> are other way to accomplish what he wants without affecting the selection.
>
> Please explain! Is there another obscure Excel function that does this WITHOUT
> having to first set the active cell?<g>
>
>


Thanks to all for your input.
In this case the chance my program is affecting a user is very small.
My program creates the new workbook at 6:00 in the morning, running
on one computer. I was there, the shift changes at 6:00, there are some
minutes when they don't use the computer, they tell their relief what
happened in the night, what to do next, what to look for, ...

However, I would have liked to use a function that does this without
having to set the active cell first.
I think the code provided by GS will eliminate the slight chance that
my program will interfere with a user.

Thanks again.

Helmut.

From: GS on 26 Apr 2010 15:12
>>>
>>> In the OP's case, there is no need to save and restore the selection,

I don't disagree with what you're implying: -that there's rarely any
reason to select anything in Excel to act on it. SPECIFICALLY IN THIS
OP's CASE this is one of those rarities. The property he's working with
uses the active cell as its marker to set the new "home" position for
the scrollable area. Excel's UI F1 instructs to first select the
position before setting this property. Excel's VBA F1 example code
activates the cell before setting the property.

It was not my intention to dispute this task. I was merely offering a
solution to your well advised point of considering the user's current
selection. In this case there actually is no other way to address that!
According to the OP this wouldn't likely be an issue, but should it
become an issue he now has a way to handle it!

>>> there are other way to accomplish what he wants without affecting the
>>> selection.
>>
>> Please explain! Is there another obscure Excel function that does this
>> WITHOUT having to first set the active cell?<g>
>
> Post a question here:
>
> news://msnews.microsoft.com/microsoft.public.excel.programming

The OP did not ask the Q there. If he had posted there I would have
replied there! He asked it here because he's using VB to automate
Excel. Does that not qualify his Q to be asked (and answered if
possible) here?

GS


From: GS on 26 Apr 2010 16:17
> Thanks to all for your input.
> In this case the chance my program is affecting a user is very small.
> My program creates the new workbook at 6:00 in the morning, running
> on one computer. I was there, the shift changes at 6:00, there are some
> minutes when they don't use the computer, they tell their relief what
> happened in the night, what to do next, what to look for, ...
>
> However, I would have liked to use a function that does this without
> having to set the active cell first.
> I think the code provided by GS will eliminate the slight chance that
> my program will interfere with a user.
>
> Thanks again.
>
> Helmut.

You're welcome!
Unfortunately, the specific property you are trying to work with uses
the active cell as its marker for setting the new "home" position of
the resulting scrollable area. That precludes that this position must
be set to the desired cell BEFORE setting its value to TRUE. Otherwise,
Excel offers no other way to determine the marker.


From: Peter T on 26 Apr 2010 18:48
"GS" <GS(a)discussions.microsoft.com> wrote in message
news:uov0N2X5KHA.5880(a)TK2MSFTNGP04.phx.gbl...
>> Thanks to all for your input.
>> In this case the chance my program is affecting a user is very small.
>> My program creates the new workbook at 6:00 in the morning, running
>> on one computer. I was there, the shift changes at 6:00, there are some
>> minutes when they don't use the computer, they tell their relief what
>> happened in the night, what to do next, what to look for, ...
>>
>> However, I would have liked to use a function that does this without
>> having to set the active cell first.
>> I think the code provided by GS will eliminate the slight chance that
>> my program will interfere with a user.
>>
>> Thanks again.
>>
>> Helmut.
>
> You're welcome!
> Unfortunately, the specific property you are trying to work with uses the
> active cell as its marker for setting the new "home" position of the
> resulting scrollable area. That precludes that this position must be set
> to the desired cell BEFORE setting its value to TRUE. Otherwise, Excel
> offers no other way to determine the marker.

I've just seen and responded to the question in excel.prog' and wandered
over here . As I posted in reply to Helmut's Q asking if freezepanes can be
set without activating appropriate workbook, sheet and cell -

"You can, indeed should, do almost everything in Excel without use of
activate or select. However there are just a few things with the Window
object you do need to, and what you are trying to do is one of them. If you
are using VB6 and automating a hidden instance, there's no need to make the
application itself visible.

code sample snipped

There are also one or two settings that can only be set with at least one
visible workbook.
"

I notice the suggestion earlier about reactivating the previous selection,
easier this way

dim rngPrev as excel.range

on error resume next
set rngPrev = xlApp.selection
on error goto 0 ' or other handler

' activate required workbook, worksheet, cell, freeze panes, etc

if not rngPrev is nothing then
rngPrev.parent.parent.activate ' the workbook
rngPrev.parent.activate ' the sheet
rngPrev.activate ' the range
end if

The original selection might not be a range so maybe better to at least
store the sheet and reactivate it (after its parent workbook)

If app is visible to the user could temporarily disable xlApp.ScreenUpdating

Regards,
Peter T


From: GS on 26 Apr 2010 19:41
Thanks for letting me know he finally did post there. I did not see
this when I looked at all posts for today.


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Scrollbar Repeats
Next: How to send email with no outlook and SMTP server ?