Prev: Scrollbar Repeats
Next: How to send email with no outlook and SMTP server ?
From: Nobody on 26 Apr 2010 10:31
VBA groups:

news://msnews.microsoft.com/microsoft.public.office.developer.vba
news://msnews.microsoft.com/microsoft.public.excel.programming
news://msnews.microsoft.com/microsoft.public.access.modulescoding
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.outlook.program_vba
news://msnews.microsoft.com/microsoft.public.office.developer.outlook.vba
news://msnews.microsoft.com/microsoft.public.powerpoint




From: GS on 26 Apr 2010 12:40
Nobody explained :
> "Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
> news:e2fX%23XU5KHA.4740(a)TK2MSFTNGP06.phx.gbl...
>> Norm,
>>
>> I haven't done any makro recording since the days of Windows 3.1
>> so I totally forgot that Excel is capable of makro recording.
>> I now came up with
>> ws.Range("B2").Select
>> ActiveWindow.FreezePanes = True
>
> That code is why you need to ask in an Excel group. There is no need to
> "Select" first, which interferes with what the user has currently selected.

<FYI>
ActiveWindow.FreezePanes sets scroll lock to the left and top of the
currently active cell. That precludes the cell must be selected BEFORE
setting the position. 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:

'Temporarily supress screen activity
'so user doesn't see anything.
'(Also makes code run faster)
With Application
..ScreenUpdating = False

'Get current selection address
Dim sCurrentSelection As String
sCurrentSelection = .ActiveWindow.Selection.Address

'Set your scroll lock to desired row, column
..ActiveSheet.Cells(2, 2).Activate
..ActiveWindow.FreezePanes

'Restore user's selection
..ActiveSheet.Range(sCurrentSelection).Select

'Restore screen activity
..ScreenUpdating = True
End With

All that's needed is to replace "Application" with your own Excel
object. As already suggested by others, it's a good idea to write your
code in Excel's VBE first so you can step through it on the fly to see
how it's working. What's important about using any code generated by
macro recorders is thay usually need a lot of cleanup of extraneous
steps, and you have to ensure your VB6 project always has fully
qualified refs to the objects of the app you're automating. People
always forget that global objects, properties, and methods can be
executed without a ref to the Application object (as you saw in the
code you posted).

HTH
Garry
--


From: Nobody on 26 Apr 2010 12:56
"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.


From: GS on 26 Apr 2010 13:04
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>


From: Nobody on 26 Apr 2010 13:49
"GS" <GS(a)discussions.microsoft.com> wrote in message
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>

Post a question here:

news://msnews.microsoft.com/microsoft.public.excel.programming


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