Excel VBA Wait

Posted on 2007-10-12
Last Modified: 2013-12-18

I'm trying to figure out how to code a process that tells excel to wait for data to be inserted into sheet1 starting in 'A5'. before running a macro. This is part of an export from a Lotus Notes database. I've tried a loop coupled with a wait but it won't allow the insert or paste to take place while its running. The code I have now runs when the spreadsheet is opened. Any suggestions?

Question by:teedeemarie
    LVL 46

    Expert Comment

    by:Wayne Taylor (webtubbs)
    Hi teedeemarie,

    You can make use of the Worksheet events, specifically, the Worksheet_Change() event....

        Private Sub Worksheet_Change(ByVal Target As Range)
            If Target.Address = [A5].Address And Not MacroRun Then
                'run your macro
            End If
        End Sub

    This will run your macro when cell A5's value changes. Paste the code into the worksheets module (right-click tab > View Code)


    LVL 14

    Accepted Solution

    In the individual worksheet module, as Wayne points out, you can use a worksheet event.  I would use slightly differently though...

    Option Explicit

    Private Const strAddy As String = "A5"

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        'Exit if only one cell is selected
        If Target.Cells.Count > 1 Then Exit Sub
        'Exit if cell being changed is not the right cell
        If Target.Address(0, 0) <> strAddy Then Exit Sub
        'Do something here
        MsgBox "You changed " & Target.Address & ": " & Target.Value, vbInformation
    End Sub

    Plus I'm not really sure what "MacroRun" is, maybe something Wayne uses globally perhaps??
    LVL 46

    Assisted Solution

    by:Wayne Taylor (webtubbs)
    Oops, that should have been deleted....

        Private Sub Worksheet_Change(ByVal Target As Range)
            If Target.Address = [A5].Address Then
                'run your macro
            End If
        End Sub
    LVL 31

    Expert Comment

    Is the Lotus Notes export being handled by a LotusScript export routine? If so, why not just add your VBA to the LotusScript export routine.  The language is nearly identical, and using OLE Automation (which is probably already taking place in the LotuScript) makes all those Excel VBA objects avaialble.

    The reason I suggest this is that #1) it is more efficient, and #2) it avoids having the Worksheet)Change event firing all the t ime, wasting cycles on all the changes it is supposed to ignore.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now