display result and hide macro formula

[acounter2]

Hi everyone,

Excel 2003.
I have been struggling for an hour how to hide a number to text macro in a way that only macro result is displayed in a cell.
Any ideas?

(I have one 2-3 years old xls, where I have managed with task, but now can't figure out how and how to unhide the macro
Can it be password protected somehow?

Thanks!

 

[VoG]

If I understand correctly, in the Visual Basic Editor Tools > VBAProject Properties, Protection tab, tick Lock Project For Viewing and set a password. Save, close and reopen the workbook for the protection to take effect.

 

[acounter2]

Thanks, but the VBA macro is still accessible and not hidden, but I'm not able to figure out the syntax in a cell as for uknown reason it doesn't work as it used earlier and the result in the cell is still alive but erratic.

I guess I have played with worksheet protection but there seems to be a single option to add protection and not to unprotect anything nor there doesn't seem to be any passwords and ... so I'm stumbled what next to do.

 

[VoG]

I'm struggling to understand what you want. If you want to convert formula/UDF results to their values try something like

Sub test()
With Range("A1:A10")
    .Value = .Value
End With
End Sub

 

[acounter2]

Thanks for guessing but problem is erratic formula result which I'm not able to fix because there is no visible formula in cell. I have got a good advice how to hide formula in a cell but now years later I don't have any idea how to gain access to this cell hidden formula.



Formula itself is a macro numbers to text which result doesn't look correct anymore. When I change numbers it reflects in a cell changes but not correctly.

I expected it to be a a worksheet protection but it doesn't seem to be active there.
Cell properties - hide and lock were active but unselecting these still didn't reveal cell formula content.
I hope it isn't lost forever by some Excel trick.

 

[Smitty]

Check the cell's format (Format-->Cells).

If it's set to Custom-->;;; the cell contents won't be visible; you'll only see the formula in the formula bar.

HTH,

 

[acounter2]

Thanks, but the cell format is general.

I remember that perhaps there was a function to hide formula and display result in cell of Excel 2003. But can't recall how and neither goggling doesn't help.

Btw. I have there also an obsolete word in a notes which may mean the password but no place to write the password to.

Protect sheet and protect workbook options both offer to protect but not to unprotect.

 

[mikerickson]

You can see what the formula is with

MsgBox ActiveCell.Formula

If there is no formula in the cell, have you looked at the Change event to see if thats the source of the cell's value.

 

[acounter2]

Thanks, but while being rather occasional Excel content creator, I'm not sure how to use: MsgBox ActiveCell.Formula. I tried to create a macro but it failed.

In the VBA code there isn't used any Change event commands. I guess that's where one may check it.

 

[acounter2]

I created a macro

Sub valem()
MsgBox ActiveCell.Formula
End Sub

But it displays the same formula result.
What does this result mean? That there is something else and not an hidden formula in a cell???