Hello,
This one should be simple, but I don't know the structure. I have a bit of code:
SHAPENAME = "Textbox 262"
inputtext = ['Pt Volume'!FW461]
With Me.Shapes(SHAPENAME).TextEffect
.Text = inputtext
.FontBold = msoTrue
.FontSize = 16
End With
i = 1
With Me.Shapes(SHAPENAME).TextFrame.Characters(i, 1).Font
.Size = 18
End With
startofword = False
For i = 2 To Len(inputtext)
If ((startofword) And (Mid(inputtext, i, 1) <> " ")) Then
startofword = False
With Me.Shapes(SHAPENAME).TextFrame.Characters(i, 1).Font
.Size = 18
End With
End If
If Mid(inputtext, i, 1) = " " Then startofword = True
Next
This code capitalizes a text string inserted in a textbox, like so: ='Pt Volume'!FW461. Works fine, but if I insert a row or column, FW461 doesn't adjust for the new cell location. So, I named cell 'Pt Volume'!FW461, TextboxLabel1, and changed the line above to inputtext = Range("TextboxLabel1"). But it didn't work. The syntax must be wrong. What is the correction needed?
Paul
Hi Paul
Maybe you can attach a sample file. It will be much clearer on what you are currently doing and how you did it.
Sunny
The actual file is gigantic and creating a sample from it could be a problem. Let me explain: I have labels in some parts of the file that are used as formulas in textboxes. These display just fine but Excel has no smallcaps function like in Word. The code above creates the smallcaps look by locating the first letter of a word in that formula to 18pt font, and the rest in 16pt. For example, ='Pt Volume'!FW461 refers to this text string: OUTPATIENT VISITS. The code will increase the font size of O and V. So far, so good.
The problem with code referring to cell addresses is that the code doesn't update when the cell address changes. If the code refers to Range("D10"), for example, and I move D10 to E10, the code won't automatically change to Range("E10"). But if I name D10 TextboxLabel1, inserting it as code like this--Range("TextboxLabel1"), it does. But I've got the syntax wrong. Leave out the Range and parentheses, like this, maybe? inputtext = "TextboxLabel1"
Hi Paul
Instead of using a textbox and VBA, maybe you can considered using picture-link instead.
See my example attached. Any changes to the cell will auto-update the picture.
Hope this helps.
Sunny
Hi Sunny,
I won't be typing a label into the cell, however. The label is a row header generated by an IF statement, such as, if "Inpatients" is selected in the settings data validation dropdown list, then the formula generates a label of "U.S. INPATIENT POPULATION," for example. The code above takes that uppercase label and formats it in smallcaps style like in MS Word. If the labels didn't change, I could just enter it the way I want in the textbox and leave it at that, and I have a bunch of those, but this code I'm writing about is for the exceptions. And it works great, too.
The problem is, I want to replace this line
inputtext = ['Pt Volume'!FW461]
with this line
inputtext = Range("TextboxLabel1")
where TextboxLabel1 is the range name for cell 'Pt Volume'!FW461, but the syntax must be wrong. Excel stops when it encounters that line.
Paul
Hi Paul
What type of textbox did you insert onto your worksheet?
Form Controls, ActiveX Controls or from the Insert-Textbox?
Insert textbox, I believe.
Hi Paul
I think you cannot format a formula in a textbox.
This is what I came up with. Not elegant (never done this before) but should work.
I have named the cell containing the text so that it is dynamic.
What the code does is to add the required text to the textbox and then format it.
I don't know if you textbox will be auto update when the cell change or you trigger it by clicking a button.
I chose to add a button in this example.
You will need to modify it to suit your needs.
Hope this helps.
Sunny
You're right, you can't format the formula, but somehow, my textboxes are formatted anyway. I didn't write the code, only adapted it. Yours here seems to do much the same thing. I'll see if I can adapt this to my situation and let you know what happens. Many thanks, once again.
Paul
I got delayed by some other projects, but I wanted to get back to you on this. I was able to adapt the code you wrote for my purposes, and it works perfectly. Thanks once again!
Paul
Hi Paul
Thanks for your feedback.
Happy to know it is working for you 
.
Cheers
Sunny
