Hide Formula inside macro

**getafixkwik** · 05-17-2016, 01:05 AM

Hi. i want to hide and lock formula inside a macro worksheet. but after changing the format, the worksheet is lock and cannot use it unless unlocked.
this is the macro
Sub copydata()
Dim lc As Integer
lc = Sheets("Sheet1 (2)").Cells(6, Columns.Count).End(xlToLeft).Column + 1
Sheets("Sheet1").Range("D4:D21").Copy Sheets("Sheet1 (2)").Cells(4, lc)
Sheets("Sheet1").Range("D6:D21").ClearContents
Sheets("Sheet1").Range("D4").ClearContents
End Sub

i have added 2 columns inside sheet1 that uses max and min functions, its this formula i want to hide and lock to prevent from correction or accidentally deleted by user.
which line i have to modify? and how?
totally clueless with macro. i get help by sktneer before
thanks.
by the way, can we lock the sheet name so that it can't be change by the user?
thanks again.

**FDibbins** · 05-17-2016, 01:42 AM

Instead of using VBA for this, have you considered using sheet protection? You can set it up so that the formula is not even visible. Or, you could just hide the columns

**getafixkwik** · 05-17-2016, 02:20 AM

Hi FDibbins,
the added cell was meant to be seen just not to be manipulated. i have tried the conventional way in format/protection. protection is successful but i cannot input.
"sheet1" by the way is getting values from "sheet1 (2)" (max/min). any connection? also in "sheet1 (2)" are there ways to protect values from changes by user (expanding columns)?
if protection is off. everything works well.

**FDibbins** · 05-17-2016, 02:26 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.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.