Solved

Popup window in excel 2007

Posted on 2011-09-16
4
315 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 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