Solved

How do I create a mandatory field based on a condition

Posted on 2007-12-03
4
2,387 Views
Last Modified: 2010-04-21
In Microsoft Excel I would like to make a mandatory field "A2" based on the condition of another field "A1". For instance when I select the value 'yes' from a drop down in A1, I want to make the next field "A2" mandatory and it must be a date format. I've found the following code, but I don't know how to modify to the above process.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Range("A1").Value = "" Then
   Range("A1").Select
   MsgBox ("You must enter a value for Cell A1")
 End If
End Sub

Any help is greatly appreciated
0
Comment
Question by:Boombox69
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 20396153
Something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Range("A1").Value = "yes" and Range("A2").Value = "" Then
   on error resume next
   Application.EnableEvents = False
    Range("A2").Select
   Application.enableevents = true
   MsgBox ("You must enter a value for Cell A2")
 End If
End Sub

Regards,
Rory
0
 
LVL 38

Assisted Solution

by:jeverist
jeverist earned 250 total points
ID: 20396167
Hi Boombox69,

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c1 As Range, c2 As Range

Application.EnableEvents = False

Set c1 = Me.Range("A1")
Set c2 = Me.Range("A2")

If Intersect(Target, c2) Is Nothing Then
    If LCase(c1.Value) = "yes" And IsEmpty(c2) Then
      MsgBox "You must enter a value for Cell: " & Replace(c2.Address, "$", "")
      Application.Goto c2
    End If
End If

Application.EnableEvents = True

End Sub

Jim
0
 

Author Closing Comment

by:Boombox69
ID: 31412341
You guy's are awesome. I'm sorry I had to split the points.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20399808
That's exactly what you should do - thanks for the points. :)
Rory
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 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