Forum Discussion
Questions about Using Formula on Shapes
Willy Lau wrote:
Is it possible to use a single formula for the 9 cells (shapes)?
I don't think so. What appears in a given cell is dependent (when using formulas) on the formula that is IN that cell. So nine cells need nine formulas (at a minimum).
Also, is it possible to make the shapes un-selectable under worksheet protection?
I think so, but you've hidden and protected everything, so it's hard to know how you're achieving what you're achieving even now. When you come asking for help here, you'd help us help you if you both unprotected and made visible the various functions/formulas/whatever.
I do not want VBA approach.
I'm with you on that general preference, but there are times when VBA works better than straight formulas/functions.
Anyway, why don't you reveal more of the inner workings for what you have here so those who know BOTH functions AND VBA can offer informed suggestions.
Thank you very much for your reply.
It is sad that I cannot use a single formula for the 9 cells. I thought I might use a formula with relative address for the 9 cells. I tried in many way, but I cannot make it happen.
Sorry for the hidden and protected cells.
- The hidden cells are the cells that I do not use. Hidding them is to put the focus on the cells that I do not hide.
- The protected worksheet is to show the "thing" I want to archieve. For example, just press "x" or "o" to play it. Cell [A1] and those description is to show the problem I am facing.
- The protected worksheet does not apply password. Just un-protect the worksheet can see the formulas.
Thank you for the suggestion to me to conside about VBA. I understand VBA might be a better way to it. However, if VBA is used, there is no way to use formula (I think VBA can create shapes with a specified image/move the focus to cells when a shape is selected/after user input, just clear the input but create a desired shape on the cell, etc. In addition, the target users do not want to enable VBA. Hence, VBA will not be used.
Currently, the way how is work is as below:
- I added comments in those 9 cells. The context of the comments is the named formula I use on the shapes that are in the 9 cells. Hence, I used 9 formulas.
>>> L = left,
>>> M = middle,
>>> R = right,
>>> T = top, and
>>> B = bottom
e.g. LT >> top left (using "__LT" named formula) - However, I changed the formula of the shapes in Cell [B3] and Cell [C3] from "__LM" and "__MM" to "__center" and "__center" (both shapes use the same formula). It is what I am hoping to achieve, "a single formula with relative address to be used in 9 cells" so that I do not need to create those 9 formulas.
- BUT, although the named formula "__center" is used in Cell [B3] and Cell [C3], the relative address in the formula does not work. The "does not work" means that the shapes in Cell [B3] and Cell [C3] do not "realize" that they are in Cell [B3] and Cell [C3]. They keep referring to Cell [A1].
- The above 3 points are related to my first question (is it possible to use a single [named] formula [with relative address] for the 9 cells [to replace those 9 named formulas]).
Note: the green words are added just in this post to describe more details. - The below points are related to my second questions (is it possible to make the shapes [in those 9 cells] un-selectable [and not be able to get focus when clicked, in order to click there to have the event to click on the cell itself, not the shapes] under worksheet protection?)
Note: the green words are added just in this post to describe more details. - Without VBA, can I click on those 9 cells (even click on the shapes' area) to let me input "x" or "o" directly in the cells?
Thank you very much.
