Worksheet Events - the continuing saga
Matt
mean take a set of cells that has data in them and goto Data |
Validation and under allow: set to list and set a list. When I use the
following code, the Worksheet_Change event does not fire when I do
either of the following:
1. Paste Data from an External source (clipboard) into a cell
2. Select a value from the aforementioned Dropdown.
I've been struggling with this for the last few days, and so far I
haven't been able to figure it out. I've read as much of the help file
as I can, but without a decent understanding of Excel's object model, I
can't determine what to do. I've read all of the info I can at any MVP's
website I can find but I can't find an answer to this issue.
Here is the code I have so far:
If Target.Column = 12 Then 'Column L
Target.Offset(1, -9).Select
ElseIf Target.Column = 7 Then
With Target.Offset(0, 1)
.FormulaR1C1 = "=Now()"
.Select
End With
With Selection
.Copy
.PasteSpecial xlValues
End With
Application.CutCopyMode = False
'Target.Offset(0, 1).Select
Else
Target.Offset(0, 1).Select
End If
End Sub
This works perfectly when I manually change the data in the cells, but
If I paste something in or select something from the dropdown, it
doesn't fire the Worksheet_change event. I'm trying to figure out which
event is fired or if there isn't one.
Thanks for any help you can provide
Matt
Sent via Deja.com http://www.deja.com/
Before you buy.
Tom Ogilvy
In Excel 97, no event fires when the dropdown is used from data validation.
Best you can do is have another cell reference the cell with the validation,
which will cause the calculate event to fire when the value is selected from
the list. However, calculate does not provide any information on what
triggered it, so you don't have a variable like target. In Excel 2000 I
believe the change event will fire.
I don't recall trying to trap a paste, but in past discussions, I believe
the approach was:
In the worksheet_selectionchange event, you can test if
application.cutcopymode = true - that is about the only thing I can think
of. Stratos posted some code today related to this issue. That code
monitors the caption of the undo button
Regards,
Tom Ogilvy
MVP Excel
Matt <cybrp...@my-deja.com> wrote in message
news:8qtojl$l3k$1...@nnrp1.deja.com...
Tom Ogilvy
Maybe he will jump in.
Regards,
Tom Ogilvy
MVP Excel
Matt Williamson <cybrp...@my-deja.com> wrote in message
news:8qu5n1$vi8$1...@nnrp1.deja.com...
> Thank you for that clarification Tom. I did see the code that Stratos
> posted and tested it. I works alright but it is lacking in some areas.
> I'm sure this is way beyond what I should actually attempt, but has
> anyone ever tried subclassing the worksheet or is it even possible? I
> wrote a routing in VB that subclassed a UserControls input field to
> check for the Windows message for paste. I might be able to do the same
> thing if I can get an hWnd for the Excel sheet. I don't have an API spy
> here at work, so I can't even check. I'll see what I can come up with
> when I get home though.
>
> Thanks again..
Matt Williamson
Jim Rech
I subclassed Excel to see what would happen. What happened was a flood of
messages that VB was too slow to handle. Hang city<g>.
--
Jim Rech
Excel MVP
Stratos Malasiotis
> Subclassing Excel is more in Stratos' area of expertise.
I guess that 'Stratos' is me <g> ; not much of a subclassing expert though :-)
Well, my opinion Matt, is that you sould not consider subclassing in any short of VB, and especially Excel VBA, except it is absolutely the
only possible way.
If we assume that the real expert on the subject is Stephen B. , a good example of the pottential problems is that he doen't use it for any
of his downloads. Even the VBEOnKey marvel works through a timer, not through subclassing (and still produces GPFs (just joking..:-).
Until know I have subclassed only userforms to get the WM_PAINT event (available in the first child window) and custom window-agents (via
CreateWindowEx) for putting an icon in the system tray and control a hidden instance of Excel. I think that this kind of unusual problems
are the only occasions that worths experimenting at that level.
Comming on the specific issue of the 'pasting', I think that there is no kind of specific message that you could try to subclass. You could
do something that detects all possible ways to paste something; not really worths it in my opinion and not sure if it would work at all.
Additionally, just for clarification, you cannot subclass worksheets - the don't exist - only windows (like a EXCEL7 workbook window). Just
a language game but some times it is important. The spreadsheet is just a drawing, nothing more.
Some controls are windowed (like the listboxes) and other create a window on-request when you activate them (like textboxes); or at least, I
guess so....
That's all I could say about the API staff. In other words, I would agree with Tom and Jim that it is a bad idea from many points of view
(good challenge though :-)
Athough I try to avoid doing such comments because they can easily begin a 'religious war', I would say that a lot of problems that seem
impossible/unsolvable can easily be worked around with good design.
"Better by Design" they use to say... (and don't forget that i.designers are one of the highest paid technical professions)
So if you just want to find a solution that will meet the requirements, on specifications, on time, and in profit, try to redesign your
concept avoiding any reason for requiring extreme solutions; learn to step back one step gracefuly, and then make ten steps in front....
If again you just love the challenge, you can experiment as much as want in your free time and pass your valuable findings to us, so we can
take advantage of you ;-)
Regards,
Thanks for inviding me in...
Stratos
Tom Ogilvy wrote:
>
> Subclassing Excel is more in Stratos' area of expertise.
>
> Maybe he will jump in.
>
> Regards,
> Tom Ogilvy
> MVP Excel
>
> Matt Williamson <cybrp...@my-deja.com> wrote in message
> news:8qu5n1$vi8$1...@nnrp1.deja.com...
Stratos Malasiotis
It can work in two ways. Via a timer (you momentarely subclass the Activeworkbook and get one message) and as pure subclassing.
For the first, run the SubclasingviaTimer() procedure that gets something like one in a thousand messages.
For the second, run the StartSubclassing() procedure after commenting the "StopSubclassing" line in the WinProc.
They both work, but in any case save everything first and get a fire-extinguisher <g>
If you work in Excel 2000 replace Getz's amd Kaplan's AddrOf function with the build in AddressOf operator.
Best luck,
Regards,
Stratos
-------------------------------------------------------
Option Explicit
Private Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" _
( _
ByVal lpClassName As String, _
ByVal lpWindowName As String _
) _
As Long
Private Declare Function FindWindowEx _
Lib "user32" _
Alias "FindWindowExA" _
( _
ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String _
) _
As Long
Private Declare Function SetTimer _
Lib "user32" _
( _
ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long _
) _
As Long
Private Declare Function KillTimer _
Lib "user32" _
( _
ByVal hWnd As Long, _
ByVal nIDEvent As Long _
) _
As Long
Private Declare Function SetWindowLong _
Lib "user32" _
Alias "SetWindowLongA" _
( _
ByVal hWnd As Long, _
ByVal ndx As Long, _
ByVal newValue As Long _
) _
As Long
Private Declare Function CallWindowProc _
Lib "user32" _
Alias "CallWindowProcA" _
( _
ByVal lpPrevWndFunc As Long, _
ByVal hWnd As Long, _
ByVal Msg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long _
) _
As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function GetCurrentVbaProject _
Lib "vba332.dll" _
Alias "EbGetExecutingProj" _
( _
hProject As Long _
) _
As Long
Private Declare Function GetFuncID _
Lib "vba332.dll" _
Alias "TipGetFunctionId" _
( _
ByVal hProject As Long, _
ByVal strFunctionName As String, _
ByRef strFunctionID As String _
) _
As Long
Private Declare Function GetAddr _
Lib "vba332.dll" _
Alias "TipGetLpfnOfFunctionId" _
( _
ByVal hProject As Long, _
ByVal strFunctionID As String, _
ByRef lpfn As Long _
) _
As Long
Const GWL_WNDPROC = -4
Const WM_MOVE = &H3
Const WM_SIZING = &H214
Const WM_ACTIVATEAPP = &H1C
Const WM_PAINT = &HF
Dim whWnd As Long
Dim OldWinProc As Long
Dim WindowsTimer As Long
Sub SubclasingviaTimer()
fncWindowsTimer 500
End Sub
Sub StopTimer()
fncStopWindowsTimer
End Sub
Sub StartSubclassing()
whWnd = fncGethWndtoWorkbook(ActiveWorkbook.Name)
OldWinProc = SetWindowLong(whWnd, _
GWL_WNDPROC, AddrOf("WinProc"))
End Sub
Sub StopSubclassing()
SetWindowLong whWnd, GWL_WNDPROC, OldWinProc
End Sub
Function WinProc _
(ByVal hWnd As Long, _
ByVal uMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long _
) _
As Long
WinProc = CallWindowProc(OldWinProc, hWnd, uMsg, wParam, lParam)
Debug.Print uMsg
StopSubclassing
End Function
Public Function fncWindowsTimer( _
TimeInterval As Long _
) As Boolean
Dim WindowsTimer As Long
WindowsTimer = 0
WindowsTimer = SetTimer _
( _
hWnd:=fncGethWndtoWorkbook(ActiveWorkbook.Name), _
nIDEvent:=0, _
uElapse:=TimeInterval, _
lpTimerFunc:=AddrOf("cbkCustomTimer") _
)
End Function
Public Function fncStopWindowsTimer()
KillTimer _
hWnd:=fncGethWndtoWorkbook(ActiveWorkbook.Name), _
nIDEvent:=WindowsTimer
End Function
Function cbkCustomTimer _
( _
ByVal Window_hWnd As Long, _
ByVal WindowsMessage As Long, _
ByVal EventID As Long, _
ByVal SystemTime As Long _
) _
As Long
StartSubclassing
End Function
Private Function fncGethWndtoWorkbook(WorkbookName As String) As Long
Dim XLMAINhWnd As Long, XLDESKhWnd As Long, EXCEL7hWnd As Long
On Error GoTo ExitFunction
If CBool(Len((Workbooks(WorkbookName).Name))) Then
WorkbookName = Workbooks(WorkbookName).Windows(1).Caption
End If
XLMAINhWnd = FindWindow("XLMAIN", Application.Caption)
XLDESKhWnd = FindWindowEx(XLMAINhWnd, 0, "XLDESK", vbNullString)
If XLMAINhWnd = 0 Or XLDESKhWnd = 0 Then Exit Function
fncGethWndtoWorkbook = FindWindowEx(XLDESKhWnd, 0, "EXCEL7", WorkbookName)
On Error GoTo 0
ExitFunction:
End Function
Private Function AddrOf _
( _
CallbackFunctionName As String _
) _
As Long
Dim aResult As Long, CurrentVBProject As Long, strFunctionID As String, _
AddressofFunction As Long, UniCbkFunctionName As String
UniCbkFunctionName = StrConv(CallbackFunctionName, vbUnicode)
If Not GetCurrentVbaProject(CurrentVBProject) = 0 Then
aResult = GetFuncID _
( _
hProject:=CurrentVBProject, _
strFunctionName:=UniCbkFunctionName, _
strFunctionID:=strFunctionID _
)
If aResult = 0 Then
aResult = GetAddr _
( _
CurrentVBProject, _
strFunctionID, _
lpfn:=AddressofFunction _
)
If aResult = 0 Then
AddrOf = AddressofFunction
End If
End If
End If
End Function
-------------------------------------------------------------------
Matt Williamson
and attempt it, I'll let ya know how things go
Matt
Matt Williamson
Ha! I assumed there would be quite a few of them.. if VB couldn't
handle them, I know VBA won't be able to. I guess that is just further
proof that it isn't a feasible answer.
Thanks for the input Jim
Matt