Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Popup window in excel 2007

Posted on 2011-09-16
4
307 Views
Last Modified: 2012-05-12
A spread sheet where I have a drop down list and if the selection in column C is "independent" I will need a popup window stating that column H must be filled by user.
0
Comment
Question by:Tavasan65
  • 2
4 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 36552440
Assuming that you are using data validation for your dropdowns, you can do what you request using a Worksheet_Change macro like:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("C2:C10")  'Watch these cells for selection of Independent
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub

For Each cel In targ.Cells
    If LCase(cel.Value) = "independent" And cel.EntireRow.Range("H1") = "" Then _
        MsgBox "If you choose Independent in cell " & cel.Address(False, False) & vbLf & _
            "then column H must be filled in as well"
Next
End Sub

Open in new window


The above code must be installed in the code pane for the worksheet being watched. It won't work at all if installed anywhere else.

As posted, the code watches cells C2:C10 for a user-selection of "Independent". If it occurs, then the code checks whether column H already has a value. If not, then a pop-up message is displayed.
WatchValidationSelectionQ2731298.xlsm
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36552456
Assuming you are using a Data Validation drop down list for Column C, add this code to the appropriate Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cel As Range
    Dim MsgString As String
    Dim CellCount As Long
    
    If Not Intersect(Me.UsedRange, Me.[c:c], Target) Is Nothing Then
        MsgString = "For the following cells, make sure to populate Column H:" & Chr(10)
        For Each cel In Intersect(Me.UsedRange, Me.[c:c], Target).Cells
            If LCase(cel) = "independent" Then
                MsgString = MsgString & Chr(10) & cel.Address(False, False)
                CellCount = CellCount + 1
            End If
        Next
        If CellCount Then MsgBox MsgString, vbInformation, "Reminder!"
    End If
    
End Sub

Open in new window

0
 

Author Comment

by:Tavasan65
ID: 36584711
these are very close.  what if you have another column U that is dependent on the text "Termination" that will require column V and column W to be filled with dates.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 36584761
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ1 As Range, targ2 As Range

Set targ1 = Range("C2:C10")  'Watch these cells for selection of Independent
Set targ1 = Intersect(targ1, Target)
If Not targ1 Is Nothing Then
    For Each cel In targ1.Cells
        If LCase(cel.Value) = "independent" And cel.EntireRow.Range("H1") = "" Then _
            MsgBox "If you choose Independent in cell " & cel.Address(False, False) & vbLf & _
                "then column H must be filled in as well"
    Next
End If

Set targ2 = Range("U2:U10")  'Watch these cells for selection of Termination
Set targ2 = Intersect(targ2, Target)
If Not targ2 Is Nothing Then
    For Each cel In targ2.Cells
        If LCase(cel.Value) = "termination" And (Not IsDate(cel.EntireRow.Range("V1")) Or Not IsDate(cel.EntireRow.Range("W1"))) Then _
            MsgBox "If you choose Termination in cell " & cel.Address(False, False) & vbLf & _
                "then column V and W must be filled in with dates as well"
    Next
End If
End Sub

Open in new window

WatchValidationSelectionQ2731298.xlsm
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

840 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