This message was edited by doofusboy at 2003-11-2 5:1:11

---

How can one determine when a user has Pasted data into a cell in an Excel worksheet? I'd like to run certain data validation code ONLY if the data is Pasted into the worksheet.

: This message was edited by doofusboy at 2003-11-2 5:1:11

---

: How can one determine when a user has Pasted data into a cell in an Excel worksheet? I'd like to run certain data validation code ONLY if the data is Pasted into the worksheet.
:
:
:
Hi, Excel have internal events for each Worksheet and Workbook... The way how to get these events is this:

Private WithEvents myExcel As Excel.Application

Private Sub Form_Load()
    Set myExcel = New Excel.Application
    myExcel.Workbooks.Add
    myExcel.Visible = True
End Sub

Private Sub myExcel_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    MsgBox "New value is " & Target.Value & " and position is " & Target.Row & " " & Target.Column
End Sub

When you declare varivable like WithEvent, in desing window (up, left) appears new object (beside Form1, Command1, Image1...) and it has all these events...
Good luck

PavlinII

:

Private WithEvents myExcel As Excel.Application
: 
: Private Sub Form_Load()
:     Set myExcel = New Excel.Application
:     myExcel.Workbooks.Add
:     myExcel.Visible = True
: End Sub
: 
: Private Sub myExcel_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
:     MsgBox "New value is " & Target.Value & " and position is " & Target.Row & " " & Target.Column
: End Sub

When you declare varivable like WithEvent, in desing window (up, left) appears new object (beside Form1, Command1, Image1...) and it has all these events...
: Good luck
:
: PavlinII
:
:
Pavlin, thanks for your reply, but this line of your code:
Private WithEvents myExcel As Excel.Application
gives me this compile error:
User-defined type not defined

Further, I do not understand how any of your code will tell me when Paste Event has occurred. Am I missing something?

: :

Private WithEvents myExcel As Excel.Application
: : 
: : Private Sub Form_Load()
: :     Set myExcel = New Excel.Application
: :     myExcel.Workbooks.Add
: :     myExcel.Visible = True
: : End Sub
: : 
: : Private Sub myExcel_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
: :     MsgBox "New value is " & Target.Value & " and position is " & Target.Row & " " & Target.Column
: : End Sub

When you declare varivable like WithEvent, in desing window (up, left) appears new object (beside Form1, Command1, Image1...) and it has all these events...
: : Good luck
: :
: : PavlinII
: :
: :
: Pavlin, thanks for your reply, but this line of your code:
: Private WithEvents myExcel As Excel.Application
: gives me this compile error:
: User-defined type not defined
:
: Further, I do not understand how any of your code will tell me when Paste Event has occurred. Am I missing something?
:
There could be one reason of this: You didn't add reference to MS Excel library to you app, did you? Click: Project/References/"Microsoft Excel x.0 Object Library", OK... (x.0 is version depending on your version of Excel - 5.0, 8.0, 9.0.. any) And now it could work... But I supposed that you have done this before you started to care about controling Excel ...

PavlinII

This message was edited by Moderator at 2003-11-4 20:17:9

---

: : :

Private WithEvents myExcel As Excel.Application
: : : 
: : : Private Sub Form_Load()
: : :     Set myExcel = New Excel.Application
: : :     myExcel.Workbooks.Add
: : :     myExcel.Visible = True
: : : End Sub
: : : 
: : : Private Sub myExcel_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
: : :     MsgBox "New value is " & Target.Value & " and position is " & Target.Row & " " & Target.Column
: : : End Sub

When you declare varivable like WithEvent, in desing window (up, left) appears new object (beside Form1, Command1, Image1...) and it has all these events...
: : : Good luck
: : :
: : : PavlinII
: : :
: : :
: : Pavlin, thanks for your reply, but this line of your code:
: : Private WithEvents myExcel As Excel.Application
: : gives me this compile error:
: : User-defined type not defined
: :
: : Further, I do not understand how any of your code will tell me when Paste Event has occurred. Am I missing something?
: :
: There could be one reason of this: You didn't add reference to MS Excel library to you app, did you? Click: Project/References/"Microsoft Excel x.0 Object Library", OK... (x.0 is version depending on your version of Excel - 5.0, 8.0, 9.0.. any) And now it could work... But I supposed that you have done this before you started to care about controling Excel ...
:
: PavlinII
:

