Conditional Appearance of msgbox

**Anshu** · 08-29-2020, 10:35 AM

Case:
There are 3 cells- "P20", "U20" and "Z20".
"Z20" contains the formula =IF(P20<U20,"??","")

I've this VBA in the sheet

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCell As Range, Rg As Range
    On Error Resume Next
    Set Rg = Application.Intersect(Target, Range("Z20"))
    If Not Rg Is Nothing Then
        For Each xCell In Rg
            If xCell.Value = "??" Then
                MsgBox "Entry Error"
                Exit Sub
            End If
        Next
    End If
End Sub

Private Sub Worksheet_selectionChange(ByVal Target As Range)
    Dim xCell As Range, Rg As Range
    On Error Resume Next
    Set Rg = Application.Intersect(Target, Range("Z20"))
    If Not Rg Is Nothing Then
        For Each xCell In Rg
            If xCell.Value = "??" Then
                MsgBox "Entry Error"
                Exit Sub
            End If
        Next
    End If
End Sub

Problem:
Msgbox appears only when I select the Cell "Z20" (If it contains "??")

My requirement:
1. Msgbox should appear immidiately after appearance of "??" in the cell "Z20"
That is, whenever a value is entered in the cell "P20" or "U20", and P20<U20 (as the formula set in cell Z20), immidiately the msgbox should appear.
2. Also, the msgbox should appear when the cell Z20 contains "??" and the cell Z20 is selected. (It is working currently)

Please find the attachment below for more clarity.

Many Thanks!

**DocAElstein** · 08-29-2020, 02:32 PM

Hi

Please always give as much information as possible about where you get macros from, and how it came to be. ( This is often very helpful to people trying to help with modifications to the program. If you are getting help from different places and do not tell us, then it can lead to people duplicating work for you unnecessarily. )

So the second macro is fine, Private Sub Worksheet_selectionChange(ByVal Target As Range)
You will see that it is triggered by

Code:

    Set Rg = Application.Intersect(Target, Range("Z20"))
    If Not Rg Is Nothing Then

What is happening there is that only
If the selected range , ( Target ) , and Z20 coincide ( cross / intersect)
will anything be done
That is fine for triggering when you selected Z20

In the first macro, which doesn’t currently work as you want it to, you are currently trying to trigger using exactly the same code lines. So that is no good. It can’t work if you selected a cell other than Z20

If I understand correctly, the cell Z20 may change as a result of you … whenever a value is entered in the cell "P20" or "U20", and P20<U20 (as the formula set in cell Z20), immediately the msgbox should appear….
So you are selecting P20 or U20 ,
I think it is best to forget the first macro as you have it that you currently have, and start again.

A working macro will be very simple and similar to what we have done together now a few times…
Remember, Target , is the range that you selected. ( Excel fills the variable, Target , with the range object that you selected )
That is probably the most imortant thing to remember in such coding. Allmost all of this sort of coding will use Target in one or more ways. Knowing about the Properties of the range object that is Target goes a long way to tell us what happened, and at what state things currently are.

Code:

Private Sub Worksheet_Change(ByVal Target As Range) '  https://excelfox.com/forum/showthread.php/2627-Conditional-Appearance-of-msgbox?p=14859#post14859
    If Target.Address = "$P$20" Or Target.Address = "$U$20" Then
        If Range("Z20").Value = "??" Then MsgBox Prompt:="Entry Error"
    Else
    ' a cell or cells other than  P20  Or  U20  where selected, so nothing is done
    End If
End Sub

Alan

**Anshu** · 08-29-2020, 03:57 PM

**DocAElstein** · 08-30-2020, 02:27 PM

Originally Posted by Anshu

…Sorry for not providing the complete information! The mistakes will never be repeated again……

This is not a big issue. It is no big deal. There are no formal rules about this. It is just my personal preference, that’s all. It is no big deal.

