Solved

Popup window in excel 2007

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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