Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an autocorrect entry italic
Hi there - please excuse my ignorance - I've never used VBA before so
I do need some help! I would like to make the replacement text into autocorrect formatted into italic text (as it will be latin text) - I have the following, Sub CreateLatin() ItemCount = Application.CountA(Range("Sheet1!A:A")) For Row = 1 To ItemCount ShortText = Cells(Row, 1) LongText = Cells(Row, 2) Application.AutoCorrect.AddReplacement ShortText, LongText Next Row End Sub However, I would like to make the LongText italic- please help me if you can, I'd be very grateful. Regards. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an autocorrect entry italic
"I would like to make the LongText italic"
I don't believe that is possible using AutoCorrect. Text formatting in a worksheet cell depends on how the worksheet cell is formatted, not on how the text is formatted when it is added to the cell. Worksheets(1).Range("A1").Font.FontStyle = "Italic" -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Beavy" wrote in message Hi there - please excuse my ignorance - I've never used VBA before so I do need some help! I would like to make the replacement text into autocorrect formatted into italic text (as it will be latin text) - I have the following, Sub CreateLatin() ItemCount = Application.CountA(Range("Sheet1!A:A")) For Row = 1 To ItemCount ShortText = Cells(Row, 1) LongText = Cells(Row, 2) Application.AutoCorrect.AddReplacement ShortText, LongText Next Row End Sub However, I would like to make the LongText italic- please help me if you can, I'd be very grateful. Regards. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an autocorrect entry italic
On 28 May, 15:14, "Jim Cone" wrote:
*"I would like to make the LongText italic" I don't believe that is possible using AutoCorrect. Text formatting in a worksheet cell depends on how the worksheet cell is formatted, not on how the text is formatted when it is added to the cell. Worksheets(1).Range("A1").Font.FontStyle = "Italic" -- Jim Cone Portland, Oregon *USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Beavy" wrote in message Hi there - please excuse my ignorance - I've never used VBA before so I do need some help! I would like to make the replacement text into autocorrect formatted into italic text (as it will be latin text) - I have the following, * *Sub CreateLatin() * * ItemCount = Application.CountA(Range("Sheet1!A:A")) * * For Row = 1 To ItemCount * * * *ShortText = Cells(Row, 1) * * * *LongText = Cells(Row, 2) * * * *Application.AutoCorrect.AddReplacement ShortText, LongText * * * Next Row * *End Sub However, I would like to make the LongText italic- *please help me if you can, I'd be very grateful. Regards. Hi Jim - sorry I seem to have omitted a rather important bit of info - the autocorrect of the latin names will actually be used in Word - and I had thought that the autocorrect text could be formatted? There is a link where a company has done something similar - http://www.biologicalrecordscentre.g...italicise.html but as I said, my VBA is effectively NIL - the code above has been snaffled from a website too! This is a request from one of our staff members and am trying to see if it can be done as the admin teams have to type up large field reports full of latin names, so they wish to use a standardised 3-4 code e.g abk and when they press space , for Ablepharus kitaibelli (in italics) to come out instead. Regards, Bev. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an autocorrect entry italic
Bev,
It sounds as if you are creating lists in Excel and transferring/pasting the data into Word. Formatting done in Excel is preserved when moved into Word. Microsoft Word has the ability to format data using Auto Correct In Excel you cannot format text using Auto Correct. Also, the link you posted has code to be used in Word, not Excel. In Excel, you would have to have the data entry people, highlight (select) the latin text and click the Italic button on the Excel toolbar. It would be possible in Excel, using VBA code, to change specified text to Italic. This would have to done after the lists are complete. The code would search against a supplied list of terms, find those in the Excel list and change the font style. Since you have not used VBA before, creating that code is probably best left to a paid consultant. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Beavy" wrote in message Hi Jim - sorry I seem to have omitted a rather important bit of info - the autocorrect of the latin names will actually be used in Word - and I had thought that the autocorrect text could be formatted? There is a link where a company has done something similar - http://www.biologicalrecordscentre.g...italicise.html but as I said, my VBA is effectively NIL - the code above has been snaffled from a website too! This is a request from one of our staff members and am trying to see if it can be done as the admin teams have to type up large field reports full of latin names, so they wish to use a standardised 3-4 code e.g abk and when they press space , for Ablepharus kitaibelli (in italics) to come out instead. Regards, Bev. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an autocorrect entry italic
Bev,
You can use the change event to change the formatting of all the desired words after the entry has been made in the cell. Create a named range Italicized (your entries currently in sheet1 B1:B???). Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. The named range Italicized can be dynamic. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myR As Long If Target.Cells.Count 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Not Intersect(Target, Range("Italicized")) Is Nothing Then Exit Sub For myR = 1 To Range("Italicized").Cells.Count If Range("Italicized").Cells(myR).Value < "" Then If InStr(1, Target.Value, Range("Italicized").Cells(myR).Value) 0 Then Application.EnableEvents = False Target.Characters( _ InStr(1, Target.Value, Range("Italicized").Cells(myR).Value), _ Len(Range("Italicized").Cells(myR).Value)).Font.Fo ntStyle = "Italic" Application.EnableEvents = True End If End If Next myR End Sub "Beavy" wrote in message ... On 28 May, 15:14, "Jim Cone" wrote: "I would like to make the LongText italic" I don't believe that is possible using AutoCorrect. Text formatting in a worksheet cell depends on how the worksheet cell is formatted, not on how the text is formatted when it is added to the cell. Worksheets(1).Range("A1").Font.FontStyle = "Italic" -- Jim Cone Portland, Oregon USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Beavy" wrote in message Hi there - please excuse my ignorance - I've never used VBA before so I do need some help! I would like to make the replacement text into autocorrect formatted into italic text (as it will be latin text) - I have the following, Sub CreateLatin() ItemCount = Application.CountA(Range("Sheet1!A:A")) For Row = 1 To ItemCount ShortText = Cells(Row, 1) LongText = Cells(Row, 2) Application.AutoCorrect.AddReplacement ShortText, LongText Next Row End Sub However, I would like to make the LongText italic- please help me if you can, I'd be very grateful. Regards. Hi Jim - sorry I seem to have omitted a rather important bit of info - the autocorrect of the latin names will actually be used in Word - and I had thought that the autocorrect text could be formatted? There is a link where a company has done something similar - http://www.biologicalrecordscentre.g...italicise.html but as I said, my VBA is effectively NIL - the code above has been snaffled from a website too! This is a request from one of our staff members and am trying to see if it can be done as the admin teams have to type up large field reports full of latin names, so they wish to use a standardised 3-4 code e.g abk and when they press space , for Ablepharus kitaibelli (in italics) to come out instead. Regards, Bev. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an autocorrect entry italic
On 28 May, 18:01, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Bev, You can use the change event to change the formatting of all the desired words after the entry has been made in the cell. *Create a named range Italicized (your entries currently in sheet1 B1:B???). Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. *The named range Italicized can be dynamic. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myR As Long If Target.Cells.Count 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Not Intersect(Target, Range("Italicized")) Is Nothing Then Exit Sub For myR = 1 To Range("Italicized").Cells.Count * *If Range("Italicized").Cells(myR).Value < "" Then * * * If InStr(1, Target.Value, Range("Italicized").Cells(myR).Value) 0 Then * * * * *Application.EnableEvents = False * * * * *Target.Characters( _ * * * * * * * *InStr(1, Target.Value, Range("Italicized").Cells(myR).Value), _ * * * * * * * *Len(Range("Italicized").Cells(myR).Value)).Font.F ontStyle = "Italic" * * * * *Application.EnableEvents = True * * * End If * *End If Next myR End Sub "Beavy" wrote in message ... On 28 May, 15:14, "Jim Cone" wrote: "I would like to make the LongText italic" I don't believe that is possible using AutoCorrect. Text formatting in a worksheet cell depends on how the worksheet cell is formatted, not on how the text is formatted when it is added to the cell. Worksheets(1).Range("A1").Font.FontStyle = "Italic" -- Jim Cone Portland, Oregon USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Beavy" wrote in message Hi there - please excuse my ignorance - I've never used VBA before so I do need some help! I would like to make the replacement text into autocorrect formatted into italic text (as it will be latin text) - I have the following, Sub CreateLatin() ItemCount = Application.CountA(Range("Sheet1!A:A")) For Row = 1 To ItemCount ShortText = Cells(Row, 1) LongText = Cells(Row, 2) Application.AutoCorrect.AddReplacement ShortText, LongText Next Row End Sub However, I would like to make the LongText italic- please help me if you can, I'd be very grateful. Regards. Hi Jim - sorry I seem to have omitted a rather important bit of info - the autocorrect of the latin names will actually be used in Word - and I had thought that the autocorrect text could be formatted? There is a link where a company has done something similar -http://www.biologicalrecordscentre.gov.gg/files/italicise.htmlbut as I said, my VBA is effectively NIL *- the code above has been snaffled from a website too! This is a request from one of our staff members and am trying to see if it can be done as the admin teams have to type up large field reports full of latin names, so they wish to use a standardised 3-4 code e.g abk and when they press space , for Ablepharus kitaibelli (in italics) to come out instead. Regards, Bev.- Hide quoted text - - Show quoted text - All - thanks for the help - will give the code a go - I'm sure that if I spend some time at it I can grasp the hang of VBA. Bev. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make an autocorrect entry italic
Jim,
No - what I am attempting to do is to autopopulate the autocorrect list with some short code entries that can be used by some of my users which will prevent them from having to type out the entire Latin string when typing field reports and site management statements in Word - as they can be tricky - I was hoping that I could force the formatting (as can be done in Windows autocorrect) to italic (as all Latin names for species must be shown as such) - given that someone has managed to do something similar in Word (i.e. the link I pasted), that something similar could be done - the list is to be kept in excel as the macro I have will populate autocorrect with the agreed codes, so its just the additional formatting requirement that is outstanding. I will give Bernie's code a go and see if that helps - I'm sure that after 14 years as a developer I can manage to get my head round VBA without the need to hire a consultant for such a small request. Regards, Bev. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA to Make Check Box Entry | Excel Programming | |||
Italic | Excel Worksheet Functions | |||
Can I make a data entry box? | Excel Discussion (Misc queries) | |||
Lookup and change to italic italic | Excel Worksheet Functions | |||
How do I make a unique entry | New Users to Excel |