Solved: If Value in one column is something and another column is mandatory

[rajesh nag]

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]

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]

A sledgehammer version, using a Userform

[rajesh nag]

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]

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]

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