-2

VBA beginner here. I apologize for not being able to provide a code snippet, but I'm seeking a script that works as follows:

  1. Check whether the value inputted in a cell (say A1) appears in an array of cells (say B1:B100).
  2. If the value inputted to A1 does NOT appear in B1:B100, show a MsgBox with Retry/Cancel buttons that warns the user that their input is invalid.
  3. If the user selects Retry, RE-ACTIVATE cell A1 so that the user must re-input a different value. If the user selects Cancel, leave cell A1 blank and exit the cell.

I am seeking a VBA solution only, NOT Excel's built-in Data Validation feature (for a number of reasons).

Thank you everyone!

jlukes1391
  • 3
  • 1
  • Hello :) welcome to SO. It would be nice if you could edit your question adding the code you have tried and the specific problems you are dealing with. Here are some VBA docs you may be interested in: MsgBox --> https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/msgbox-function Find --> https://learn.microsoft.com/en-us/office/vba/api/excel.range.find – q0mlm Mar 28 '20 at 23:47

1 Answers1

0

Try this for a starting point. If you insert a "Shape" and assign this macro to it. The shape can be used as a button to run the macro when clicked on.

Sub Macro1()

Dim v1 As Variant
Dim loop1, msgbutton As Long

v1 = Cells(1, 1).Value 'read the value in cell A1

For loop1 = 1 To 100
    'compare v1 with cells range B1 to B100
    If v1 = Cells(loop1, 2).Value Then Exit For
Next loop1

'if there is a match the loop will exit before reaching 101
If loop1 > 100 Then
    'no match so make a message box
    msgbutton = MsgBox("The data is invalid.", 5, "Box Title")
    If msgbutton = 4 Then 'retry
        Cells(1, 1).Value = Empty
        Cells(1, 1).Select
    Else 'cancel
        Cells(1, 1).Value = Empty
    End If
Else 'it's a match
    Stop 'your code here
End If

End Sub
Nibby
  • 16
  • 1