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/vb...xl%20event.htm

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" wrote in message
ups.com...
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/vb...xl%20event.htm

Chip,

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

Is this not right?


Chip Pearson wrote:
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" wrote in message
ups.com...
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/vb...xl%20event.htm

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 wrote:
Chip,

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

Is this not right?


Chip Pearson wrote:
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" wrote in message
ups.com...
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/vb...xl%20event.htm

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.

Susan wrote:
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 wrote:
Chip,

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

Is this not right?


Chip Pearson wrote:
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" wrote in message
ups.com...
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/vb...xl%20event.htm

sonu wrote:
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 wrote:
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.

Susan wrote:
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 wrote:
Chip,

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

Is this not right?


Chip Pearson wrote:
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" wrote in message
ups.com...
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/vb...xl%20event.htm

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)

"sonu" wrote in message
oups.com...
Chip,

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

Is this not right?


Chip Pearson wrote:
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" wrote in message
ups.com...
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/vb...xl%20event.htm

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




Chip Pearson wrote:
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)

"sonu" wrote in message
oups.com...
Chip,

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

Is this not right?


Chip Pearson wrote:
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" wrote in message
ups.com...
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/vb...xl%20event.htm

Any helps guys

sonu wrote:
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




Chip Pearson wrote:
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)

"sonu" wrote in message
oups.com...
Chip,

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

Is this not right?


Chip Pearson wrote:
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" wrote in message
ups.com...
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/vb...xl%20event.htm

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

"sonu" wrote in message
ups.com...
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/vb...xl%20event.htm

"NickHK" wrote in message
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 (http://www.vbaccelerator.com/codelib...mr/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)


"NickHK" wrote in message
...
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

"sonu" wrote in message
ups.com...
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/vb...xl%20event.htm

Chip,

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

Regards
Sonu


Chip Pearson wrote:
"NickHK" wrote in message
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 (http://www.vbaccelerator.com/codelib...mr/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)


"NickHK" wrote in message
...
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

"sonu" wrote in message
ups.com...
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/vb...xl%20event.htm

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)



"sonu" wrote in message
oups.com...
Chip,

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

Regards
Sonu


Chip Pearson wrote:
"NickHK" wrote in message
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 (http://www.vbaccelerator.com/codelib...mr/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)


"NickHK" wrote in message
...
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

"sonu" wrote in message
ups.com...
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/vb...xl%20event.htm

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" wrote in message
...
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)



"sonu" wrote in message
oups.com...
Chip,

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

Regards
Sonu


Chip Pearson wrote:
"NickHK" wrote in message
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
(http://www.vbaccelerator.com/codelib...mr/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)


"NickHK" wrote in message
...
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

"sonu" wrote in message
ups.com...
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/vb...xl%20event.htm