VBA code for Validation

[Former Member]

HI All,

    I have a requirement I have to write a VBA code to validate for all input cell in an input form (EPM 10) with negative values.

In case the user enters a negative value, a message box thrown " No negative values allowed" and secondly if the user changes the value of a cell that

particular cell should become red color. Again on refresh the red color validation should disappear.

     Now that I had already tried with the validation function in SP13 which did not give the desired result and so I have decided to code ,

Now I have a problem I need to code the excelVBA that I have to apply the validation code only on the input cells How do I do that ? the sample code is below

Dim EPMex As New FPMXLClient.EPMAddInAutomation

(Macro 1 - For negative value validation)

Sub Worksheet_Change(ByVal Target As Range)

If Range("A1") < 0 Then

        MsgBox "No Negative value"

    End If

    Target.Font.ColorIndex = 3

End Sub

(Macro 2 - For changed cell)

Private Sub Worksheet_Change(ByVal Target as Range)

    Target.Font.ColorIndex = 5

End Sub

Thanx

[Former Member]

Hi Sam,

Maybe you can use BPC EPMFormatting Sheet.

Put this custom format #,##0.00;[Red](#,##0.00);  on row and colum banding.

These will put red color in every data that user input or change, but the weakness of this method is there is no validation in input template.

Hope this will help you.

Thanks,

Suprapto

[former_member186338]

Hi Sam,

Yes it will a hard coding of the input areas. For some specific report you can do it more dynamic analyzing members on the axis (looking for the cells with EPMOlapMemberO function, but i's still hard work.

Normally these cases are handled with Excel validation rule and users are aware that they have to use Paste value not to delete the rule.

B.R. Vadim

[former_member186338]

Hi Sam,

There is no way in VBA to determine that some cell is the input cell or not. It means that you have to manually define the input area as a combination of rectangular ranges. Then you have to compare target range (rectangular) of Worksheet_Change with this area and check values for the cells on the intersection. No easy solution.

B.R. Vadim

[former_member186338]

Ups, some correction!

Target range can also be non rectangular - as a combination of multiple areas... (In case of Paste of single value to number of cells selected by Ctrl).

Vadim

[Former Member]

Thanks, but wouldn't that be considered to be a hard coding and in case of data change it might impact the input schedule. Normally how are these cases handled ?