Disable lookup autofill

Hi

This may sound weird.

I have a lookup table, using which I lookup user name and populate their location on the main sheet (using VBA code).

For names missing in the lookup table, I get #N/A, which is expected.

Next, I loop through all #N/A's on the main sheet, and add their corresponding user names in the lookup table.

Here, I want to stop my program, and pop up a msgbox to add locations for user names newly added in the lookup table.

But, as soon as the names are added to lookup table, their locations are updated as 0 on the main sheet.
I think this happens due to lookups refreshing automatically, and substituting missing locations with zero's. (Remember, user has not yet got a chance to update these lissing locations in lookup)

Is there a way to stop this lookups refresh until user manually updates loctions and clicks a button?

I will try to create a sample sheet with this scenario, and upload it later today, but meanwhile, if anyone has any solution, I will appreciate it very much.

tia
Ajay

Hi Ajay;
I think that the only way that you can prevent the refresh is to set calculation to manual.
I don't think that is a good idea. If you turn automatic calculation off and the user doesn't put something into N8 (which presumably would set calculation back to automatic), then calculation is still set to manual.
I think this is a better solution.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("M:M")) Is Nothing Then
        With Target.Offset(0, 1)
            If .Value = "" Then
                Application.EnableEvents = False
                .Value = " "
                Application.EnableEvents = True
            End If
        End With
    ElseIf Not Intersect(Target, Columns("N:N")) Is Nothing Then
        With Target
            If .Value = "" Then
                Application.EnableEvents = False
                .Value = " "
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub

Or you could change the formula in B5 to

=IF(VLOOKUP(A5,M:N,2,FALSE)=0,"",VLOOKUP(A5,M:N,2,FALSE))

If you go this route and have a Huge list, you might do this

Z5 : =VLOOKUP(A5,M:N,2,FALSE)
B5 : =If(Z5=0,"",Z5)
And hide column Z (or whatever column suits you).

that way vlookup only executes once so the calculation is faster

Thanks, Foxguy.
I used a work around wherein I programatically pre-poulate the cell B5 with text "#N/A" so that it doesn't default to zero after I add "D" to lookup table.

I know the solution is as weird as the problem, but It works for me.

Thanks very much
Ajay

Hi snb;

Originally Posted by snb

It's only a matter of:

    ActiveWindow.DisplayZeros = False

You're right. I completely forgot about this, and it could easily be a better solution than mine.