Drop down menu proplem not giving me pop up message

[curness]

Drop Down and Popup Message
Hi There

I've already found useful VBA codes to get a popup message even for drop down menus. The spread sheet I'm working on it does not work with these helpful formulas. If for example you select "no" on drop down and the cell number might be number 2, there is no pop up. However if you select target cell somewhere else on formula not to do with drop down the pop up will appear. So its as though the drop down connect to the cell target ignore any numbers that are change on the drop down menu. However if I manually type over the cell link on the drop down cell it will work to get the pop up. I want it to work using the drop down menu. Hope this makes sense and is it something simple I've missed?

here is an example of a formula which I'm sure is fine but it ignores every time if I'm go by the cell link in the drop down properties. If I don't sue a drop down then it works in a different cell

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") > 0.5 Then
MsgBox "Discount too high"
End If

END SUB

HERE'S another again does not work with drop down



Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
If Target.Value = "X" Then MsgBox "Aaargh!", vbExclamation
End Sub

I've also tried data validation and same thing happens and does not work with a drop down on my spread sheet in the cell link.

Thanks


Robert

[Bernie Deitrick]

You are using a control with a linked cell. Changing that control (and the value of the linked cell) doesn't actually fire the worksheet's change event, which requires direct entry into a cell.

So either:

1) in another cell, use a linking formula to that cell, and use the worksheet calculate event to check the value of the cell with the formula.
2) get rid of the control, and use Data Validation with the "List" and dropdown options on that cell. Then the cell will change, and the change event will fire.

[curness]

first option works like a treat. Thank you

[curness]

Sorry I am now getting the same message boxes that will popup by default 15 times when i'm running another macro code to clear contents. How can I stop this.

[Bernie Deitrick]

As the first line in the other code, use

Please Login or Register  to view this content.

and as the last line, as well as in any error handler:

Please Login or Register  to view this content.

Another option is to store the value for comparison somewhere and only execute the code if the value changes, like

Please Login or Register  to view this content.

[curness]

Bernie thanks for the help but is it possible or anyone you can use my first example and embed this correctly as won't work for me.

[Bernie Deitrick]

Give this book a try....


Book1.xlsm

[curness]

Thanks for the advise but I'm trying to do something like the following. Problem is if both criteria = 2, on the first one it pops up find but on the second one I get two pop up messages one after the other. How can I stop this if someone can please amend my formula.

Private Sub Worksheet_Calculate()
If IsNumeric(Range("c63")) Then
If Range("c63").Value = 2 Then
MsgBox "You should only select this option if the eligible rent is exempt in certain accommodations such as supporting housing or if the 13 week protection rule applies. ", vbInformation
End If
End If

If IsNumeric(Range("b52")) Then
If Range("b52").Value = 2 Then
MsgBox " supporting housing or if the 13 week protection rule applies. ", vbInformation

End If
End If
End Sub

[Bernie Deitrick]

You need to do some other work, including storage cells.

Book1.xlsm

[curness]

Thank you very much - you are a star and I have learned something again.

[curness]

can someone please tell me how to add a third line to the following code if I want Storedvalue3 say cell A1?

If Range("Storedvalue").Value = Range("C63").Value And _
Range("Storedvalue2").Value = Range("B52").Value Then Exit Sub

Thank you

[Bernie Deitrick]

I hope you can see the pattern for adding more....

Please Login or Register  to view this content.

[curness]

works perfect - thank you once again.