Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

pop up message in excell

Posted on 2011-04-22
7
Medium Priority
?
245 Views
Last Modified: 2012-05-11
i have an excell sheet with two main fileds: student class: (1st secondary,2nd secondary, 3rd secondary, other,) now every class has its own score of success.
students in 1st secondary must get more than 60 out of 100 to sucseed, .....
now the user who is inputing the values will choose first the class then he will put the score, what i want is to evaluate the score the user is inputing & put a pop up message in case the student failed . in cas of faliour the a pop up message will appear  & in this case the user cant continue inputing the other fileds unless he changes the score of the student to a succesfull score.
so each classs has its own failour scores.
0
Comment
Question by:Hassanay
  • 2
  • 2
5 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35448147
A sample file would definitely speed up the process of providing a solution as it would help me get my references correct :)

Sid
0
 

Author Comment

by:Hassanay
ID: 35448161
here the file class.xlsx
class.xlsx
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35448320
Here it is :)

Please find a sample attached. Please note that I have colored the cells. Only the yellow color cells can be changed. I have also protected the worksheet. The password is "pass"

Hope this helps.

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim minScore As Double
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("E3")) Is Nothing Then
        '~~> Check if Student Class is selected
        If Len(Trim(Range("E2").Value)) = 0 Then
            Target.ClearContents
            MsgBox "Please Select a student class first"
            Exit Sub
        Else
            Select Case Range("E2").Value
            Case "1st secondary"
                minScore = Range("C2").Value
            Case "2nd secondary"
                minScore = Range("C3").Value
            Case "3rd secondary"
                minScore = Range("C4").Value
            Case "other"
                minScore = Range("C5").Value
            End Select
            
            If Len(Trim(Target.Value)) = 0 Then
                ActiveSheet.Unprotect "pass"
                Range("E4").Value = "-"
                ActiveSheet.Protect "pass"
            ElseIf Target.Value < minScore Then
                ActiveSheet.Unprotect "pass"
                Range("E4").Value = "Failed"
                ActiveSheet.Protect "pass"
                MsgBox "Student has failed"
            ElseIf Target.Value >= minScore Then
                ActiveSheet.Unprotect "pass"
                Range("E4").Value = "Passed"
                ActiveSheet.Protect "pass"
            End If
        End If
    ElseIf Not Intersect(Target, Range("E5")) Is Nothing Then
        If Range("E4").Value = "Failed" Then
            Target.ClearContents
            MsgBox "You cannot enter anything here as the student has failed"
            Application.EnableEvents = True
            Exit Sub
        End If
    ElseIf Not Intersect(Target, Range("E6")) Is Nothing Then
        If Range("E4").Value = "Failed" Then
            Target.ClearContents
            MsgBox "You cannot enter anything here as the student has failed"
            Application.EnableEvents = True
            Exit Sub
        End If
    End If
letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume letscontinue
End Sub

Open in new window

class.xlsm
0
 

Author Comment

by:Hassanay
ID: 35451147
thats ok, but i also need a dynamic message, which each class has its own success score, so the error message must tell the user that the success for this class is 65 , so for each class there is a message. and please note that protection is not a priority in my case..so if it can be done without macro it will be better.
i only need some formula to validate the score field and tell the user if he passes or not.
0
 
LVL 10

Expert Comment

by:bromy2004
ID: 35929414
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question