Sorry, I forgot to answer the second part...
When you define WithEvents iExcel blablabla, you get object iExcel. This iExcel equals to Excel (but it's just not displayed yet) - you can do everything that excel can do...
When you open VB editor in Excel (Alt+F11) and doubleclick on sheet name (list1) in Project Explorer you gets place, where you can write code attached to this list (or any other one.. it behaves nearly like module) and there exist one predefined object Worksheet.. and when you choose this object, you'll get list of all events that WorkSheet has (Like form: Activate, Load, Click, Unload etc.) but here are useful things like BeforeDoubleClick, AfterDoubleClick, Calculate, SelectionChange.. And SelectionChange is important for you, because this event is generated (called) everytime when any cell is changed (there is typed or pasted text for example).. And if you take your code that controls if the pasted text (or value, I don't know) is correct, it will control what you want everytime after some cell changed.. Do you understand? You can play with this there... And if you define iExcel like WithEvents, you gets all these nice events too... So you will know when any cell is changed...

In that code it was sub myExcel_SheetSelectionChange... It even gives you Targed - the changed cell!

Hope it's clear now. If you still don't understand any part of this, ask..

PavlinII

PavlinII, I understand exactly what you have said thus far and am in fact placing my code under the SelectionChange event. Understand how to use "Target" and infact use it extensively in my code.

Not sure how to make my question clearer. How can I determine that the Target.Cells or Target.Value was PASTED into the worksheet as opposed to being typed in by the user???

Is it possible to subclass or hook the WM_PASTE message???

This message was edited by Moderator at 2003-11-5 19:24:14

---

