Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1961
  • Last Modified:

Excel VBA Wait

Hello!

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?

Thanks!!
0
teedeemarie
Asked:
teedeemarie
  • 2
2 Solutions
 
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)

Regards,

Wayne
0
 
Zack BarresseCEOCommented:
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??
0
 
Wayne Taylor (webtubbs)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
0
 
qwaleteeCommented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now