I can sometime learn from the other attempt, or get useful information from it.
More important is that I am often more able to determine at what understanding level the person asking the questions is at. So I can better Tailor my help to suit..

Originally Posted by Anshu

…, first of all, I try to search over internet as much as possible. Still, if I don't find the exact solution I want, then only I post my problems here. Generally, I avoid to post the same problem twice, or at multiple sites, until and unless, it's necessary to do so. And If I post anything again, at other site, I always try to include the link….

That sounds like a good attitude… but that is just my opinion…

Just some notes on the ... Cross posting and Cross Posting Rule.
For the person wanting help, there can be advantages to cross post secretly. So it is obviously tempting to do it.
In most of the English speaking forums it would be detected, since a lot of senior helpers give help at most forums.
As you found out yourself , sometimes it is detected: https://www.mrexcel.com/board/thread.../#post-5495983 https://www.mrexcel.com/board/thread...value.1135674/
It is not always detected. It sometime goes unnoticed.

I have mixed feelings about it. Generally I agree with the typical cross post rule, for the obvious reasons given typically of …”providing links as soon as you cross post so that other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered….
But sometimes in the past when someone has cross posted secretly elsewhere and obtained a solution independently to my solution, the alternative solution was very interesting to me. That solution would probably not have come about if the person asking for help had followed the cross posting rules…. In such case, for both me and the person asking the question, it was beneficial that the cross post was done secretly.
The other person giving help may have not answered if he knew of the cross post. But , like many older helping members, they answer hundreds or thousands of questions, almost from instinct, sharing the knowledge they have, but not benefiting from any question themselves, other than some imaginary feeling of having some position of authority or seniority, - like a child or computer game addict lost in some computer game.
I guess what he doesn’t know does not hurt him. When such people die, few notice, or care….. This is sad, but true. (Just recently I noticed a very senior member who had helped many thousands stopped posting at one of the major forums. I spent many weeks of investigatory search across several countries to find out that he had died. Nobody cares. Apart from me, nobody knows about him. Nobody other than me ever will know, or care about, all the details about him).

I think the most important thing is to give it some thought, that’s all. … at the end of the day, you make your choice, and live with the consequences..
Anyway, none of this is a big issue here at excelfox, not currently anyway
It is all not too important.

