Msgbox depending on cell value

[Yulyo]

Hello all,
I have a small problem and I just can't make it work:
- in cell a1 I have a drop-down list with "yes" / "no"
- I need a solution that, if the cell a1 is yes, to make me write one number in cell b2 - it should not let me continue without inserting the number in b2
- I was thinking about vba, tried something like this, but I don't know how to make the link between vba and cell a1/b2
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">
Sub test()</code>
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">
</code>
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">Dim myValue As Variant</code>
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">If ActiveSheet.Range("A1").Value = "YES" Then</code>
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">myValue = InputBox("please insert the number")</code>
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">Range("b2").Value = myValue</code>
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">End If</code>
<code spellcheck="false" class="listtype-code listindent1 list-code1 lang-php">End Sub


I am opened to any other/simpler solution.
Any help would be greatly appreciated.
Thank you </code>

 

[baitmaster]

nothing jumps out that's wrong with the code, except the means of making it run. You would need an Event to trigger it automatically. There are a number of standard things that can trigger macros to run, in your case you'd want the "worksheet change" event. Call this by adding the following code to the worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("A1")) Is Nothing Then Call test


End Sub

I wouldn't normally force my Users to enter something, but would rely on educating them, and having an error message that flags that something is missing. Otherwise you're at the mercy of them having macros enabled

 

[baitmaster]

You might also be having problems with case sensitivity. Avoid this by writing

Option Compare Text

at the top of your code module

 

[Yulyo]

Hello baitmaster and thank you for your answer.
In the meen time i was thinking about a simpler (??) solution:
- when the user will click save, a message will popup asking for the code.

Until now i managed to do this, but it will save the file even if the user will click OK or Cancel without adding the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myValue As Variant


If Application.Sheets("Export").Range("C6").Value <> "" And Application.Sheets("Export").Range("c27").Value = "DA / YES" And Application.Sheets("Export").Range("c29").Value = "" Then
myValue = InputBox("Please enter the code:")
Else
Range("c29").Value = myValue
End If

Any chance you could help me with a loop, to ask the same question until the user writes anything in the inputbox and of course not to save the file if the user click cancel? (if the user will click cancel, the vba should exit without saving and without closing the file)
Again, thanx a lot for the previous answer. It answered my original question, it is me that changes his mind

Thank you

 

[baitmaster]

Not entering anything, OR selecting Cancel, returns the same "" value from your inputbox - therefore I haven't created a loop as there's no exit from it using the Cancel button. You could easily put a named location in your code and ask the User if they want to try again, but this doesn't really add anything. Note the Boolean named Cancel in the code heading, changing this to True stops the Save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myValue As Variant


If Application.Sheets("Export").Range("C6").Value <> "" And Application.Sheets("Export").Range("c27").Value = "DA / YES" And Application.Sheets("Export").Range("c29").Value = "" Then
    myValue = InputBox("Please enter the code:")
    
    If myValue = "" Then
        MsgBox "a valid value must be entered", vbExclamation, "file not saved"
        Cancel = True   ' stops the file saving
        Exit Sub        ' stops this routine in case more detail to be added later
    Else
        Range("c29").Value = myValue
    End If


End If


End Sub