Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I know how to hide formula by unlocking and hiding the cell properties then by applying worksheet protection. I want to know how to hide formula but allow to copy it(formula) in excel 2003 workbook.
re: I know how to hide formula by unlocking and hiding the cell properties...
Why would you hide a formula but unlock the cell? Even Excel thinks that's a bad idea and throws up a warning when you do that.
re: I want to know how to hide formula but allow to copy it
You want to copy something you can't see?
Please give us some more details as to what you are trying to accomplish.
It sounds like you're going to need VBA to do this, but we need more details before we can suggest anything.
0 
Derby,
The word "unlocking" in my earlier post/comment was a mistake on my part. Yes I need VBA that lock formula from viewing with an option to copy it.
Actually, i want to create a standard format and distribute it to my subordinate staff so that they can enter data and use my formula wherever required. I have more than one formula and it may spreads on many columns/rows in my worksheet.
0
Your requirements are still not clear enough for me.
If your staff can't see your formulae - plural, since you said "I have more than one formula" - how would they know what they are copying?
0
Column have same formula that can be copied down to the rows but different column have different formula.
for example:Column C
Row1=a1+b1
Row2=a2+b2
Row3=a3+b3Column D
Row1=a1*b1
Row2=a2*b2
Row3=a3*b3
Row4=a4*b4Column E
Row1=mround((C1+d1)/2, 1)
Row3=mround((C3+D3)/2, 1)The staff have to copy down the formula to the rows as long as their data goes down. The requirement is such that they need not to copy formula from column E to Column C
0
I guess you could use a Worksheet_Change macro to accomplish your goal.
As data is entered into Column A, the formulae in C:E are copied from the row above into the row where the new data was entered. As written, it will fail for Row 1, since there is no Row 0. If you need to actually Lock the cells in Columns C:E then the code has to "upgraded" to handle the protection issues before it can copy/paste the formulae.
I assume that you also realize that the users have to have macros enabled on their systems and that you should hide and protect the code also.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Range("C" & Target.Row - 1 & ":E" & Target.Row - 1).Copy _ Destination:=Range("C" & Target.Row) End If End Sub
0
Tried the code but says " Run-time error 424: Object required". However i can copy the value not formula.
It seems i cannot achieve what i want as i have been to so many excel expert with this question. The simple question and hard to get answer was: how i can copy the formula from a cell that is protected & its formula hidden. One of my friend suggest me this:
"Define a name, like SecretFormula, to be the actual formula -- that is, if you have a formula like =VLOOKUP(D3,G1:H22,2,FALSE), define SecretFormula to be that exact formula, then use =SecretFormula instead. In the VBE (Alt/F11), go to the immediate window (Ctrl/G), type this in:
Thisworkbook.Names("SecretFormula").Visible = False
and then its definition won't show up in the define name dialog and the user will only see =SecretFormula in the cell."But it does not fullfil my requirement. I am thinking that isn't there a way to write a code that lock all cells which begins with "=" sign(as all formula begin with '=') and made it copyable. Anyway Thanks Derby for your reply.
0
You can absolutely do what you want, but your code needs to unprotect the worksheet first, then copy the formula, then protect the worksheet again.
I do not have access to Excel this weekend, so I can't produce the code you need, but trust me, what you are asking for is not that complicated.
The code I offered earlier works just fine for me, with no errors, but it does not deal with the protection issue. To use it, you need to right click the sheet tab for the sheet you want to use it in, choose View Code and paste it in. Of course the sheet must not be protected.
If you look up Protect (or something similiar) in VBA Help, you might be able to figure out how to add the unprotect/protect code.
If you can wait until Monday, I'll work on it then.
0
DAGS VBA unprotect worksheet for lots of hits on using VBA to Protect and Unprotect a sheet.
This is not tested...
Note: myPassword is the password for the sheet.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Row > 1 Then ActiveSheet.Unprotect Password = "myPassword" Range("C" & Target.Row - 1 & ":E" & Target.Row - 1).Copy _ Destination:=Range("C" & Target.Row) ActiveSheet.Protect Password = "myPassword" End If End Sub
0
Thanks Derby for your reply. I will try your VBA code and will report you whether it works or not after two days as i will be on hiking with friends.
0
code not working for me. perhaps some wrong with my excel setting that i could not find out.
0
Using words like "code not working for me" doesn't help us solve your problem.
It's really hard for us to see your spreadsheet from where we're sitting.
Perhaps you could describe what not working means. You know, things like error messages, symptoms, unexpected results, etc.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
