PDA

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

xld
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

xld
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