Scroll Event

[sonu]

How can I develop an event using class module that can detect if
current windows has been scrolled. either by using scroll bar or mouse
wheel.

I know little bit about class modules and develop a test event using
raise event and with event commands.

I read and used the article on the following link but still now sure
how to do this with scroll bar.

http://www.tushar-mehta.com/excel/vba/vba-xl event.htm

 

[Chip Pearson]

I don't think you can do this, class module or not. There is no "scroll"
event for any object in Excel, so you would have no way of detecting when
the sheet or window is scrolled.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[sonu]

Chip,

But I thought we could create events that are not part of xls events by
using class module.

Is this not right?

 

[Susan]

and why do you say there is no scroll event? when you record a macro &
scroll, you get:

Range("B3").Select
ActiveWindow.LargeScroll Down:=1
ActiveWindow.SmallScroll Down:=5
Range("B30").Select

doesn't that constitute an event? if not, can you explain why not?
thanks
susan

 

[sonu]

Susan,

I may not be saying it right.

Just like on change or calculate events we can run a sub to do
something I want to find out when user scroll so that I can do
something or run some sub.

What I am trying to do is to have button in my excel to run some
macros. I want this button to be on the worksheet and always on top
line that is visible in the window. if user scroll either by using
scroll bar or mouse I want that button to flote with spreadsheet. Just
like some time you see some buttons or add with some web sites.

To do this I need to trap scroll event. I dont see that in excel. I was
woundering if I can create it using class module.

 

[Chip Pearson]

You can certainly create custom events in a class module. Suppose you
created an event called ScrollUp. How would you know when to raise the
event?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Susan]

Susan,

<blush> i'm sorry, i was arguing with chip, not you!

next time i will preface my arguments! ha ha
(just semi-quietly lurking & learning).
susan

 

[sonu]

Chip,

Thanks for the response.

That is the million dollar question. This is what I thought I could do
with class module.

My other low tech way is to run a on time sub on open and then every
second or 15 second run som code to see what is the top most visible
cell is in the window and if it changed from previous then run the
routine to set the position of the button again.

This sub would add additional overhed to the processor and that is why
I was trying to see if there is any other way I can do that.

Any help is really appreciated.

Thanks

Sonu

 

[sonu]

Any helps guys

Chip,

Thanks for the response.

That is the million dollar question. This is what I thought I could do
with class module.

My other low tech way is to run a on time sub on open and then every
second or 15 second run som code to see what is the top most visible
cell is in the window and if it changed from previous then run the
routine to set the position of the button again.

This sub would add additional overhed to the processor and that is why
I was trying to see if there is any other way I can do that.

Any help is really appreciated.

Thanks

Sonu

 

[NickHK]

Short of getting into hooks/subclassing, why not check the
ActiveWindow.VisibleRange
ActiveWindow.Panes(1).VisibleRange

in the SelectionChange event.
Not perfect, but at least will know if the user is looking at the same area
of the WS or not.

NickHK

 

[paul.robinson]

Hi
If you want to be able to run a command regardless of where you are on
the sheet, why not run the command from the menu bar?
regards
Paul

 

[Chip Pearson]

Short of getting into hooks/subclassing,

Subclassing is what I was thinking about, listening for a WM_VSCROLL and/or
a WM_HSCROLL message.

I've used with good success the freeware SSubTmr6 subclassing component from
www.vbaccelerator.com (www.vbaccelerator.com/codelib/ssubtmr/ssubtmr.htm) .
I can't recall if I've used it in VBA (I've used it many times in VB6), but
it should work without problems in VBA6. Now that I think about it, I'm
pretty sure I've used it at least for testing in VBA6. Its very simple to
use -- set a Reference, Implement the interface in your object module, and
you're good to go.

Then I bought the UniversalCOM Suite ($500 from DesaWare www.desaware.com)
than contains dozens of VB6/VBA/COM components, including one called
SpyWorks that, among other things, lets you subclass windows from other
processes. Very nice and very useful. I use that for my subclassing needs
now.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[sonu]

Chip,

Thanks. I will look into it. would it raised event when user scrolls?

Regards
Sonu

 

[Chip Pearson]

Thanks. I will look into it. would it raised event when user scrolls?

No. Subclassing the Window will provide you messages every time something
happens. The value of the message number tells you what happened. There are
two additional parameters, called wParam and lParam, that provide additional
information, specific to the message number. For example, on a WM_VSCROLL
message number, the wParam parameter indicates whether the user scrolled up
or down, either one page or one line.

Your code would then, in response to receiving a WM_VSCROLL message, call
RaiseEvent to trigger the event you have defined with the Public Event
statement.

Subclassing can be tricky, and isn't something for the novice programmer.
You'll spend a good amount of time looking through the MSDN documentation to
determine what message numbers you want to receive, and what the meaning of
the wParam and lParam variables mean for those particular messages. This all
assumes you know how to get the HWnd of the Window you want to subclass. If
you don't how to do that, subclassing probably isn't for you.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[NickHK]

Chip,
That's why I didn't advise the OP on the subclassing route, as the tone of
the enquiry didn't sound it was advisable.
Depends how much effort the OP is willing to put in .
I'll have a look at your sunclasser in VBA and see how it goes.

NickHK

 

[Chip Pearson]

Nick,

The whole thread intrigued me, so I wrote a sample application in Excel that
subclasses the ActiveWindow and listens for a WM_VSCROLL message. It
supports switching windows within the same workbook. It could easily be
changed in the ThisWorkbook object module to support switching workbooks.

When it receives a WM_VSCROLL message, it examines WParam and raises one of
four events:

Public Event ScrollLineUp(ByVal TopLeftCell As Range, ByVal WindowHWnd As
Long)
Public Event ScrollLineDown(ByVal TopLeftCell As Range, ByVal WindowHWnd As
Long)
Public Event ScrollPageUp(ByVal TopLeftCell As Range, ByVal WindowHWnd As
Long)
Public Event ScrollPageDown(ByVal TopLeftCell As Range, ByVal WindowHWnd As
Long)

The result of the events are messages in the Immediate Window in VBA.

The code is quite well documented. You'll need the SSubTmr6.DLL from
http://www.vbaccelerator.com/codelib/ssubtmr/ssubtmr.htm

Download and unzip the file and copy SSubTmr6.dll to "C:\Windows\System32"
and RegSvr32 the DLL. Then set a reference in VBA to "vbAccelerator VB6
Subclassing and Timer Assistant".

You (and Sonu) can download the Excel workbook at

http://www.cpearson.com/zips/SubClassForVScroll.zip



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[NickHK]

Chip,
Thanks for the effort. I'll have a look.
Whilst I would normally avoid subclasssing unless it was really worth it and
the only way forward, there are questions posted here that require it.

Will you add a link to this file/concept on your site ?
It would be easier then to direct users to the link.

NickHK

 

[Chip Pearson]

Nick,

Will you add a link to this file/concept on your site ?

I have added a link to the SubClassForVSCROLL.xls workbook on my Downloads
page (www.cpearson.com/excel/download.htm) under the topic "SubClassing the
ActiveWindow". It is also listed on my Topics page
(www.cpearson.com/excel/topic.htm ) under the "SubClassing" entry.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Chip Pearson]

I made some substantial updates to the example workbook, making it more
demonstrative. If also has considerably more documentation. The new workbook
is up on the site.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[sonu]

I really appreciate all the help you guys have provided. I am going to
look into it. It is too much info for me at this time but I have
printed and download all the messgaes and necessary files. I am going
to look into this. It is going to be very useful. Thanks again.

Regards
Sonu.