Just remember that at excelfox.com currently there are not many people helping.
I , for example, answer all the questions that I am able to. But I know only a small amount of the vast area that Excel and Excel VBA is. Mostly I know just a minute area of VBA. This area I do know quite well, in a small number of cases I know the subject better than anyone else, including anyone at Microsoft or even those that are responsible for the software.
Sometimes I only look in every 2-3 days. You will not often get a quick answer here. ( For example, I will have time tomorrow or the day after to look at your outstanding question ( https://excelfox.com/forum/showthrea...ple-Conditions ) )

At forums such as mrexcel.com and excelforum.com there are many hundreds of helpers active constantly. Sometimes you can get lots of great help very quickly. Sometimes you wont get any reply. Sometimes you may catch one of the many quirky frustrated psychotic mentally ill forum Moderators or Administrators on a bad day. It’s a bit varied.

**Anshu** · 08-30-2020, 05:25 PM

(I've seen your profile here https://www.tips.net/Authors/A0001_Allen_Wyatt.html)
I'm happy to have a conversation with such an amazing personality!!

I'm very new to excel as well as "Queries Poster", and therefore, I've never known about various rules and guidelines about posting and cross posting.

As you found out yourself , sometimes it is detected: https://www.mrexcel.com/board/thread.../#post-5495983 https://www.mrexcel.com/board/thread...value.1135674/

This is my first (and last perhaps) cross posts (with many mistakes like not including the link).

After that, I come to learn (mostly by you) that posting, too, is a "skill"!!

Most of the time (almost every time), here on excelfox, I'm provided a working solution and honest reply by a single person..."..."(understood)

When someone helps me by giving their time and knowledge, especially on demand, and in return, I'm unable to give him or her anything except a word of "thanks", it makes me to feel a little bit hesitated.

… at the end of the day, you make your choice, and live with the consequences..

Thanks for the line, which makes me smile.

**DocAElstein** · 08-31-2020, 03:29 PM

Lol.. ..

…. My name is Alan Elston, not Allen Wyatt. I am not Allen Wyatt! - It happens often that people think that I am Allen Wyatt when I post at Allen Wyatt’ site.
I am a long, long way away from being as famous as Allen Wyatt. He is a professional computer person.
I learnt Excel VBA a few years ago to help me with an important personal project, that’s all. I am not a computer professional.

I have studied and worked in research in Physics and some specialised areas of Microwave Electronics, at private companies and in University, where I also worked as a Lecture.
That was in England. But I moved to Germany and took a totally different direction. I mostly work as a builder, for other people, and currently I spend almost all my time building my own “Castle”, here in Northern Bavaria, Germany.

I never took much interest in computing until a few years ago, when I needed it to speed up an important personal project: I needed some help in this , a few years ago, and posted some questions, mostly at mrexcel.com , but mostly my questions were too difficult for anyone , or for the current level of understanding, so I eventually solved them myself. But I found Excel VBA quite interesting, so stayed to give help mostly , first at mrexcel , then at excelforum.
I posted as Doc.AElston initially, then in a few other names in more recent years . I got quite senior as a member quite quickly with a few names , but now I have less time for the hobby, so I post mostly at excelfox in my original forum name .
I originally led and developed some of the background technology for the first generation of mobile phones. But this was way way back in my past, and I have no need or interest to work professionally anymore . I have no real interest in computing as a professional. I was at about the “Guru” level in some computing forums in recent years, ( in “secret” forum names) , but I doubt I am anywhere near the top 100 of people. Rather in a lot of places I am in the top 1000 people. I spread myself around a bit. These days I am a “Jack of all trades, and master of none”
I, Alan Elston, am no where near the level of someone like Allen Wyatt, who is internationally known in many areas.

It is much quieter at excelfox and this suits me better just now as I have less time for the hobby. I also post a lot at Blog site comment sections, such as at Allen Wyatt’s Site. There is less pressure to answer quickly at such places.

_.____

I expect your most difficult problem in using VBA are less the VBA itself , but more in the English language. I have seen this many times at forums. VBA is based on the older Basic language. This was one of the most simplest computer languages ever written. It was written such that many code lines say in English exactly what they do. If a person with English as their native language cannot learn quickly VBA then they must be a total idiot.
VBA is a very vast subject. It is not difficult to learn but would take a very long time to learn everything. In many cases, senior forum people are just those that take an interest in Excel and VBA and who have a lot of free time on their hands and can devote a lot of their time to it, or who as part of their normal work use Excel VBA a lot.
You cannot really be a genius at VBA, or in most areas of computing. Computing is mostly just a code. If you know the code you can use it.
For a non native speaking English person , it is a totally different story all together, and it will be very difficult to understand. For a non native English speaking person, sometimes the best way to go about learning VBA is to improve in their English language skills first.

**Anshu** · 08-31-2020, 05:20 PM

Lol.. .. …. My name is Alan Elston, not Allen Wyatt. I am not Allen Wyatt!

I have this Alan vs Allen doubt in my mind for more than two weeks. So, thank you for the confirmation!

It happens often that people think that I am Allen Wyatt when I post at Allen Wyatt’ site.

If people are forced to think so, then there'll be something special they may find (see) in you,surely. So I think, it's your quality (and a little bit fault of people too!).

I expect your most difficult problem in using VBA are less the VBA itself , but more in the English language.

Absolutely Right! Actually my native language is Hindi, and I'm not good enough in English to put my ideas in words fluently, or to understand the written words easily.

For a non native English speaking person, sometimes the best way to go about learning VBA is to improve in their English language skills first.

That's what I'm working at. Learning English is my first priority. Thank you for the suggestion!!