Solved

How do I create a mandatory field based on a condition

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

Independent Software Vendors: 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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

617 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