View Full Version : Solved: If Value in one column is something and another column is mandatory
rajesh nag
03-21-2008, 02:07 AM
Hi
Please see the attached file in this
If value in Budget Type = 'Oracle paying to partner or
'Partner paying to supplier or
'Partner paying to Oracle
then
OPN Partner Country
OPN Partner Name
Partner Type columns are mandatory
Can any one help me by providing vba code to perform above action
Regards,
Rajesh
Bob Phillips
03-21-2008, 02:28 AM
Add a formula of =AA2 in IV2, and copy down as far as you need, then use
Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "IV" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
With ActiveCell
If .Row > 1 Then
If Me.Cells(.Row, WS_RANGE).Value = "Oracle paying to partner" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to supplier" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to Oracle" Then
If .Cells(.Row, "S").Value = "" Or _
.Cells(.Row, "W").Value = "" Or _
.Cells(.Row, "X").Value = "" Then
MsgBox "Partner name,company and type are mandatory"
.Value = ""
End If
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
mdmackillop
03-21-2008, 03:34 AM
A sledgehammer version, using a Userform
rajesh nag
03-22-2008, 01:18 AM
Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "IV" '<== change to suit
On Error Goto ws_exit
Application.EnableEvents = False
With ActiveCell
If .Row > 1 Then
If Me.Cells(.Row, WS_RANGE).Value = "Oracle paying to partner" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to supplier" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to Oracle" Then
If .Cells(.Row, "S").Value = "" Or _
.Cells(.Row, "W").Value = "" Or _
.Cells(.Row, "X").Value = "" Then
MsgBox "Partner name,company and type are mandatory"
.Value = ""
End If
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
HI,
This code is working fine for me but i have filled some values in column S W and X still its showing the error message could you please help me on this one
Bob Phillips
03-22-2008, 02:31 AM
Sorry about that, try this revision
Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "IV" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
With ActiveCell
If .Row > 1 Then
If Me.Cells(.Row, WS_RANGE).Value = "Oracle paying to partner" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to supplier" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to Oracle" Then
If Me.Cells(.Row, "S").Value = "" Or _
Me.Cells(.Row, "W").Value = "" Or _
Me.Cells(.Row, "X").Value = "" Then
MsgBox "Partner name,company and type are mandatory"
.Value = ""
End If
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
rajesh nag
03-22-2008, 10:47 PM
Sorry about that, try this revision
Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "IV" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
With ActiveCell
If .Row > 1 Then
If Me.Cells(.Row, WS_RANGE).Value = "Oracle paying to partner" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to supplier" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to Oracle" Then
If Me.Cells(.Row, "S").Value = "" Or _
Me.Cells(.Row, "W").Value = "" Or _
Me.Cells(.Row, "X").Value = "" Then
MsgBox "Partner name,company and type are mandatory"
.Value = ""
End If
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
Thanks A Lot
Regards,
Rajesh
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.