vba prompt to edit

[tcnt9176]

I have an If statement that if it is true, I need the macro to stop so that the user can update a spreadsheet referenced in a vloopup so that they will not have any N/A's and resume after the user is finished. Is that possible?

Thanks everyone!!

    On Error Resume Next
    Selection.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    If ActiveCell.Text = "#N/A" Then
    Range("A1").Select
    Selection.AutoFilter Field:=3, Criteria1:="#N/A"
    MsgBox "Please Review N/A's"
    End If

 

[iliace]

You could add this to the Worksheet_Change event. Depending on how you process other events, you might make a trigger variable that either sets a flag or a variable that indicates within the Worksheet_change procedure that user is currently updating #N/A values. If it detects that mode, do the Find again, and if no #N/As are found, prompt the user again whether it's ok to resume execution.

 

[tcnt9176]

I've never used a Change Event. Do you have a brief explanation on how they work or a link to a good guide to use for this? Thanks!!!

 

[iliace]

Worksheet_Change event is a built-in procedure in your Sheet code module (your macro is probably in a standard Module). If you double-click on the Sheet module (e.g. Sheet1) you will see two drop-down boxes at the top of the code pane, looking like this:

(General)     (Declarations)

First, click on (General) and change it to Worksheet. Next, click on (Declarations) and select Change. VBA will create a procedure that looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

There you put the code that you want to run each time a change occurs in the worksheet. Be careful because if you add code that changes the worksheet, the event will trigger again and you'll be stuck in a recursion loop.

Now, within this new event procedure, you can put some code like this:

On Error Resume Next
   If Me.Columns(3).Find(What:="#N/A", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False) Is Nothing Then
     If MsgBox("You removed all #N/As. Resume?", vbYesNo) = vbYes Then
[B][COLOR="SeaGreen"]         ' go back to what you were doing before[/COLOR][/B]
     End If
  End If
On Error GoTo 0

This article here will provide just about everything you could want to know (probably more) about Excel VBA events.

http://www.cpearson.com/excel/Events.aspx

 

[tcnt9176]

Yeah. I have only used the standard moduel. I am a little confused but will read the info you linked to. Thanks for your help!!!

 

[iliace]

I think it's more important to grasp the workflow that you're going for. I don't know what the entirety of your project does, so here's a basic outline.

1. User starts the macro, which begins at an "entry point".
2. The macro does some stuff, and determines that it needs some input from the user (like the #N/A values).
3. Macro stops ("break point") and notifies user that it needs input (fixing the #N/A values).
4. As the user changes the values in the worksheet, the macro keeps checking to see if any #N/A values still remain.
5. Once the macro determines that all the #N/A values are gone, it asks user whether it's ok to continue running (i.e. the new values are all correct).
6. After user confirms that it's ok, the macro continues where it left off ("re-entry" or "resumption" point).

The Worksheet_Change event occurs during steps 4 and 5. The advantage of this approach is the code has some control of when it continues running.

You might be in a situation where the user will not always know what the data should be in place of the #N/A error. In that case, you may want a different approach - perhaps a non-modal form that allows the user to click "OK let's keep going"; that means you have to design the form, its events, and so forth. Another solution might be a "resume" macro that the user has to run manually - that method would count on the user remembering/knowing how to continue, AND the resume macro would need to check that it's picking up where the code left off.

One good approach that's both simple and might work for your situation is having the macro check for #N/A right at the entry point, and if there is are #N/As, it would exit and inform the user that it needs to be rerun once the errors are corrected.

The options for what you can do here are almost limitless. There is a lot of factors here that will determine what you should do, falling into three broad categories:

1. How much does your user know about the process in which your macro is involved? Does he/she need a lot of guidance, or have solid knowledge of when and how the code should run?
2. Where do the #N/A errors come from? Is it possible to check for them at the entry point, or do they occur over the course of the macro? In the case of the latter, is it possible to avoid them?
3. If the #N/A errors do require user input in the middle of the macro, how long will they take to correct?

Some stuff to think about.