Does anyone know if VBA would allow a single cell to have a CONCATENATE string with multiple Font Styles and/or Sizes?
Scenario:
Could D5
=CONCATENATE(A1,B1,C1)
Where A1 is Font 20 bold
Where B1 is Font 12 regular
Where C1 is Font 20 bold
So data string in D5 would reflect that formatting I.e.
example: ABC123456XYZ
If so I would love the formula please??
Cheers
Brett
Hi Taupo,
If you turn on the macro recorder and select part of a cells text and then change its fonts you can see what the macro is doing. See the attached for an example of what you suggest. I turned on the Macro Record and did your above text.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
As MarvinP's example shows, you can easily format different parts of a text literal -- some bold, some different font sizes, etc.
However, to my knowledge, there is no way to accomplish the same thing on the result of a formula. You can copy -> paste special -> as values (or the equivalent in VBA) to convert the result of the formula to a text literal, then format the different characters as you desire. But you cannot accomplish the same thing while leaving the formula intact.
Originally Posted by shg
Hi Marvin
You're a legend mate I didn't think it would be possible!!!! Can I draw on your knowledge a little further please regarding the purpose of this?
The spreadsheet i am applying this to is a transaction register for the sale of second hand books for a student guild, where one row = 1 x transaction / book and the start cell this Multi font style and size would be in is Z10. The product/data string of the cell would then be used in another macro as a copy and paste to a DYMO label printer and the label affixed to the spine of the physical book.
So in reality your macro would need to do the following:
Create the text string as it does using Z10 as the Range Select; however, the exact string format I would want it to produce is:
The first 3 alpha characters in Font size 20 and Font style Bold, then 4 x spaces, then the next 14alpha/numeric characters in Font Size 12 and Font Style Regular, then 4 x spaces, then the last 3 x alpha characters in Font Size 20 and Font Style Bold.
So that would deliver exactly what I need in Z10 but I want the same to happen in Z11, Z12, Z13 etc. which will all have exactly the same formatting requirements but the actual data in each cells string would be different e.g.
Z10 could be ACC BB15001-65.5 $78.5
Z11 could be INT BB15002-42.0 $54.5
Etc.
So can the initial macro built on Z10 include code so it is incremental i.e. could I drag Z10 to Z11, Z12, etc. and the macro adjust accordingly to reference run on each different cell?
I hope so?
Cheers
Brett
Hey Marvin
Was hoping you had seen my return post and were able to provide additional information please?
Cheers
Hi Taupo,
I just got back from a trip and am trying to understand your additional question. Z10, Z11 .. Z12 confuses me. Do you mean that you want to print something that is in Z10 then the thing in Z11?
If you could attach a sample workbook of a simple example with two or three samples I think it might be accomplishable. MrShorty may have not thought of the INDIRECT() function or a User Defined Function (UDF) that VBA might supply.
To attach a sample workbook, click on Go Advanced and then the Paper Clip Icon above the advanced message area.
Hi Marvin
Thank you for getting back to me it is greatly appreciated. Yes, apologies for my first reply in the context of you finding it confusing without a sample workbook; I have attached this now and hope it, and the following, clarify what I would ideally like the VBA script, you provided, to do as extra. There are other sections to the workbook but for the purposes of this exercise I have deleted them so only the ranges applicable to this question are present OK:
1. The workbook is for recording transactions relating to ‘buying-back’ textbooks no longer required by students.
2. Primary rule is: 1 x row = 1 x transaction for 1 x textbook
3. Conditions:
Once unique data is manually entered for a transaction:
Example:
Row 12 – cells D12 to M12, where
a. Cell N12 is specifically formatted to display the sell price with the $ symbol (see
b. Cell R12 uses a CONCATENATE TEXT formula to create a text string (including spaces) which represents the agreed naming convention to be used as the transaction receipt Fie name. My intention is to use this as a copy/paste action in a VBA macro that auto-populates an Excel.xltx buy-back receipt, for each transaction.
c. Cell S12 has a custom date format of yyyymmdd and = D12; this is required for the 4th component of the CONCATENATE in R12
d. Cell T12 uses a UPPER(LEFT TEXT formula to pick the first 3 alpha characters of the Text Title in H12; this is required for cell U12. This is used for filing the physical textbooks we buy back i.e. they are displayed in alphabetical order based on the first three letters of the textbook title.
e. Cell U12 is currently set up to use a CONCATENATE TEXT formula to create a text string (including spaces) which represents the agreed naming convention to be used for the label that is attached to the spine of the physical textbook. My intention is to use this as part of the same VBA in b. above as a copy/paste action to populate the label template in our DYMO LabelWriter 450 Turbo application.
4. Desired additional conditions for current script:
Making the textbook ‘spine’ label user friendly and easily readable was the reason for starting this post and brings us to where we are now i.e. as previously prescribed I would ideally like your VBA to:
a. Present the text string, for each transaction, in Column U to be custom formatted as follows (using Row 10 as the example)
String component 1: Cell T10 – Font Size ‘20’, Font Style ‘Bold’
String component 2: 5 x spaces
String component 3: Cell C10 – Font Size ‘12’, Font Style ‘Regular’
String component 4: 1 x space
String Component 5: Cell L10 – Font Size ‘12’, Font Style ‘Regular’
String component 6: 5 x spaces
String Component 7: Cell N10 – Font Size ‘20’, Font Style ‘Bold’
(N10 needs to display with $ symbol and 2 x decimal places)
Desired result example - Row 10: INT BB15001 67.5 $82.50
Desired result example - Row 11: ESS BB15002 52.5 $64.50
Etc.
I ‘played’ with the script extensively trying different variations but the only option that would work, as written, was to return the string without the desired spaces, and the sell price displayed without the $ symbol and to 1 decimal point only i.e. 00.0
Anyway hope the attached and the details above are sufficient Marvin and that you can see a way of achieving what I would ideally like; look forward to hearing from you.
Cheers
Brett
Last edited by Taupo58; 08-26-2015 at 09:57 PM.
deleted duplicate reply post
Last edited by Taupo58; 08-26-2015 at 09:06 PM. Reason: Inadvertently posted same reply twice
Sample Workbook
Hi Brett,
Darn, after working on this for awhile, I don't seem to be making any progress. I'll bump it up to the smart gurus and see what happens.
Hi Marvin
OK thanks for that i really appreciate it................maybe it's a case of there are limitations to what can be achieved in Excel maybe...........hmmmm!!!!
Cheers
Brett
When you say script, do you mean VBA code? If so, try this out. The macro currently looks at U10 ("INT BB15001 67.5 $82.50") and does the formatting you require.
But firstly, i've updated the CONCATENATE Formula in column U to include the $ sign.
Formula:
Please Login or Register to view this content.
Please Login or Register to view this content.
Hi quekbc
Thanks very much for your code which returns what I defined which is fantastic. Now that i know it can work, and as per the conditions I explained about the workbook, this transaction register will operate on a yearly basis to record all transactions for that given year and based on averages this could be as many as 300 transactions in a one year.
Therefore as each row = 1 x transaction for 1 x book then, unless we buy back exactly the same book from another student(s), the data string in each cell in column U is going to be different. With that in mind the macro needs to be incremental across 300 x U column cells if you understand where I'm coming from so:
1. Can the code be amended to do this i.e. can a macro be coded so it will do what a formula does when you drag it that is, the macro range reference changes to the actual cell range reference you want the macro to run against automatically?
2. The test example you have sent back to me only populates the macro outcome in cell Z10; i tried getting it to display in other cells but it defaults to Z10?
a. What part of the code is saying the result can only be displayed in Z10
b. does this mean i need to add an additional column to the workbook so the macro result can be published adjacent to its U column cell
3. I would also appreciate your guidance on how this macro could be set to run automatically based on a defined trigger; for example could it be set to continuously run and display the result as the various components of the string appear in the U column cell or what can you suggest?
I hope i have explained that concisely enough for you to know what I am on about
Cheers
Brett
Apologies, I've made an assumption about your familiarity with VBA and have just put in a proof of concept where one can take it and extend it into something else. Let me answer your question 1 by 1 and then I'll code up something suitable.
Well, yes and no. It can't be done in a cell that contains formulas - i.e. it cannot be a macro user defined formula. It can, however, be made to run through the entire list - generate resulting text, and format them accordingly. Does this work for you?
The part that says Set rng = Range("U10") means that it will only look at the code in U10. The code Set rng = rng.Offset(0, 5) then proceeds to work on Z10. - this was done as a proof of concept, and was intended to put into a loop in the end result.
No, this can be done directly into column U - by macro, not by formulas.
Yes, this can be done to calculate automatically and it's done via an Event triggered macro (an Event could be something like, a cell has changed, or the user double-clicking). However, I personally do not like the use of Event macros just because I don't like things running without me wanting it run. I would prefer the use of a button, where the user can click it whenever he/she wants the values in column U updated. Tell me which you prefer and I'll code it up accordingly.
Hi quekbc
Oh yes I definitely fit into what i hope is the majority of forum users i.e. "I know exactly what I want a formula /code to do but only enough about how to achieve it to ensure I well and truly stuff it up"
Yep I am a genuine amateur but am desperate to learn and compared with a few months ago have got better but nothing in comparison to someone of your experience.
OK so to the points above:
Point one
I believe what you are saying is the macro can be coded to apply to every cell in Column 'U' and return the value applicable to each each row? If that's the case then yep that certainly works for me.
Point two:
Yeah sorry about that; don't know why I even asked as I did know that one....Doh!
Point three:
Yes for sure anything that will reduce unnecessary columns is good by me; I am presuming you are saying the code can do everything I need and put it straight into column 'U' cells without the need of the existing Concatenate formula in 'U' ?
Point four
Yeah look with the little I do know I appreciate it is always better to 'physically' start a process and button activation is fine by me. I am presuming you are proposing one button, which I could presumably place in a strategically appropriate location?
Thanks very much for your ongoing support
Cheers
Firstly, I've put in some dummy inputs to test it further.
Okay, so here goes. Use the code below, assign it to a button, and click the button to generate the labels. See attach for a working example.
Please Login or Register to view this content.
Last edited by quekbc; 08-28-2015 at 12:59 AM. Reason: Fixed code - see subsequent posts.
Hi quekbc
Wow that was fast you obviously are very experienced at this; thanks so much mate it works brilliantly! When I first saw the results i saw that something wasn't 'quite' right and very quickly realised it was caused by the last line of code for the string having "L" for the range instead of "N", I made the change and thereafter all was sweet!! This has made a big difference through automating what i wanted and i thank you very much.
Pushing the envelope further would you have any objection if I forwarded another VBA requirement at a later date to review i.e. once I've put the effort into trying to do what I can first?
Cheers thanks mate
Brett
Hey Brett, glad that it's working. Good catch with the "L" reference. Missed that bit.
Happy to help out with another VBA requirement - just start a new thread and either myself or someone else can have a go at it.
Thank you that's terrific.
Cheers
Brett
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks