-1

I want to tell the user the value entered is unavailable.

The question below bears no relevance to the problem (I can't choose the available options from a drop down - it's more than that).

Question in Cell B25: "Do you want this available in blue"
Responses: "Yes" , "No"

If Range("B35").Value = "Yes" Then
    VBA.Interaction.MsgBox "Please contact Product Manager", , "Not available"
End If

Choosing "No" should not stop the rest of the code from executing, nor should it stop the user from completing the rest of the form.
The message box appears and OK can be clicked. It then appears again for each entry in the form as the value of B35 hasn't changed.

I tried different ways to exit the routine, and even put the routine in a different module.
I think a helper cell could be useful.

Community
  • 1
  • 1
jars104
  • 1
  • There really isn't enough here to answer this, an if statement will work but I'm guessing your logic is structured incorrectly. – Warcupine Jan 15 '21 at 18:17
  • you need to qualify the IF statement with a bool that represents whether you have already checked this cell and raised the error – KacireeSoftware Jan 15 '21 at 23:38

1 Answers1

0

Without seeing your actual sheet, and only going off of the simple example you have provided, I can only say that you need to qualify your IF statement with a variable which represents whether you have already raised this error or not. For example:

Option Explicit

Public bChecked35 As Boolean


Sub abc()
    If Range("B35").Value = "Yes" And Not bChecked35 Then
        MsgBox "Please contact Product Manager", , "Not available"
        bChecked35 = True
    End If
End Sub

Notice that I defined a single boolean variable to represent that one cell. I defined the variable at the module level, so as long as the workbook is open, the variable remains in scope.

In your case, you need to determine what level of scope the variable should have...when to "reset" it, etc..

If you have a lot of cells to validate, maybe you want a procedure to create a collection of bools ...

KacireeSoftware
  • 798
  • 5
  • 19