Formatting Part of a Cell - urgent help please

RichIsGod

New Member
Joined
Mar 28, 2013
Messages
4
Hi, i'm new to this so ill try to cover as much detail as i can to avoid confusion. For my final year project i have decided to make a hardvard referencing tool using VBA. The Form has a set of radio buttons at the top with the different media options to choose from such as a book, website, newspaper etc. when a radio button is selected, the textboxes and labels below change to tell the user what they should enter in each box. now to the part im having trouble with:

when they submit their input by clicking a button, the text is output into a single cell with addition punctuation added but certain parts such as book title need to be in italics. I have found how to format part of the string but the starting position will change everytime depending on the length of the strings that come before it.

This is what i have so far but i cant get it to work :(
Can anybody help please?
Code:
ActiveCell.Value = Txt3.Value + ", " + Txt4.Value + ". (" + Txt5.Value + ") " + Txt6.Value + ". " + Txt7.Value + Txt8.Value + Txt9.Value + Txt10.Value + ": " + Txt11.Value + "."
    
'Finds the starting position of the text to be italicised
    start1.Value = 7 + Len(Txt3.Value) + Len(Txt4.Value) + Len(Txt5.Value)
  
'Changes the text to italics from the starting point for the length of the string being italicised
    With ActiveCell.Characters(Start:=start1.Value, Length:=txt6Len.Value).Font
        .Name = "Calibri"
        .FontStyle = "Italic"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What exactly do you mean by "can't get it to work"? Do you get an error (if so, what error and where) or does it do the wrong thing, or nothing, or...
 

RichIsGod

New Member
Joined
Mar 28, 2013
Messages
4
Sorry, the error highlights this line:
start1.Value = 7 + Len(Txt3.Value) + Len(Txt4.Value) + Len(Txt5.Value)

i get error 424
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

I think this line
With ActiveCell.Characters(Start:=start1.Value, Length:=txt6Len.Value).Font
should be
With ActiveCell.Characters(Start:=start1.Value, Length:=Len(txt6.Value)).Font
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Sorry, the error highlights this line:
start1.Value = 7 + Len(Txt3.Value) + Len(Txt4.Value) + Len(Txt5.Value)

i get error 424
has start1 been defined and given an object?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Then remove the .Value from all references to Start1
 

RichIsGod

New Member
Joined
Mar 28, 2013
Messages
4
I Love You.
Thanks for helping to fix this, i know you probably facepalmed at some of my mistakes but i only have basic knowledge.
Much appreciated :D
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,749
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top