?
Solved

Popup window in excel 2007

Posted on 2011-09-16
4
Medium Priority
?
324 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
[X]
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
  • 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 93

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

771 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