Macro to do a data validation check before exporting to text file

Hi

I have an excel macro enable spreadsheet, that exports the contents into a text file. It has three columns in the xls that is used to capture amounts with two decimal places and these are formatted as text fields. When the macro runs it left pads the amount columns with zeroes to get a 16 width result in the exported text file and removes the decimals as target system implies the decimals. If decimals are left out and a whole number is entered it screws the result up

Because of this, I can't add normal data validation within the xls to ensure that the amount fields are entered with two decimals and because I need the fields as text I can't use formatting to force the result. Hence I want to build into the macro, a step at the beginning that select the ranges as the three columns I am interested in from rows 2 to 1000 and checks for any cells with no decimal followed by 2 numbers, if found, it should highlight the cell as well as pop up a MsgBox with an error message and end the rest of the macro from running (i.e. abend) until the user fixes all the cells and re-runs

In another forum post a guy did a script that checked for specific values and highlighted cells where the condition was not met. just need mine to be slightly different as it is looking for a decimal + 2 number pattern in the cell and I need the cells not just highlighted but the msgbox and error and rest of macro stops / exits at that point

the other forum post was this one

https://www.mrexcel.com/forum/excel-...alidation.html

thanks for the help guys

I think there’s a flaw in the code above, in the IF THEN part, so use this one instead:

Please Login or Register  to view this content.

Hi Akuini

Thanks very much for this

I tried running macro and it gave a run time error 2004 at the point below (row below was highlighted yellow)

r.Interior.Color = xlNone

Sorry runtime error = 1004 not 2004

Originally Posted by troygeri

Hi Akuini

Thanks very much for this

I tried running macro and it gave a run time error 2004 at the point below (row below was highlighted yellow)

r.Interior.Color = xlNone

Maybe it has something to do with your Excel version Excel 2003 ?
That line is supposed to clear the cell color before it get highlighted.
Just delete that line, then run the code again, see what happen.

Could it be because there is more than one worksheet in the spreadsheet and worksheet name needs to be specified when defining the range?

Using Excel 2016.. but ill try it..

Originally Posted by troygeri

Could it be because there is more than one worksheet in the spreadsheet and worksheet name needs to be specified when defining the range?

I don't think so. The code work only in activesheet.
Let's try something simple first. Run this code in a new blank sheet. See if it gets error.

Please Login or Register  to view this content.

OK that did fix it... thankjs

Only remaining issue is I have is, user presses button that runs another macro (call it parent macro), parent executes this qualitycheck macro first, and then calls and runs remaining macros. If running your qualitycheck macro detects a problem, I want it to stop the remaining macros from running, pressing OK on the Msg Box should result in user being sent back into the worksheet where they will see the highlighted cell with the error

One other point. Highlighted cell is grey, no matter what color is specified in the macro. Secondly, it only seems to highlight the first cell it encounters that breaches the condition. Running you above color sub on a blank sheet worked as expected without an error, column A was highlighted blue and the others had no highlighting

Originally Posted by troygeri

OK that did fix it... thankjs

Only remaining issue is I have is, user presses button that runs another macro (call it parent macro), parent executes this qualitycheck macro first, and then calls and runs remaining macros. If running your qualitycheck macro detects a problem, I want it to stop the remaining macros from running, pressing OK on the Msg Box should result in user being sent back into the worksheet where they will see the highlighted cell with the error

You mean it’s fixed by deleting the line?
Well, you need to find a way to clear the range before running the macro, otherwise if you run it for the second round then the highlighted cell (from the first run) is still there so it will distort the result.
Try changing the line with this:

Please Login or Register  to view this content.

I don’t quite understand your macro set up, but basically you need put my macro inside your macro (that runs by hitting a button) & you need to put it above the existing lines of your macro.

OK vbWhite seems to work ok.. no error.

Ignore my comment about highlighting not applied to all cells.. it does highlight all cells in error

For the parent macro. it starts off something like this

Sub xls_txt_convert()

Dim DestinationFile, CellValue, Filler_Char_To_Replace_Blanks As String
Dim FileNum, ColumnCount, RowCount, FieldWidth As Integer
Dim sht As Worksheet

Call QualityCheck
Call zeroPad
Call RowSelect

So what I want is if QualityCheck finds cells with error, then it should exit the parent macro and not keep running after pressing OK on the msg box that your macro pops up

Ok, it should be something like this:

Please Login or Register  to view this content.

Almost there!! Tried your suggestion above. It stops at Call QualityCheck(deflag), with (deflag) highlighted and Compile Error (ByRef Argument Type Mismatch)

The above code works for me, so I don't understand why it doesn't work for you.
But try this this one:

Please Login or Register  to view this content.

Thanks. Adding deFlag As Boolean to the DIM statement resolved it. The only remaining issue I have is that the cells without the decimals still don't get highlighted. I've tried different ways of writing the Interior.Color statement to no avail

Originally Posted by troygeri

Thanks. Adding deFlag As Boolean to the DIM statement resolved it. The only remaining issue I have is that the cells without the decimals still don't get highlighted.

I forgot to change the decimal separator back to ".", so replace "," with "." in red part:

Please Login or Register  to view this content.

yeh I already had that

Please Login or Register  to view this content.

The check works as if whole numbers are in the cells it pops up the MsgBox but if all numbers are with two decomal places all the other macros run as intended. So, basically, the check is working, just not highlighting the cells