Excel_symbol_€_empty_cell
Solved
benji71
Posted messages
789
Status
Member
-
benji71 Posted messages 789 Status Member -
benji71 Posted messages 789 Status Member -
Hello everyone,
I hope you are doing well
I come to you with a little question.
To facilitate the understanding of my question, here is the file: https://www.cjoint.com/?3DelLYdl422
In B6:B10, I have cells where amounts can be entered (I say can because sometimes it's not the case in all of them)
When I put a 0 or a number in a cell, the € symbol appears.
Once there is no number in the cell, the symbol disappears (which is normal)
I want to know if there is a way to make the € symbol remain visible in the cell regardless of whether there is something in the cell or not. In my opinion, this is not possible unless I leave the zero.
The problem is that when I need to clear the content of the sheet (it is a sheet to be used for different people), I naturally clear the zero and thus my € symbol disappears.
The ultimate goal is to always see the cells where an amount should come in.
I think my request is somewhat futile but…who knows…I await your opinion.
Have a very nice day
Berni
I hope you are doing well
I come to you with a little question.
To facilitate the understanding of my question, here is the file: https://www.cjoint.com/?3DelLYdl422
In B6:B10, I have cells where amounts can be entered (I say can because sometimes it's not the case in all of them)
When I put a 0 or a number in a cell, the € symbol appears.
Once there is no number in the cell, the symbol disappears (which is normal)
I want to know if there is a way to make the € symbol remain visible in the cell regardless of whether there is something in the cell or not. In my opinion, this is not possible unless I leave the zero.
The problem is that when I need to clear the content of the sheet (it is a sheet to be used for different people), I naturally clear the zero and thus my € symbol disappears.
The ultimate goal is to always see the cells where an amount should come in.
I think my request is somewhat futile but…who knows…I await your opinion.
Have a very nice day
Berni
11 answers
-
Hello,
(I haven't looked at your file)
Regarding the "coding" to display the "€" symbol, I don't know.
I suggest another, simpler solution
If I understand correctly, you want all users to know which cells will have amounts.
Would adding a "color code" (highlighting) to those cells help you?
Example: B6 to B10 in apple green ^^
Have a good day -
Hello Dracknard,
thank you for your response and proposal.
unfortunately no, this "small" table is part of a sheet that will be printed in B/W.
it is very possible that there is no solution because I think that what I am looking for is impossible, but I am asking the question to be sure.
thank you,
very good afternoon,
berni -
Hello everyone
Maybe a solution that could work for you but through a macro!
https://www.cjoint.com/?3DemkTgbOGo
Have a good continuation -
Hello ccm81,
it's a pleasure to read you.
I hope you are doing well.
I did think about the macro but my cells are not next to each other.
I will keep your idea in mind but...
a big thank you to you.
berni// -
It’s not really a problem
Const range1 = "A2:B4" Const range2 = "C5" Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, range As Range Set range = Union(Range(range1), Range(range2)) If Not Intersect(Target, range) Is Nothing Then For Each c In Target If Not Intersect(c, range) Is Nothing Then If c.Value = "" Then c.Value = "€" End If Next c End If End Sub
cdlmt-
-
Good evening dear ccm81,
a huge thank you for responding to my post.
there is something that escapes me or that I did wrong (that's why I'm sending the file back... you never know...)
https://www.cjoint.com/?3Dfv2QJchPV
I changed what I thought I needed to change but... it's still not working...
for example, if I go to c6 and delete the content, the € symbol disappears...
how does the Excel sheet "know" based on VBA code that the € symbol should permanently remain in cell c6, for example?
I think in hindsight that my request must really be "annoying," so if it's too "annoying," don't stress over it... right...? ;-)
thank you for your opinion/advice..
have a very good evening...
berni///
-
-
Hello everyone,
It seems to me that you are making things more complicated. In a cell, you put the following format:0.00 €;-0.00 €;€
You enter a 0 and only € is displayed. You just need to copy your cell into all the cells where you want this format and the function is fulfilled. All good? :)
--
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry -
Hello,
I think like Bginforme, whom I salute, but starting from his format, I would capture this personalized format
0.00 €;-0.00 €;""
it is possible to add a color for negative values
0.00 €;[Red]-0.00 €;""
and why not one for positive values
[Blue]0.00 €;[Red]-0.00 €;""
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.-
Hi Mike,
The question is: "is there a way to keep the € symbol visible", why did you remove my euro?
It's not very convenient for me if you're taxing my euros: I didn't have many and I was happy that benji71 wanted to put in a lot, as for the taxes, I have enough already ;-) -
-
-
And while I see that not everyone is following except for the top student, who is ccm81... congratulations to him.. :-)
your suggestions are good (as usual) but... I’m looking for a way for the € symbol to remain displayed in the cell even if it doesn’t contain a number.
why? in a file, I have a base sheet that I use several times for different families.
each time I redo the calculation, I have to reset my sheet to "zero" and thus without any numbers in the cells.
to better communicate with the community, I'm reposting the file but with my calculation sheet on sheet two: https://www.cjoint.com/?3DfxH2M04bO
you will see on the sheet several cells with the € symbol (i13, i14, i15..)
every time I have to make a calculation for a family, I use this sheet.
and therefore, every time I have to delete its content otherwise the result in AA2 will be distorted.
the "catch" is that if I delete the cells, I also delete the € symbol.
my question is therefore to know if there is a "way" to keep the € symbol in the cell even if it is empty.
but once again, don’t stress over this, it's not that super, super important.. but if you like to think hard.. then... :-)
thank you for your valuable advice and good evening/night to all,
berni/// -
-
-
The cells modified by the macro are only those that are already formatted in the "€" currency format (and right-aligned if you want the € symbol to be right-aligned when the cell is empty)
By the way, we can add a little button that would set those cells to "zeuro" for a small cost (in euros of course)
Have a nice day and lots of euros to everyone -
Hello benji71 and everyone on the forum,
Since you apparently care about your macro, you might as well make a macro that properly clears the entire range concerned:
Public Sub effacer() Dim elm As Range For Each elm In Union(Range("I12:I48"), Range("L12:L48")) If Left(elm.Formula, 1) <> "=" _ And InStr(1, elm.NumberFormatLocal, "€") > 0 Then elm.Value = 0 Next elm End Sub
You can replace the zero I've put with your € but your calculations won't work anymore...
--
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry -
Hello everyone,
thank you all for getting involved to help me find a solution.
it's always nice to see that you can count on those who are more insightful and skillful than yourself. The "dinosaurs" of this site are really gentlemen, thanks to them...they will recognize themselves (with or without € :-)
best regards,
berni/// -
Your symbol is easy; just copy and paste it into the box unless you absolutely want to automate it.
