Prohibit paste in formula bar not just cells

WaltzAir

New Member
Joined
Sep 19, 2012
Messages
33
Excel 2007/Win 7: I need to prohibit users from doing paste operations, i.e. just allow direct data entry via keyboard.
I have found code to disable cutcopymode and disable menus and key shortcuts.
However, copying text from Word or other application still works and pasting into the formula bar accepts it.
Pasting directly in a cell is not happening, however.
Is there anyway to disable the user's clipboard? Or is that too drastic?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,707
Office Version
  1. 2016
Platform
  1. Windows
Possibly try looking at hiding the Formula Bar when the workbook is opened. Is there a reason why you don't want them to Copy Paste?

Application.DisplayFormulaBar = False
 

WaltzAir

New Member
Joined
Sep 19, 2012
Messages
33
Possibly try looking at hiding the Formula Bar when the workbook is opened. Is there a reason why you don't want them to Copy Paste?

Application.DisplayFormulaBar = False

The users tend to copy large amounts of text (along with typos) into a cell. If they have to type it from the keyboard, they are more succinct, which is what is desired.
I wonder if there is a way to detect reactivation of the Excel app upon leaving Word with the clipboard loaded ... then I could intercept and clear it before it could be pasted into the formula bar.
I need the formula bar displayed for viewing cell contents when there is a large amount of text.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,707
Office Version
  1. 2016
Platform
  1. Windows
You can always change the display to text in the sheet by using the Control Key + Accent Key (the key below the escape key) and use same combination to switch back to normal.

A typing error is a typing error, spell checking is always another option.
 

WaltzAir

New Member
Joined
Sep 19, 2012
Messages
33
You can always change the display to text in the sheet by using the Control Key + Accent Key (the key below the escape key) and use same combination to switch back to normal.

A typing error is a typing error, spell checking is always another option.

The typing errors come from the text that was created in some other application (possibly w/o spell check).
Of course, part of the problem is disciplining the users to run spell check once it's in Excel.
The text issue viewing in the formula bar is one of cell size and text overflow that cannot be displayed in a cell.
 

WaltzAir

New Member
Joined
Sep 19, 2012
Messages
33
To further explore the cell text display issue -- a cell can contain up to 32,767 characters. For a maximum cell size of 255 wide by 409 high and the normal style set to 8 and the text in the cell set to 8, the "standard" or "n" character will display 240 characters wide by 35 rows high. Proportional fonts range from 561 "." characters wide to 153 "m" or "W" characters wide, so the total is quite variable. Setting the text to a lesser font size than the normal style will allow more to display -- down to the "greeking" limit. For example, using the same scenario with a font size of 4 and all "n" characters will allow all 32,767 to display (a factor of 2 squared). At font size 8, 240*35 gives 8400 standard characters, or about 25% display-able. Even the minimum period character will only display 19,635 (59%) characters! The "m" will only show 5,355 (16%). The formula bar will display all, but its window even at max would need to scroll.
 

Forum statistics

Threads
1,136,854
Messages
5,678,138
Members
419,746
Latest member
tysonboy82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top