Solved

How do I create a mandatory field based on a condition

Posted on 2007-12-03
4
2,392 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
[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 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

Technology Partners: 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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

734 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