Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Send data to open worksheet (Excel XP)

    I'm attaching the final version of my names data base sheet that you all helped me with some weeks back.

    At the top there are 2 control buttons. Pressing one sends the selected name below to my invoice, pressing the other sends it to my quote.

    I thought as a final cleanup/simplification it would be nice to have only one control button. It would send the name/address data to the inovice OR quote, depending upon which one is open. I'd have to say that 99 percent of the time it would be only one or the other.

    Any suggestions? Or am I getting to fussy here !!!

    Thanks,
    BH
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send data to open worksheet (Excel XP)

    Insert the following code at the beginning of the macro for the button you want to keep.

    Dim strName As String
    Dim win As Window
    On Error Resume Next
    strName = "AAQuote.xls"
    Set win = Windows(strName)
    If win Is Nothing Then
    strName = "AAInvoice.xls"
    Set win = Windows(strName)
    If win Is Nothing Then
    MsgBox "Neither AAQuote nor AAInvoice is open.", vbCritical
    Exit Sub
    End If
    End If
    On Error GoTo 0

    Replace all further occurrences of Windows("AAQuote.xls") or Windows("AAInvoice.xls") with win, e.g.

    win.Activate

    Notes:
    1) The code could be made more efficient by not activating workbooks and selecting cells, but that is a different matter.
    2) If both windows happen to be open, the name will be sent to the first one mentioned in the code (AAQuote.xls)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Send data to open worksheet (Excel XP)

    Hans,

    As always you just blow me away !!! Thanks.....worked like a charm.

    Double buttons worked but take that one extra momentary mental step to think about which form is open. This makes it just a tad simpler to use.

    Thank you yet again!
    BH

Posting Permissions

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