: PavlinII, I understand exactly what you have said thus far and am in fact placing my code under the SelectionChange event. Understand how to use "Target" and infact use it extensively in my code.
:
: Not sure how to make my question clearer. How can I determine that the Target.Cells or Target.Value was PASTED into the worksheet as opposed to being typed in by the user???
:
: Is it possible to subclass or hook the WM_PASTE message???
:
Oh, I expected that for you is pasting and typing by user the same.. (Do you really need to recognise what king it was? If you write or paste data, you should control it in both way, shouldn't you?)
I know one way how to get system events... But..
This code registers HotKey Ctrl+V (but it don't care about Ctrl+C and pasting by ReturnKey in Excel)... And when I tried to use
If msg = WM_PASTE Then
I didn't got this event... And even I didn't got it when I pasted into my form.. There exist API functions RegisterEventSource and DeRegisterEventSource... But I didn't have time to try them..
And Excel has no event like BeforePaste of AfterPaste.. It has that SelectionChange and Change. And there is little different that you can use!
When you type text, Change and then SelectionChange is generated.
When you paste text, Change is generated but SelectionChange ISN'T... And it works with Ctrl+C, Ctrl+V and Ctrl+C, Return.. (It do not work when you use F2, Ctrl+V - it behaves like typing text)

'Module
Public glngOldProc As Long
Private Const WM_HOTKEY = &H312
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


Public Function NewWindowProc(ByVal hWnd_X As Long, _
 ByVal msg As Long, ByVal wParam As Long, ByVal lParam As Long) _
 As Long
If msg = WM_HOTKEY Then
    If wParam = 1 Then MsgBox "CTRL + v
    NewWindowProc = 0
Else
    NewWindowProc = CallWindowProc(glngOldProc, hWnd_X, msg, _
     wParam, lParam)
End If
End Function

'Form
Private Sub Form_Load()
glngOldProc = SetWindowLong(Form1.hWnd, GWL_WNDPROC, _
 AddressOf NewWindowProc)
'Asc("V") - there MUST be UpperChr
RegisterHotKey Me.hWnd, 1, MOD_CONTROL, Asc("V") 'Number 1 is wParam
End Sub

Private Sub Form_Unload()
UnregisterHotKey Form1.hWnd, 1 'Again 1 = wParam - identification
                               'when you have more HotKeys
SetWindowLong Me.hWnd, GWL_WNDPROC, glngOldProc
End Sub

Do you really have to recognise what happend? (Paste X Type)?
Using different between Change and SelectionChange can be useful..

PavlinII

: This code registers HotKey Ctrl+V (but it don't care about Ctrl+C and pasting by ReturnKey in Excel)...
:

Very, very bad idea. That code will prevent any pasting from happening in other applications and will cause you code to be triggered when a user attempts to paste in other apps. It also won't be triggered if the user uses the menu button or the mouse right-click context menu.

Whether or not it is any better is up to you, but I'd try the following. I don't know much about Excel, so this code is written for a textbox in VB. It needs two variablea available to multiple events called CurLen and CurStart.

In MouseUp and KeyUp events and maybe MouseMove:

CurLen = Len(txtName.Text) - txtName.SelLength
CurStart = txtName.SelStart

And in the change event:

Static LastLen As Long
If CurLen - LastLength > 1 Then
    'More than 2 characters added at once.
    'User must have pasted.
    'CurStart is where the changes started and
    'CurLength is how many characters were added.
End If

Hope this helps! (hope it works...)
KDL

: : This code registers HotKey Ctrl+V (but it don't care about Ctrl+C and pasting by ReturnKey in Excel)...
: :
:
: Very, very bad idea. That code will prevent any pasting from happening in other applications and will cause you code to be triggered when a user attempts to paste in other apps. It also won't be triggered if the user uses the menu button or the mouse right-click context menu.
:
: Whether or not it is any better is up to you, but I'd try the following. I don't know much about Excel, so this code is written for a textbox in VB. It needs two variablea available to multiple events called CurLen and CurStart.
:
: In MouseUp and KeyUp events and maybe MouseMove:
:
:

CurLen = Len(txtName.Text) - txtName.SelLength
: CurStart = txtName.SelStart
: 

:
: And in the change event:
:
:

Static LastLen As Long
: If CurLen - LastLength > 1 Then
:     'More than 2 characters added at once.
:     'User must have pasted.
:     'CurStart is where the changes started and
:     'CurLength is how many characters were added.
: End If
: 

:
: Hope this helps! (hope it works...)
: KDL
:
KDL, thanks for the reply. Innovative approach, but don't think that will cover "dog" being pasted over by "cat".

The scenario I have is this: multiple columns where the cells have been set up with Excel's Data Validation so that the data entered into them must be specific values from predefined lists. Problem is, if someone Pastes a value into these cells, the Data Validation formatting is blown away, so I would like to run my own validation code to insure that the value being pasted into the cell is in the predefined list. Make sense?

: KDL, thanks for the reply. Innovative approach, but don't think that will cover "dog" being pasted over by "cat".
:

Good call; same-length overwrite (or overwriting with less) never occured to me.

: The scenario I have is this: multiple columns where the cells have been set up with Excel's Data Validation so that the data entered into them must be specific values from predefined lists. Problem is, if someone Pastes a value into these cells, the Data Validation formatting is blown away, so I would like to run my own validation code to insure that the value being pasted into the cell is in the predefined list. Make sense?
:

I understand perfectly, I just don't have much idea how else to try and handle it. You can trap key and mouse commands if you can subclass the field and try to interpret what is about to happen; WM_RBUTTONDOWN and you can stop/replace a right-click menu or key messages to watch for Ctrl+V are two possibilities...

: : KDL, thanks for the reply. Innovative approach, but don't think that will cover "dog" being pasted over by "cat".
: :
:
: Good call; same-length overwrite (or overwriting with less) never occured to me.
:
: : The scenario I have is this: multiple columns where the cells have been set up with Excel's Data Validation so that the data entered into them must be specific values from predefined lists. Problem is, if someone Pastes a value into these cells, the Data Validation formatting is blown away, so I would like to run my own validation code to insure that the value being pasted into the cell is in the predefined list. Make sense?
: :
:
: I understand perfectly, I just don't have much idea how else to try and handle it. You can trap key and mouse commands if you can subclass the field and try to interpret what is about to happen; WM_RBUTTONDOWN and you can stop/replace a right-click menu or key messages to watch for Ctrl+V are two possibilities...
:

To KDL: In Excell, cells don't know position of cursor inside the text... And ChangeEvent is raised after the change is validated (not during typing text :(
And I wrote that it is not good idea to try that first code.. (I think..)

And about validation:
BTW: When you paste values (or insert by macro or by filling) Validation is blown away because it's not possible to raise repair events for more than one cell at one time... (For case when you paste more than one cell). And authors of Excel were too lazy to create Range().Validate or anything like this because you can select more than one cell... So there is no way how to raise authomatic validation.
Only one thing that you can do is ActiveWorkbook.CircleInvalid... But it only makes red circles around cells with invalid values... And there is no way how to For Each InvalidValue In InvalidValues control, repair or request repairing of the value... I said, they were too lazy.. And ickie because inside .CircleInvalid must be something like this

But. You said that you would like to do own validation on paste event, didn't you? So, what about this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Variant                   'For Each variable must be Variant
For Each myCell In Target
    'MsgBox myCell.AddressLocal
    mySpecificControlOfCell (myCell)    'Do whatever you want, myCell behaves like cell
Next myCell
End Sub

This will be raised everytime when value of cell is changed (by typing, inserting from macro, filling or pasting - just everytime) and it controls values inside whole selection (if you paste more than one cell at once...

I hope that this is enough effective... (When your validating code must be written in any case...) And controling every entering could be usefull too

PavlinII

Pavlin, thanks for your suggestion. Am going to combine it with what I found at this link, as this seems to be as close as I'm going to get to my original objective.

http://j-walk.com/ss/excel/tips/tip98.htm

Thanks again for the time you've spent on this.