VBA Label Caption Won't Display When Excel Column Width is 0

[Annie Bender]

Wow, I finally got through my first VB "tutorial" project and everything works nice, except one userform label caption will only display as long as the Excel column it draws data from is set wide enough to show the data on the spreadsheet too. But that data is not supposed to be visible to spreadsheet users, as it involves intermediate calculations only. I don't see anything special about either that particular data (it's numeric) or about the coding, but here it is, in case you can spot something there.

Label4.Caption = Label4.Caption + Worksheets("Next Service").Range("I51").Text & " mi" & Chr(13)

Otherwise, are there any suggestions you can offer me? Oh, and if I set the column width to some small width, both the data cell and the userform label caption show ###. I know why excel displays that, but not why it would be rendered that way in the VBA project or what to do about it. TIA

Annie

[SammyB]

I agree that is should work, but it is always shakey to use range.Text because it returns what shows on the worksheet, but I didn't know that hidden cells have blank .Text. Use .Value instead and let VB convert it to a string. HTH --Sam

[Annie Bender]

Hi Sammy. Thanks for the response. Using .Value in place of .Text leads to a "Type Mismatch" run-time error. Anyway, the label caption will render all of the desired worksheet cell contents correctly, but only if the worksheet data column is wide enough to display it too, and that's what I don't want to have to do. The user has no interest in that data and so shouldn't even be seeing it on the worksheet. And yet, when I set the column width to 0 on the worksheet to hide it there, it's hidden in the userform label caption too! I hope someone has come across this odd situation and has already figure it out. I'll keep working on it too, but when it comes to VB, I don't yet have a very big tool chest to reach into. Ha ha.

Annie

[Annie Bender]

Resolved

Novice VBA mistake (I've already made a ton of those). I'm posting the solution for anyone facing a similar problem in the future who comes across this thread.

Sam was on the right track to use .Value in place of .Text, but I kept getting the "Type mismatch" error. Eventually, it was pointed out to me that my coding line was using a "+" to join two parts of the label together, rather than the "&". Not only did "&" and ".Value" together take care of the type mismatch error, but the caption now renders correctly and displays, even with the worksheet column width set to 0. Annie is happy now. Thanks.

Annie

[kadghar]

agree with annie and Sammy:

When working with strings, or not, always use & instead of +, just to avoid mistakes.

Instead of using Range and the cell's name, use the Cells index, e.g. instead of Range("I51"), i'll rather use Cells(51,8).
And yes, Value is a property that is always there.
And instead of CHR(13), use the VB constant for new line (that combines CHR(13) and CHR(10)): vbNewLine
So perhaps this will do:

Label4.Caption = Label4.Caption & Worksheets("Nex t Service").Cells (51,8).Value & " mi" & vbNewLine