Pause Macro to Edit Spreadsheet

[tcnt9176]

Hello All,
I am trying to come up with a way for my macro to stop if there are any #N/As after a vlookup so that the user can edit the sheet referenced in the vlookup so that there will ultimately not be any #N/As returned.

In researching the only way I see to do that is by using a form, which I am a novice at.

I set up a very simple userform that I have pop up if there are any N/As with stops the script but does not allow for any editing while the userform is there. I tried changing the modal to false which allowed editing but did not stop the macro from running.

Does anyone have any ideas of a way of stopping the macro while the user edits and then clicks on the userform to continue the macro?

 

[shg]

That's not a lot to go on. Maybe ...

Sub x()
    Dim rErr As Range
    
    On Error Resume Next
    Set rErr = Range("A1:A10").SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    
    If Not rErr Is Nothing Then
        rErr.Select
        MsgBox "Fix these errors and try again ..."
        Exit Sub
    End If
    
    ' carry on ...
End Sub

 

[tcnt9176]

I have the script to identify the N/As from the vlookup. If I have a msgbox, the user cannot edit the spreadsheets until they click ok and then the macro starts back. Here is the code. It works fine up until I need the macro to stop so the user can edit the spreadsheet before proceeding through the remainder of the code.

    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(C[-1],'[Assignments Dispostions.xls]Client Bump HDS'!C2:C3,2,FALSE)"
    LastRow = Range("A65536").End(xlUp).Row
    Range("C2").AutoFill Destination:=Range("C2:C" & LastRow), Type:=xlFillCopy
    
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C1").FormulaR1C1 = "Name"
    Range("A1").Select
    Selection.AutoFilter Field:=3, Criteria1:="<>#N/A" _
        , Operator:=xlAnd, Criteria2:="<>CBS"
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.Height <> 0
    ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    
    Set cell = Columns(3).Find(What:="#N/A", Lookat:=xlPart)
    If Not cell Is Nothing Then
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    UserForm1.Show
    End If

 

[tcnt9176]

Does anyone have any ideas on this one. It would really help me out!!!

 

[Snakehips]

Why not have your code, on finding an #N/A, display an input box? This could prompt the user for the desired input which the code will then direct to your lookup table.

Redirect your errors to say

On Error Goto FixError

If you get anN/A generated at Range T54 for example.

FixError:

RevisedData = InputBox("We have found an error in the database lease update with a valid value","Bad Lookup - Please Update")

Sheets("mySheet").Range("T54").Value = RevisedData

On Error Goto 0

Goto BackToCode


Above is not strictly correct code but should give you an idea?

 

[tcnt9176]

I tried this and also have researched on my own for a few days and still cannot get the desired outcome. I need something that will stop the macro, allow the user to edit another spreadsheet tied to vlookups & wait until the user is finished editing to continue.

Any other ideas?

 

[villy]

Hello,
What I think you need here is that when it encounter an error #N/A for example then
display a message "Please correct the error then run again." add the code Exit Sub then it will stop the macro, you can edit the spreadsheet then re-run your code. This time it should run without any errors.
hope it helps in any way.
Thanks

 

[Jaafar Tribak]

1- Amend your existing code as follows : (Lines in red are added)

'....

  Set Cell = Columns(3).Find(What:="#N/A", Lookat:=xlPart)

 If Not cell Is Nothing Then
   Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,Header:=xlYes,OrderCustom:=1,MatchCase:=False,Orientation:=xlTopToBottom,DataOption1:=xlSortNormal
   [B][COLOR=Red]Dim oForm as UserForm1
   Set oForm = New UserForm1[/COLOR][/B]
 End If

2- Add a commandButton to your form. This commandbutton (CommandButton1) is there to allow the user to resume the Macro after they have finished editing the worksheet.

Place this code in the UserForm Module :

Private bResumeMacro As Boolean

Private Sub UserForm_Initialize()

    bResumeMacro = False
    Me.Show vbModeless
    Do
        DoEvents
    Loop Until Columns(3).Find(What:="#N/A", Lookat:=xlPart) Is Nothing _
    And bResumeMacro = True

End Sub

Private Sub CommandButton1_Click()
    
    If Columns(3).Find(What:="#N/A", Lookat:=xlPart) Is Nothing Then
        bResumeMacro = True
        Unload Me
    End If
    
End Sub

 

[Jaafar Tribak]

Add this code to your userform module as well :

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   If Not bResumeMacro Then Cancel = True
End Sub