Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Capture Paste Event

doofusboydoofusboy Posts: 256Member
[b][red]This message was edited by doofusboy at 2003-11-2 5:1:11[/red][/b][hr]
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.


«1

Comments

  • PavlinIIPavlinII Posts: 404Member
    : [b][red]This message was edited by doofusboy at 2003-11-2 5:1:11[/red][/b][hr]
    : 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:
    [code]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[/code]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

  • doofusboydoofusboy Posts: 256Member
    : [code]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[/code]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:
    [B]Private WithEvents myExcel As Excel.Application[/B]
    gives me this compile error:
    [B]User-defined type not defined[/B]

    Further, I do not understand how any of your code will tell me when Paste Event has occurred. Am I missing something?
  • PavlinIIPavlinII Posts: 404Member
    : : [code]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[/code]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:
    : [B]Private WithEvents myExcel As Excel.Application[/B]
    : gives me this compile error:
    : [B]User-defined type not defined[/B]
    :
    : 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: [blue]Project/References/"Microsoft Excel x.0 Object Library"[/blue], 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
  • PavlinIIPavlinII Posts: 404Member
    [b][red]This message was edited by Moderator at 2003-11-4 20:17:9[/red][/b][hr]
    : : : [code]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[/code]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:
    : : [B]Private WithEvents myExcel As Excel.Application[/B]
    : : gives me this compile error:
    : : [B]User-defined type not defined[/B]
    : :
    : : 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: [blue]Project/References/"Microsoft Excel x.0 Object Library"[/blue], 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 [b]Worksheet[/b].. 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, [blue]SelectionChange[/blue].. 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


  • doofusboydoofusboy Posts: 256Member
    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 [B]PASTED[/B] into the worksheet as opposed to being typed in by the user???

    Is it possible to subclass or hook the WM_PASTE message???
  • PavlinIIPavlinII Posts: 404Member
    [b][red]This message was edited by Moderator at 2003-11-5 19:24:14[/red][/b][hr]
    : 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 [B]PASTED[/B] 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)

    [code][green]'Module[/green]
    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

    [green]'Form[/green]
    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") [green]'Number 1 is wParam[/green]
    End Sub

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

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

    PavlinII


  • KDivad LeahcimKDivad Leahcim Posts: 3,948Member
    : 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:

    [code]CurLen = Len(txtName.Text) - txtName.SelLength
    CurStart = txtName.SelStart
    [/code]

    And in the change event:

    [code]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
    [/code]

    Hope this helps! (hope it works...)
    KDL
  • doofusboydoofusboy Posts: 256Member
    : : 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:
    :
    : [code]CurLen = Len(txtName.Text) - txtName.SelLength
    : CurStart = txtName.SelStart
    : [/code]
    :
    : And in the change event:
    :
    : [code]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
    : [/code]
    :
    : 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?
  • KDivad LeahcimKDivad Leahcim Posts: 3,948Member
    : 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...
  • PavlinIIPavlinII Posts: 404Member
    : : 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
    [code]
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Variant [green]'For Each variable must be Variant[/green]
    For Each myCell In Target
    [green]'MsgBox myCell.AddressLocal[/green]
    mySpecificControlOfCell (myCell) [green]'Do whatever you want, myCell behaves like cell[/green]
    Next myCell
    End Sub[/code]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
«1
Sign In or Register to comment.