Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Insert text from clipboard to cell on currently selected row

  1. #1
    Join Date
    3rd September 2010
    Posts
    7

    Insert text from clipboard to cell on currently selected row

    I have a worksheet using Excel 2002 that will automatically insert a text string from the clipboard that works but the problem is that I need it to insert the text on the line that I have highlighted and it will only do that on the first line and then it wants to re-write the line above it instead of inserting the text on the line where I want it (may be the second or third line down with a blank line in-between). Here is the Activate & Insert code so maybe someone can tell me what is wrong?
    Thanks, Gene

    Here is the type of string that will be on the clipboard -- A17; Bill Smith; Mary Smith; Phoenix; AZ; Bigfoot; 21FW; 2003; 8 ; 15; 19; justme@mysite.com ; Joey


    VB:
     'Summary: Returns text from the clipboard as a string.
     
     'Does the clipboard contain format?
    Private Declare Function IsClipboardFormatAvailable _ 
    Lib "User32.dll" _ 
    (ByVal wFormat As Integer) As Long 
     
     'Open the clipboard
    Private Declare Function OpenClipboard _ 
    Lib "User32.dll" _ 
    (ByVal hwnd As Long) As Long 
     
     'Get a pointer to the formatted data
    Private Declare Function GetClipboardData _ 
    Lib "User32.dll" _ 
    (ByVal wFormat As Integer) As Long 
     
     'Close the clipboard
    Private Declare Function CloseClipboard _ 
    Lib "User32.dll" () As Long 
     
     'Finds first double Chr$(0) in a string
    Private Declare Function lstrlen _ 
    Lib "kernel32.dll" _ 
    Alias "lstrlenA" _ 
    (ByVal lpString As String) As Long 
     
     'Returns a pointer to the clipboard data in memory
    Private Declare Function GlobalSize _ 
    Lib "kernel32.dll" _ 
    (ByVal hMem As Long) As Long 
     
     'Prevent the clipboard data from being overwritten
    Private Declare Function GlobalLock _ 
    Lib "kernel32.dll" _ 
    (ByVal hMem As Long) As Long 
     
     'Free the memory used by the clipboard data
    Private Declare Function GlobalUnlock _ 
    Lib "kernel32" _ 
    (ByVal hMem As Long) As Long 
     
     'Copy clipboard data from protected memory to a string buffer
    Private Declare Sub MoveMemory _ 
    Lib "kernel32" Alias "RtlMoveMemory" _ 
    (ByVal strDest As Any, _ 
    ByVal lpSource As Any, _ 
    ByVal Length As Long) 
     
     
    Function GetClipboardText() As String 
         
        Dim DataSize As Long 
        Dim hClip As Long 
        Dim hData As Long 
        Dim pData As Long 
        Dim Ret As Long 
        Dim strText As String 
         
        Const CF_TEXT As Long = 1& 
         
        Ret = OpenClipboard(0&) 
        If Ret = 0 Then 
            MsgBox "Clipboard is in use." 
            Exit Function 
        End If 
         
        Ret = IsClipboardFormatAvailable(CF_TEXT) 
        If Ret Then 
            hData = GetClipboardData(CF_TEXT) 
            If hData Then 
                DataSize = GlobalSize(hData) 
                strText = Space$(DataSize) 
                pData = GlobalLock(hData) 
                Call MoveMemory(strText, pData, DataSize) 
                strText = Left$(strText, lstrlen(strText)) 
                Call GlobalUnlock(hData) 
            End If 
        Else 
            MsgBox "There is No Text on the Clipboard." 
        End If 
         
        Ret = CloseClipboard 
        GetClipboardText = strText 
         
    End Function 
    
    
    VB:
    Sub InsertText() 
         
        Dim Rng As Range 
        Dim RngEnd As Range 
        Dim Text As String 
         
        Set Rng = Range("A2:M2") 
        Set RngEnd = Cells(Rows.Count, Rng.Row).End(xlUp) 
        Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd).Offset(1, 0)) 
         
        Text = GetClipboardText 
        If Text <> "" Then Rng.Rows(1).Cells.Value = Split(Text, ";") 
         
    End Sub 
     
    Sub ActivateInsertKey() 
         
        Application.OnKey "{INSERT}", "InsertText" 
         
    End Sub 
    
    
    Last edited by AAE; September 3rd, 2010 at 06:23. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Need Macro help please

    Hello genestoy,

    Welcome to Ozgrid.

    Thread titles are important for obtaining good search results and should be written with this thought in mind -- that is, the title should be "search friendly", meaning a search using YOUR title as the search terms will yield relevant results. Thread titles should concisley and accurately describe the content of your thread.

    Your title of "Need macro Help Please" does not adequately describe your thread and is of not help to others searching the forum for a solution to a similar problem.

    Click the EDIT POST button below your thread, then click Go Advanced and revise the thread title to something that describes your thread/objective.

    Take some time to read the Forum Rules so that you can avoid infractions that may impact your posting privilege. Thanks.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    1st September 2010
    Posts
    62

    Re: Need Insert Text Row Macro help please

    If you want it to be on the currently selected row then why all the work with Rng and RngEnd? Would not Activecell.Row put you on the highlighted row?

    Your example code will only fill (from what I can see) rows 2 and 3 because of the integer value in the offset. If you had used RngEnd-1 instead of the 1 the code would fill the first empty row after 2.
    Last edited by rikcando; September 3rd, 2010 at 06:14. Reason: oops, fixed a typo

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    3rd September 2010
    Posts
    7

    Re: Need Insert Text Row Macro help please

    Thanks for the reply but I am a novice at Excel and did not write the above code so could you explain or show me in the code what needs to be changed?
    Thanks for your help
    Gene

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st September 2010
    Posts
    62

    Re: Need Insert Text Row Macro help please

    If you are looking to have the text placed into the row that you have highlighted, the following code will do that.
    VB:
    Sub InsertText() 
         
        Dim Rng As Range 
        Dim RngEnd As Range 
        Dim Text As String 
         
        Set Rng = Range("A" & ActiveCell.Row & ":M" & ActiveCell.Row) 
         '      Set RngEnd = Cells(Rows.Count, Rng.Row).End(xlUp)
         '      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd).Offset(RngEnd.Row - 1, 0))
         
        Text =GetClipboardText 
        If Text <> "" Then Rng.Rows(1).Cells.Value = Split(Text, ";") 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    3rd September 2010
    Posts
    7

    Re: Need Insert Text Row Macro help please

    rikcando,
    You are a life saver!!!! That works perfectly and I thank you so much!
    Gene

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    3rd September 2010
    Posts
    7

    Re: Insert text from clipboard to cell on currently selected row

    I need a little help with this again, first it has been working just fine but I now find I need one of the columns (the "I" column) to do a counta which I have already set up but the problem is that the insert above stores the number in the I column as text so the counta won't work with text. How do I make the insert store that one column as a number for the counta? Or if that is too hard to do what code would I use fof that "I" column to total the entries that have numbers in them, not a addition of all the numbers but a total of the cells with numbers in them but excluding any cells with text? Thanks again for any help!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    3rd September 2010
    Posts
    7

    Re: Insert text from clipboard to cell on currently selected row

    UPDATE: I figured it out so no help needed, thanks!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: September 26th, 2007, 04:22
  2. Open File Macro: Same Directory As Macro Workbook
    By xsubzeroz in forum EXCEL HELP
    Replies: 3
    Last Post: November 17th, 2006, 01:58
  3. Replies: 2
    Last Post: October 3rd, 2006, 06:51
  4. Replies: 3
    Last Post: October 3rd, 2006, 03:20

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno