Results 1 to 12 of 12

Copy double-clicked cell to next empty row in different sheet with each double-click

Threaded View

  1. #1
    Registered User
    Join Date
    08-29-2012
    Location
    Calfironia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Copy double-clicked cell to next empty row in different sheet with each double-click

    I am trying to create an inventory system.

    I have written code so that if you double click a product (Sheet1), it adds the relevant information about the product (columns 1,2,3, 9, 10) into the purchase order (Sheet 4).


    I also have code so that it only adds a secondary product if it is from the same supplier (as seen by the value of Row 22, Column 6).

    I need to find a way such that the first product double clicked is added to Row 15, Column 3 of Sheet 4. If another product is double-clicked a new row is inserted below row 15 in Sheet 4 and the relevant information is copied, etc...

    Any help would be great!

    This is what I have so far:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        'Override the default double-click behavior with this function.
        Cancel = True
        
        'Declare variables.
        Dim wks As Worksheet, xRow As Long
        
        'If an error occurs, use inline error handling.
        On Error Resume Next
        
        'Capture double-clicked cell
        Target.Select
        'Capture double-clicked row
        seRow = Target.Row
        
    
        seCol = 0
        'Insert in Row 15 of Sheet4
        xRow = Sheet4.Cells(Sheet4.Rows.Count, 1).End(xlUp).Row + 1
        'For first product added to purchase order
        If Sheet4.Cells(22, 6) = "" Then
            Sheet4.Cells(22, 6) = Cells(seRow, seCol + 5)
            Sheet4.Cells(xRow, 3) = Cells(seRow, seCol + 2)
            Sheet4.Cells(xRow, 9) = Cells(seRow, seCol + 6)
            Sheet4.Cells(xRow, 10) = Cells(seRow, seCol + 9)
            Sheet4.Cells(xRow, 1) = Cells(seRow, seCol + 7)
            Sheet4.Cells(xRow, 2) = Cells(seRow, seCol + 8)
            
            MsgBox "New Order Started: Producted Added to PO"
        
        'If trying to add another product to purchase order, check to make sure the vendor is the same
        Else
            If Sheet4.Cells(22, 6) = Cells(seRow, seCol + 5) Then
        
                Sheet4.Cells(xRow, 3) = Cells(seRow, seCol + 2)
                Sheet4.Cells(xRow, 9) = Cells(seRow, seCol + 6)
                Sheet4.Cells(xRow, 10) = Cells(seRow, seCol + 9)
                Sheet4.Cells(xRow, 1) = Cells(seRow, seCol + 7)
                Sheet4.Cells(xRow, 2) = Cells(seRow, seCol + 8)
        
                MsgBox "Product Added to PO"
            
            Else
                MsgBox "This item is not from the same vendor"
        End If
        End If
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
       
        End Sub
    Last edited by erhathaway; 08-21-2013 at 08:49 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Load web image when cell holding URL is clicked/double-clicked
    By jchamber00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2013, 09:13 PM
  2. Double Click cell to jump to sheet
    By neodjandre in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 04-11-2008, 10:18 AM
  3. How to copy the value of cell into clipboard by double click
    By lassang01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2007, 05:01 PM
  4. [SOLVED] how to copy on double-click the cell value to Clipboard
    By cyrille in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-15-2005, 06:45 PM
  5. [SOLVED] how to copy on double-click the cell value to Clipboard
    By cyrille in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-11-2005, 08:06 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1