Excel 2007 : Message box creation

**Nikeyg** · 05-10-2011, 06:17 AM

Hi guys.

I have a macro that creates a sheet from data refreshed from an external source.
This macro checks the data for changes and notifies you of the changes to prevent amendments from going unnoticed.
Currently, what it does is filter the data, copy it, create a new sheet and paste it into the new sheet.
This is a very clunky way to do it in my opinion.
What I want it to do, is put the information in a MsgBox so the user can spot it, then click ok to dismiss once they know it's taken care of.

Is there any way for a macro to create a message box from copied/pasted data?
Or are they limited only to data pre-entered before the macro run?

Or, is there another way around it? Userforms or something?

Thanks in advance for any help.

**royUK** · 05-10-2011, 07:29 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Nikeyg** · 05-10-2011, 08:02 AM

Ok
I'll try, this isn't an easy thing to explain.
The dummy sheet has 2 tabs, what the sheet looks like before, and after refreshing.

The macro copies the data of the original, and pastes it into column S.
This provides the conditional formatting with an original copy of the data to compare to for Unique or duplicate values.
You'll notice on the after sheet, that the column S is populated as per that example.
You'll also notice column P has a LEN formula to count the amount of characters in the cells in column A.
A9 has been amended, and as such, the conditional formatting identifies it as red.

My macro, then produces an autofilter, that filters column A by the colour red, and column P by the number 8, as amended trades have 8 characters.
The LEN is required as if new data comes into the sheet, because it is Unique data, it also gets coloured red. So, the LEN identifies amended trades only.

Once the autofilter is applied, the macro copies the data that is in the autofilter range, and pastes to a new sheet.

What I need is to replace the copy and paste to a new workbook, with the amended trade appearing in a Message Box in the centre of the screen.

Please let me know if I haven't been clear enough.
It's been a long process getting the sheet to this point.

Thanks

**Nikeyg** · 05-10-2011, 12:18 PM

I've managed to get this code, which puts my filtered range into a Message Box (YAY!!!)

Please Login or Register  to view this content.

Problem is, it displays all the data on the same line, only wrapping the data when there is too much for that line.

What I need, is for each line of data to be displayed on a seperate line.
Any idea how to seperate the data lines?
Data is only in columns A to D.

**Nikeyg** · 05-11-2011, 04:42 AM

Sorry to bump this.
But all my Google searches are drawing a blank.

Would putting my data into an array help?

If so, how do you go about putting an autofiltered range into an array?