Solved

Popup window in excel 2007

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

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 80

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now