VBA Macros code to run a validation using BEFORE_SAVE Function and to Save Data

[Former Member]

Hi Experts,

I am struggling to come out with the correct syntax to combine the "Before_Save ()" function and "SaveAndRefreshWorkbookData() ".

I have some cells running validations and i want the Macros to evaluate this cells and based on their value execute the "SaveAndRefreshWorkbookData() " or display a message box asking to "Check Calculation"...anyone with some ideas willing to share the syntax?

Thanks.

[akos_beres · Accepted solution]

Maleo,

what sort of validation are you trying to run? Can you provide an example?

Akos

[Former Member · Accepted solution]

I’d like the VBA code to make sure that no data gets saved back to the server if any “MISMATCHES” Excel data validation cells are display.

I am currently using an EPMSAVEDATA formula at in a separate column with an If statement, but it is not working as planned. Because it is still saving the rows that MATCH……the purpose of this is to do not allow the user to save any data back to the server up until all cells say “TIES”, not just the ones pass the excel data validation.

=IF($M$40="MISMATCHES","NOT SAVING DATA",EPMSaveData(H33,,$L$1,$L$2,$L$3,$L$4,$L$5,$H$32,L33))

[akos_beres · Accepted solution]

Maleo,

what I would do is break the if function to evaluate the send and the save into two separate columns.

1. change formula to =IF($M$40="MISMATCHES","NOT SAVING DATA","SAVE DATA").

2.  In cell M30 put a formula to count if there are any mismatches like =countif(M32:M500,"MISMATCHES")

3. Re-write save =IF($M$30>0,"",EPMSaveData(H33,,$L$1,$L$2,$L$3,$L$4,$L$5,$H$32,L33))

Hope this helps!

Akos

[former_member186338 · Accepted solution]

Hi Maleo,

Using EPMSAVEDATA is a bad practice in general, try to use normal EPM input form.

In VBA you can test everything what you need (just check cells in the test column).

Vadim

[Former Member · Accepted solution]

Vadin,

So you're are saying that I should hard code the Columns that would save the data back to the server on my VBA code? if so, how can I achieve that? I am currently using this code for my SaveandRefresh;

Option Explicit

Sub SaveData()

Dim S As New FPMXLClient.EPMAddInAutomation

S.SaveAndRefreshWorksheetData

End Sub

which is simple and straight forward, how do I declare the particular Range of cells to be saved back to the server? Thanks in advance

[Former Member · Accepted solution]

Thanks for the input, I actually ended up assigning an integer value to the validation cells;

=IF(M40="TIES",0,1) then at the very bottom I am adding all the number in a cell name "rng_Validation" which should add up to zero "0" in order to allow the the data to be saved to the server.

afterwards, I completed this with a simple VBA function;

Function BEFORE_SAVE()

If Range("rng_Validation") > 0 Then

    MsgBox "Please correct the numbers before saving", vbCritical

    BEFORE_SAVE = False

Else

    BEFORE_SAVE = True

End If

End Function

[akos_beres · Accepted solution]

Maleo,

I think Vadim is saying don't use the EPMSave formulas because it is slower and less dynamic. He is suggesting to convert the EPMsave formulas into an EPM report.

Akos

FYI - Use the below syntax ... newer version of the EPM add-in have issues with running EPMAddinautomation

Sub SaveData()

Dim client As Object

Set client = Application.COMAddIns("FPMXLClient.Connect").Object

client.SaveAndRefreshWorksheetData

End Sub

[former_member186338 · Accepted solution]

Hi Maleo,

Your code is for saving data. You can add any checks before S.SaveAndRefreshWorksheetData

As an alternative you can use BEFORE_SAVE event function - just create it in a new module of VBA. Please read help: 42.7 Custom VBA Functions (Events)

BEFORE_SAVE will be triggered if you press standard save button or launch your custom save procedure.

About ranges: you have fixed column for check. You can get start and end row using GetDataTopLeftCell and GetDataTopLeftCell.

Vadim

[former_member186338 · Accepted solution]

Yes, you can use Excel function to aggregate the check result!

The same can be done in VBA:

Dim cell As Range

BEFORE_SAVE = True

For Each cell In Range("C4:C13").Cells

If cell.Value = "MISMATCHES" Then

    BEFORE_SAVE = False

    Exit For

End If

Next cell

P.S. Or faster code:

Dim varCheck() As Variant

Dim lngTemp As Long

varCheck = Range("C4:C13").Value

BEFORE_SAVE = True

For lngTemp = 1 To UBound(varCheck, 1)

If varCheck(lngTemp, 1) = "MISMATCHES" Then

    BEFORE_SAVE = False

    Exit For

End If

Next lngTemp

[former_member186338]

But what is the issue?

BEFORE_SAVE will be triggered if SAVE is launched

Inside this function you can perform validation and in case of error set BEFORE_SAVE=False.

You don't need to execute SaveAndRefreshWorkbookData

Vadim