• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

A Bit of Help|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / A Bit of Help|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosA Bit of Help
sp_PrintTopic sp_TopicIcon
A Bit of Help
Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
1
February 21, 2018 - 1:00 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
February 22, 2018 - 5:21 pm
sp_Permalink sp_Print

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

Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
3
February 23, 2018 - 6:22 am
sp_Permalink sp_Print

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"

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
February 23, 2018 - 11:18 am
sp_Permalink sp_Print

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

Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
5
February 23, 2018 - 4:21 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
February 23, 2018 - 4:28 pm
sp_Permalink sp_Print

Hi Paul

What type of textbox did you insert onto your worksheet?

Form Controls, ActiveX Controls or from the Insert-Textbox?

Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
7
February 23, 2018 - 5:27 pm
sp_Permalink sp_Print

Insert textbox, I believe.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
February 23, 2018 - 5:43 pm
sp_Permalink sp_Print

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

Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
9
February 24, 2018 - 4:34 am
sp_Permalink sp_Print

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

Avatar
PaulFogel
Member
Members
Level 0
Forum Posts: 81
Member Since:
July 7, 2016
sp_UserOfflineSmall Offline
10
March 6, 2018 - 12:34 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
11
March 6, 2018 - 1:00 pm
sp_Permalink sp_Print

Hi Paul

Thanks for your feedback.

Happy to know it is working for you Smile.

Cheers

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mark Carlson
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.