By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,359 Members | 1,543 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,359 IT Pros & Developers. It's quick & easy.

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

P: 15
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.

Expand|Select|Wrap|Line Numbers
  1. 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

Mar 30 '10 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 807
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
Mar 30 '10 #2

P: 15
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.

Mar 30 '10 #3

P: 15
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.

Mar 31 '10 #4

Expert 100+
P: 1,295
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("Next Service").Cells(51,8).Value & " mi" & vbNewLine
Apr 2 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.