Macro to support Notification of two different numbers

EE Pros,

I'm looking for a macro that notifies a Excel WB User that two numbers are not =.  In the attached mock up, the "Imputed Tax Rate", calculated by dividing Income Tax by Net Sales, is different then the "Effective Tax Rate" which is entered as a number in a different cell.

I am trying to notify the user by a text box, a message or "comment" that only shows when the User selects the Effective Tax Rate Cell, or hovers the cursor over it (In the Mock Up, cell M19).  The only other conditions are; if the numbers are "=" there is no need to display the notice.  If the Income Tax cell is -0- or blank, the Imputed Tax Rate is then also -0- and may be displayed as compared with the Effective Tax Rate.

Hopefully this is an interesting challenge!  And as always, "thank you" in advance!

B.
Notice.xlsm
Bright01Asked:
Who is Participating?
 
Roy CoxConnect With a Mentor Group Finance ManagerCommented:
Is this better?
Notice-V4.xlsm
1
 
Roy CoxGroup Finance ManagerCommented:
I'll take a look.

I think this could probably be done using Data Validation
0
 
Roy CoxGroup Finance ManagerCommented:
Is this correct

I'm not sure what the existing code in the change event is for.
Notice.xlsm
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Bright01Author Commented:
Roy,

It's coming along.  I've cleaned up the existing code and added a Form that has the text that I'm looking for within the Form.  Here's what I cannot do;

1.) How do I add the math code to include in the form / text? (Imputed Rate and Effective Rate)
2.) How do I display the form when the cursor hovers over M19?

That would go a long way.

J.
Notice-V2.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
Working on it
0
 
Roy CoxGroup Finance ManagerCommented:
Hi Jim

Change the inputted value in M19 to an incorrect value to display the UserForm.

You could use Selection_Change to display the form when M19 is selected, there's not a hover  event.
Notice-V2.xlsm
0
 
Martin LissOlder than dirtCommented:
Bright, I believe that I can come up with a "hover" solution so hang on a bit.
0
 
Roy CoxGroup Finance ManagerCommented:
I've seen  this done with a shape, but I really don't think it works well.
0
 
Roy CoxConnect With a Mentor Group Finance ManagerCommented:
I've added a hyperlink to the cell. Now hover over it. I prefer the UserForm
Notice-V2.xlsm
0
 
Bright01Author Commented:
Martin and Roy,  Thanks!  Martin, Roy's solution works but not completely to spec.  Based on Roy's work, I have refined the ask.  

When you put data into the Income Tax cell (E17), and the Effective Tax Rate cell (M19) is not -0- or blank, or if you change the Tax Rate Cell (M19) and the Income Tax cell (E17) is not -0- or blank, and both the Income Tax cell (E17) and the Effective Tax Rate cell M19) are not =,  then the alert should be displayed showing both results.  Hovering would also be a nice add and could be as good if not better then an alert.... but given Roy's comments, I rethought the ask.  Either approach may be workable.

Thank both of you guys for the help.....

J.
0
 
Bright01Author Commented:
Roy,

Almost!  Love the hover!  BUT... the math in the alert box must reflect any change to either cells (i.e. Income Tax ((E17)) or Effective Tax Rate ((M19)).
0
 
Roy CoxGroup Finance ManagerCommented:
I was waiting to hear from you. I just need to amend the rules to display the UserForm to match what you have just asked for. The calculation amd message was the  hard work. I've added a hover feature, but I'm not keen on it.

Got to go now but I can add the rules for you later. when I get home, I'll be about an hour
0
 
Martin LissOlder than dirtCommented:
Go with Roy on this. I was trying to do it via the suggestions in this article but I could only get the form to show once.
0
 
Bright01Author Commented:
I'll do it Martin.  Thanks for jumping in.  Between you, Roy and Brad, I'm consistently amazed at the skills and knowledge you guys have and the value delivered through EE.

J.
0
 
Roy CoxGroup Finance ManagerCommented:
Hi Jim

I need to play about with the hover, but check out the userform showing.

I'll check back later
Notice-V3.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
I've just remembered that I wrote some code last year that can probably be adapted to act as "comment" It's been a long day but I'll locate it and see how it amends. I'll get back as soon as I can.
0
 
Bright01Author Commented:
Roy, your version 2 with the hovering capability is almost exactly what I'm looking for (vs. the UserForm approach).  All we need to do is build in the change to the two variables into the hover notice and it will be perfect.


J.
0
 
Roy CoxGroup Finance ManagerCommented:
Hi Jim

I spent quite a bit of time playing with this last night. I noticed that the pop up example wasn't changing when changes were made in the worksheet. I tried a couple of fixes but they didn't work.

I've substituted code in V4 that pops up a Label with the message on it.

V3 has the UserForm which I still think is best, you can dismiss it when read.

Check  they pop up with the correct criteria.
Notice-V3.xlsm
Notice-V4.xlsm
0
 
Bright01Author Commented:
Roy,

I can live with the Form approach (V3).  But there are two issues;

1.) The Imputed Rate and the Effective Rate need to be reversed in the text.
2.) I tried to adjust the form because when you cycle through the different scenarios, the second % is dropped in the Alert1
3.) And finally, if the Imputed Rate and the Effective Rate are =, then the Alert should not fire since they are the same.

Thanks for all the help!

B.
0
 
Bright01Author Commented:
Sorry.... 3 issues.
0
 
Roy CoxGroup Finance ManagerCommented:
No problem, I'll sort this
1
 
Bright01Author Commented:
Thank you!!!
0
 
Bright01Author Commented:
PERFECT!  Thank you Roy.  This adds a nice touch to the model.  Great job. And always appreciated.

Jim
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.