Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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


    Code:
    '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
    Code:
    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,205

    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
    68

    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
    68

    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.
    Code:
    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