Paste Value Macro

**ragrone** · 06-10-2014, 10:20 PM

I need to be able to have this macro running on a worksheet automatically. It would be to allow pasting text only into ALL cells on the worksheet.

How would I do this?

Please Login or Register  to view this content.

Note: I recorded this macro however, I need to change the columns realistically to say columns A through P and always be active on the spreadsheet.

I attempted to place it on the worksheet as

Please Login or Register  to view this content.

No luck could someone clean this up for me? thanks...

**protonLeah** · 06-10-2014, 11:41 PM

You have a sub within a sub...

Please Login or Register  to view this content.

Tfherefore, delete the red lines. Note: I don't see where you have copied anything to paste.

**ragrone** · 06-11-2014, 05:28 AM

Thanks, and I understand that as, I was looking for a method to apply this statement

Please Login or Register  to view this content.

To the entire sheet.. it was relatively late last night and was in a hurry...

So to clarify, I have two questions:
1) After recording any macro, how and where relative to the type of macro e.g. worksheet or workbook, how do you get the "modules" to run automatically in either the workbook or worksheet?
2) I recorded the above macro, and would like to know how I can apply that small line of the code to the worksheet to allow pasting only the "text" or "values" only

Please Login or Register  to view this content.

or preferably:

Please Login or Register  to view this content.

Applying either one of these rules to the entire workbook would work as well

Maybe what I am looking for Is VBA that would limit the number of characters and type of (values only) to any given cell in a column.

Something like Msgbox "You are only allowed to copy/paste or enter no more than 5 characters to this cell" of course that would be the VBAlert

Sorry for the confusion

Originally Posted by protonLeah

You have a sub within a sub...

Please Login or Register  to view this content.

Tfherefore, delete the red lines. Note: I don't see where you have copied anything to paste.

**protonLeah** · 06-11-2014, 07:25 PM

You cannot write code to stand guard over copy/paste operations. You would have to use "complicated" Menu/ribbon modifiers: like http://www.ozgrid.com/forum/showthread.php?t=164806
and
http://www.rondebruin.nl/ribbon.htm

**ragrone** · 06-11-2014, 10:03 PM

Actually I found this which works really well it forces the users to manually enter data into the worksheet....

Please Login or Register  to view this content.

However, I found a better solution to my Data Validation problem

Please Login or Register  to view this content.

That does a bang-up job and works well in conjunction with the excels built in Data Validation Dialogue box...

Thanks... it was a long road to get back into the VBA groove... the last I coded anything was 2005...

Originally Posted by ragrone

Thanks, and I understand that as, I was looking for a method to apply this statement

Please Login or Register  to view this content.

To the entire sheet.. it was relatively late last night and was in a hurry...

So to clarify, I have two questions:
1) After recording any macro, how and where relative to the type of macro e.g. worksheet or workbook, how do you get the "modules" to run automatically in either the workbook or worksheet?
2) I recorded the above macro, and would like to know how I can apply that small line of the code to the worksheet to allow pasting only the "text" or "values" only

Please Login or Register  to view this content.

or preferably:

Please Login or Register  to view this content.

Applying either one of these rules to the entire workbook would work as well

Maybe what I am looking for Is VBA that would limit the number of characters and type of (values only) to any given cell in a column.

Something like Msgbox "You are only allowed to copy/paste or enter no more than 5 characters to this cell" of course that would be the VBAlert

Sorry for the confusion