I have the following Excel VBA code:
Sheet1.Columns.ColumnWidth = 8.14
Sheet1.Rows.EntireRow.RowHeight = Sheet1.Cells(1).Width
Sheet1.Cells.WrapText = True
Sheet1.Cells.NumberFormat = "F0 P0 C0" & vbLf & "00 00 00"
Sheet1.Cells.Value = 123456789
The first two lines make the cells in my sheet perfect squares of 8.14 horizontal points and the following lines intend to make each cell look like this (aka displaying a 9 digit number in visually "separated" sections within the same cell):
F1 P2 C3
45 67 89
The problem is that Excel replaces my number (which should exactly fit into that 8.14 pts sized cell on 2 lines, since it's word-wrapped) with hash signs (#), because of its perceived length - and I don't want that.
Is there a way, through Excel settings or VBA code, to prevent this automated behavior without changing the cell format to 'General' or 'Text' or increasing the cell's width?
Now I've seen this question answered before, but all the answers referred to changing the cell format or size, which I can't do, for a couple of reasons:
this sheet is intented to be a more or less geographical "map", in which each cell is a point on that map, so naturally I need as many "points" as possible displayed in the visible section in Excel. If I make the cells (squares) larger, I can barely fit a 10x4 grid into the visible section, at 100% zoom - since I must double the cell's previous size to a huge 124 pixels, in order for Excel to display my number unaltered
if I change the format to 'Text' (aka String cell values), my Excel file becomes huge (cca. 1MB) and virtually unusable, since the map is around 255x128 "points". 255x128 number formatted cells work blisteringly fast compared to the same amount of 'text'/string formatted cells
if I change the format to 'General' is more or less the same story as above, plus, I can't use custom formats to "separate" my number sections as seen in my example (and yes, this is the intended appearance).
EDIT: While checking http://datapigtechnologies.com/blog/index.php/forcing-carriage-return-in-custom-date-format/ I found this from a certain Dave (credits to him), which is also the solution to my problem:
If you try to shrink the columnwidth, you’ll notice you’ll see ####’s way before you (or I) think you should. But a slight change can fix this. On the alignment tab of the format cell dialog, turn off wraptext (if it’s on). Turn on “shrink to fit”, then turn on wraptext.
I tried it and it worked! The VBA code for this is equally simple - adding the code below right after my initial code did the trick:
Sheet1.Cells.WrapText = False
Sheet1.Cells.ShrinkToFit = True
Sheet1.Cells.WrapText = True
I didn't want to "cheat" and answer to my own question, so I wrote this as an edit to my original post.
