This version worked ok for me--ignore the formatting when it's done:
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim wks As Worksheet
Dim isBold As Variant
Dim myPfx As String
Dim mySfx As String
Dim inBoldSequence As Boolean
Dim NewString As String
Dim myChar As String
Set wks = Worksheets("Sheet1")
myPfx = "<strong>"
mySfx = "</strong>"
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "no text constants!"
Exit Sub
End If
For Each myCell In myRng.Cells
If Len(myCell.Value) = 0 Then
'do nothing
Else
NewString = ""
isBold = myCell.Font.Bold
If isBold = True Then
'whole cell is bolded
NewString = myPfx & myCell.Value & mySfx
ElseIf IsNull(isBold) Then
'mixture of bold and non-bold
inBoldSequence = False
For iCtr = 1 To Len(myCell.Value)
myChar = Mid(myCell, iCtr, 1)
If myCell.Characters(iCtr, 1).Font.Bold = True Then
'found a bold character
If inBoldSequence Then
'do nothing special, just add the character
NewString = NewString & myChar
Else
'turn it on
inBoldSequence = True
'add the prefix
NewString = NewString & myPfx & myChar
End If
Else
'found a regular character
If inBoldSequence Then
'turn it off
inBoldSequence = False
'add the suffix
NewString = NewString & mySfx & myChar
Else
'not in bold, just add the character
NewString = NewString & myChar
End If
End If
Next iCtr
'check to see if last character was bold
If inBoldSequence Then
'if it was, add the suffix
NewString = NewString & mySfx
End If
End If
End If
myCell.Value = NewString
Next myCell
End With
End Sub
jeff wrote:
>
> Okay, I think you've answered part of my question... I can't do it the way I
> want to do it!!!
>
> How about this instead...
>
> When writing the questions in Excel, I will simply use the actual BOLD
> that's in the toolbar, for anything that should be bolded.
>
> Since I'm running a macro to create the .txt file, I'll just 'scan' through
> each question first (character by character), and make note of where the
> font.bold=true starts and stops. I will then insert <strong> and </strong>
> into the cell at the
> these points.
>
> Something like this perhaps (I'm new to VBA, so that's why I'm asking). Not
> sure what the correct command is to determine the length of a text string in
> a cell is, and I'm not sure how to insert text at a certain point within a
> string... I'm just making this up off the top of my head...
>
> Range("C3").Select
>
> startBold = 0
> startHTML = "<strong>"
> stopBold = 0
> stopHTML = "</strong>"
> txtLength = LEN(C3)
>
> for x = 1 to txtLength
> if startBold = 0 then
> if (ActiveCell.Character(x,1).font.bold = true) then
> startBold = x
> end if
> end if
>
> if startBold > 0 then
> if (ActiveCell.Character(x,1).font.bold = false) then
> stopBold = x
> end if
> end if
> next x
>
> txtLength = LEN(startHTML)
> ActiveCell.Insert(startHTML).Characters(startBold,1)
> ActiveCell.Insert(stopHTML).Characters(stopBold + txtLength,1)
>
> Any pointers would be appreciated.
> Thanks
>
> P.S.- GIFT format is just a text file, with a standardized layout for the
> importing and exporting of questions & answers. Here's a link if your
> interested:
>
> http://moodle.dcu.ie/help.php?module...ormatgift.html
>
> "Dave Peterson" wrote:
>
> > I don't think I'd base anything on the actual editing of the value--since no
> > macro can help with that.
> >
> > But if there are certain words that always get bolded (and always get bolded
> > whenever they're used), you could use a macro--essentially a series of
> > edit|replaces:
> >
> > select the range
> > edit|replace
> > what: _example_ (_ represents a space character)
> > with: _<strong>example</strong>_
> > replace all
> >
> > Or depending on how you generater this GIFT file (never heard of this), you may
> > be able to do the same thing while you're creating the file.
> >
> >
> >
> > jeff wrote:
> > >
> > > It would be selected, while you are in the cell, actually typing in the text,
> > > and you would either hold down shift and use the cursor keys to choose your
> > > text within the cell, or use the mouse to choose the text I want.
> > >
> > > Here are more specific details of what I am really trying to accomplish, in
> > > case there is another way to do it:
> > >
> > > Basically, we are writing questions, and are exporting them in a text file
> > > (GIFT format) that will be imported into another program. If I BOLD a word in
> > > the cell, when it is exported into the text file it gets stripped of the
> > > BOLD. The program we are importing into uses HTML tags, so while I am
> > > entering my question into Excel, I want to be able to put <strong> on the
> > > left side of my cursor, and </strong> on the right side of cursor. I can then
> > > type the word I want to be BOLDed in between <strong> and </strong>, and when
> > > I export the text file, it will contain this formatting.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > When you're editing that cell, no real macros can run.
> > > >
> > > > You may be able to accomplish it another way--if you share the rules of how you
> > > > selected that "example" text.
> > > >
> > > >
> > > >
> > > > jeff wrote:
> > > > >
> > > > > This is probably an easy question for you experts out there...
> > > > >
> > > > > I have the following text in cell C3: "This is example text."
> > > > >
> > > > > I select cell C3 with the mouse, then press F2 to edit, and then select the
> > > > > word 'example' with the mouse. Here's my problem... I now want to run a macro
> > > > > (in the Right-Click mouse menu), which will:
> > > > >
> > > > > 1) Cut the selected text out
> > > > > 2) Add the letters "AA" to the beginnning of this cut text
> > > > > 3) Add the letters "BB" to the end of this cut text
> > > > > 4) Reinsert the new text back into the original text, so the text in cell C3
> > > > > would now read "This is AAexampleBB text."
> > > > >
> > > > > Any help would be appreciated.
> > > > > Thanks
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
Similar Threads
