GLamberson
New Member
- Joined
- Oct 16, 2018
- Messages
- 10
I have searched and found 1 thread that was similar, but did not answer my dilemma, if I missed something, my apologies. I am very new to VBA.
I have my formulas hidden, however, I can still copy he set& paste to a new workbook and see the formulas. I want to allow copying & pasting, is there a way to allow the copying & pasting but show the formulas as values only?
Below is what I am using for my code to hide formulas:
I have my formulas hidden, however, I can still copy he set& paste to a new workbook and see the formulas. I want to allow copying & pasting, is there a way to allow the copying & pasting but show the formulas as values only?
Below is what I am using for my code to hide formulas:
Code:
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000]Private Sub Workbook_SheetSelectionChange(ByVal ShAs Object, ByVal Target As Range)[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] Const pwAs String = "Secret"[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] DimrFormulaCheck As Range[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] On ErrorResume Next[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] WithTarget[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .Parent.Unprotect pw[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .Locked = False[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .FormulaHidden = False[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] If.Cells.Count = 1 Then[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] If.HasFormula Then[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .Locked = True[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .FormulaHidden = True[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .Parent.Protect pw, , , , 1[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] End If[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] ElseIf.Count > 1 And .Count < 5 Then[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] With .SpecialCells(xlCellTypeFormulas)[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .Locked = True[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .FormulaHidden = True[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] .Parent.Protect pw, , , , 1[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] End With[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] End If[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] End With[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000] On ErrorGoTo 0[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Times New Roman"][B][FONT=Arial][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT][/B][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]
[EndCode]
Thank you.