Excel VBA Wait


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?

Who is Participating?
Zack BarresseConnect With a Mentor CEOCommented:
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??
Wayne Taylor (webtubbs)Commented:
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)


Wayne Taylor (webtubbs)Connect With a Mentor Commented:
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
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.