Remkomplekty.ru

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba font

Excel vba font

In this article I will explain how you can change the size of the font for a cell or range in Excel using VBA. I’ve also provided an example on how you can retrieve the size of the font used in a cell or range.

You can download the code and file related to this article here.

Basics:

The following line of code changes the size of the font used in cell A1 to l8:


After:


The line below retrieves the size of the font used in cell A1 and prints the value in cell B1:

Cells(1, 2) = Range(«A1»).Font.Size

Example 1, Set Font Size:

In this example there are drop down lists in row 2. The user selects a font size from them. Upon selecting a value from the drop down lists the size of the font in row 1 is updated:


After the user selects 14 from the drop down list the size of the font in cell D1 is changed to 14:


The drop down lists are created using data validation. The source for the data validation is in sheet 2:


For more information about using drop down lists in Excel please see Excel VBA Drop Down Lists.

The program uses a Worksheet_Change event handler. The event handler executes when the user selects a new value from the drop down lists:

‘executes when the user selects a new value from
‘the drop down lists
Private Sub worksheet_change( ByVal target As Range)
Dim i As Integer
‘loops through the cells in row 1
For i = 1 To 4
‘adjusts the font of the cells in row 1 to
‘the font selected in row 2
Range(Cells(1, i), Cells(1, i)).Font.Size _
= Cells(2, i)
Next i
End Sub

Example 2, Get Font Size:

The example below gets the font size used in a cell. There is text written in row 1. When the user presses the Run button their size is printed in row 2:


After changing the font size of the cells in row 1 and pressing the Run button:


The code used in this program can be seen below:

‘executes when the user presses the Run button
Private Sub btnRun_Click()
Dim i As Integer
‘loops through the cells in row 1
For i = 1 To 4
‘gets the font size
Cells(2, i) = Range(Cells(1, i), _
Cells(1, i)).Font.Size
Next i
End Sub

You can download the code and file related to this article here.

Excel vba font

Introduction to Fonts

A font is an art effect made of designed symbols used to represent letters and other characters on a cell or a group of cells. A font is characterized by various characteristics such as a name, a style and a size.

Microsoft Excel uses some default fonts to display the names of columns and rows on a worksheet. Another font is used by default to show the contents of cells. If you want to use a different font to display text in cells, you can changes these defaults. When changing the default font, in other words if you decide to change the default font, you should use only the most popular fonts that are more likely to be found on your user’s computers.

To assist you with setting the fonts of columns, the Ribbon is equipped with a Font section in the Home tab:

The Name of a Font

You can make a worksheet user-friendly by applying some fonts and colors to their content. A font is primarily known by its name. When starting a worksheet, Microsoft Office Excel 2007 applies a default font named Calibri to the cells. If you want, you can change it.

To visually change the font used by a cell or a group of cells:

  • Click the cell to activate it or select a group of cells. On the Ribbon, click Home. In the Font section:
    • Click the arrow of the Font combo box and select the desired font
    • Click the more options button . This would open the Format Cells dialog box with the Font tab activated. In the Font property page, use the Font combo box to select the desired font
  • Right-click a cell and click Format Cells. Select a group of cells then right-click the selection and click Format Cells. In the Format Cells dialog box, click the Font tab. In the Font property page, use the Font combo box to select the desired font

To programmatically specify the name of a font, refer to the cell or the group of cells on which you want to apply the font, access its Font object, followed by its Name property. Then assign the name of the font to the cell or group of cells.

Practical Learning: Selecting a Font

  1. Change the code as follows (if you do not have the Rockwell Condensed font, use Times New Roman):

Return to Microsoft Excel and press Ctrl + Shift + W to see the result

  • Return to Microsoft Visual Basic
  • The Size of a Font

    Besides its name, a font is also known for its size. The size defines how much height and proportional width would be used to represent the characters of the selected font.

    To visually specify the font size used by a cell or a group of cells:

    • Click the cell to activate it or select a group of cells. On the Ribbon, click Home. In the Font section:
      • Click the arrow of the Font Size combo box and select the desired size
      • Click the more options button . In the Font property page of the Format Cells dialog box, use the Size combo box to select the desired size
    • Right-click a cell and click Format Cells. Select a group of cells then right-click the selection and click Format Cells. In the Format Cells dialog box, click the Font tab. In the Font property page, use the Size combo box to select the desired font
    Читать еще:  Как узнать среднее значение в excel

    When a font is installed, a set of font sizes is created in the Font Size combo box. You can use those numbers but you can also set a new one. To do this, instead of selecting a value in the Font Size combo box, type the desired number and press Enter or Tab. The operating system would calculate the size and apply it.

    To programmatically specify the font size of a cell or a group of cells, refer to that cell or the group of cells, access its Font object, followed by its Size property, and assign the desired value to it.

    Practical Learning: Setting the Font Size of a Cell

    1. Change the code as follows:

    Return to Microsoft Excel and press Ctrl + Shift + W to see the result

  • Return to Microsoft Visual Basic
  • The Style of a Font

    Another aspect of the appearance of a font is its style, which is a technique of drawing the characters of the text, depending on the font. This characteristic comes in four options:

    To visually change the font style of a cell or a group of cells:

    • Click the cell or select a group of cells on the worksheet. In the Home tab of the Ribbon, in the Font section,
    • Right-click a cell and click
    • Click the cell to activate it or select a group of cells. On the Ribbon, click Home. In the Font section:
      • Click the button that represents the desired style: Bold , Italic , or Underline
      • Click the more options button . In the Font property page of the Format Cells dialog box, select the desired option in the Font Style combo box
    • Right-click a cell and click Format Cells. Select a group of cells then right-click the selection and click Format Cells. In the Format Cells dialog box, click the Font tab. In the Font property page, select the desired style in the Font Style combo box

    You can specify more than one style on a cell or a group of cells. To do this, click the button of the desired style. When a style is valid for a control, when you click that control, the style button is highlighted: , , or . To remove a style, click the undesired button. To add a style to another style, simply click the desired button. Based on this, you can have one, two or three buttons highlighted in the combination of your choice.

    To support font styles, the Font object is equipped with various Boolean properties that are Bold, Italic, Underline, and Strikethrough. Therefore, to grammatically specify the font style of a cell or a group of cells, access the cell or the group of cells, access its Font object, followed by the desired style, and assign the desired Boolean value.

    Practical Learning: Formatting With Styles

    VBA Excel. Цвет текста в ячейке

    Изменение цвета текста (шрифта) в ячейке рабочего листа Excel с помощью кода VBA. Свойства .Font.Color, .Font.ColorIndex и .Font.TintAndShade

    Использование цветовой палитры для присвоения цвета тексту в ячейке листа Excel аналогично присвоению цвета фону ячейки, только свойство диапазона .Interior меняем на свойство .Font.

    Цвет текста и предопределенные константы

    Цвет шрифту в ячейке можно присвоить с помощью предопределенных констант:

    Напомню, что вместо индексов строк и столбцов можно использовать переменные. Список предопределенных констант смотрите здесь.

    Цвет шрифта и модель RGB

    Для изменения цвета текста в ячейке можно использовать цветовую модель RGB:

    Аргументы функции RGB могут принимать значения от 0 до 255. Если все аргументы равны 0, цвет — черный, если все аргументы равны 255, цвет — белый. Функция RGB преобразует числовые значения основных цветов (красного, зеленого и синего) в индекс основной палитры.

    Свойство .Font.ColorIndex

    Свойство .Font.ColorIndex может принимать значения от 1 до 56. Это стандартная ограниченная палитра, которая существовала до Excel 2007 и используется до сих пор. Посмотрите примеры:

    Таблица соответствия значений ограниченной палитры цвету:

    Открывается в новом окне Стандартная палитра Excel из 56 цветов

    Основная палитра

    Основная палитра, начиная c Excel 2007, состоит из 16777216 цветов. Свойство .Font.Color может принимать значения от 0 до 16777215, причем 0 соответствует черному цвету, а 16777215 — белому.

    Отрицательные значения свойства .Font.Color

    При записи в Excel макрорекордером макроса с присвоением шрифту цвета используются отрицательные значения свойства .Font.Color, которые могут быть в пределах от -16777215 до -1. Отрицательные значения соответствуют по цвету положительному значению, равному сумме наибольшего индекса основной палитры и данного отрицательного значения. Например, отрицательное значение -8257985 соответствует положительному значению 8519230, являющегося результатом выражения 16777215 + (-8257985). Цвета текста двух ячеек из следующего кода будут одинаковы:

    Свойство .Font.TintAndShade

    Еще при записи макроса с присвоением шрифту цвета макрорекордером добавляется свойство .Font.TintAndShade, которое осветляет или затемняет цвет и принимает следующие значения:

    • -1 — затемненный;
    • — нейтральный;
    • 1 — осветленный.

    При тестировании этого свойства в Excel 2016, сравнивая затемненные и осветленные цвета, разницы не заметил. Сравните сами:

    При тестировании в первых трех ячейках первого столбца должен быть одинаковый текст для удобства сравнения оттенков.

    VBA Tips & Tricks

    Welcome to VBA Tips & Tricks. All VBA related information will be posted on this blog. Of late, VBA has been disregarded by many software professionals for .Net, c# and other technologies. This blog will also post articles related to them too Happy reading

    Читать еще:  Excel 2020 разработчик

    Pages

    Saturday, September 15, 2007

    Excel VBA — Change Font Color for Part of Text

    Formatting Text (Cell Contents) Partially using Excel VBA

    Sometimes, you need to differentiate some parts of text in an Excel cell. This can be done using formatting those characters.

    Here is the way that can be done using VBA using the Characters property

    Sub Color_Part_of_Cell()
    ‘Print ActiveCell.Characters.Count
    With ActiveCell.Characters(2).Font
    .Color = RGB(36, 182, 36)
    End With

    With ActiveCell.Characters(2, 2).Font
    .Color = RGB(36, 182, 36)
    End With

    With ActiveCell.Characters(, 2).Font
    .Color = RGB(36, 182, 36)
    End With

    The output of the above is

    expression Required. An expression that returns an object in the Applies To list.

    Start Optional Variant. The first character to be returned. If this argument is either 1 or omitted, this property returns a range of characters starting with the first character.

    Length Optional Variant. The number of characters to be returned. If this argument is omitted, this property returns the remainder of the string (everything after the Start character)

    The same characters object can be used to make a character Bold / Italic

    11 comments:

    Hi
    can you extract a text string from the excel cell, which has a different color? The text in the cells is in black and blue, but could not figure out how to make extraction of the blue or black work.
    Thank you.

    You can try something similar to the following to extract colored text to a separate string

    For i1 = 1 To ActiveCell.Characters.Count
    If (ActiveCell.Characters(i1, 1).Font.Color <> vbAutomatic) Then
    sColoredText = sColoredText & ActiveCell.Characters(i1, 1).Text
    End If
    Next i1
    End Sub

    Hello,
    Can this be modified to show a trigger instead of a certain character location. For example, if the name John is in the cell, then everything from John to the end of the string be another font color.

    So glad I found this. You initial code and response post were exactly what I was looking for. Thanks!

    This will not work if the cell has a custom number format. Change to a non-custom format (eg General) and it works as expected.
    Paul Wright
    www.excelexpert.com

    Hello Shasur. This is what I need. The report template I’m working on has built in macros. I don’t know how exactly to implement the codes you wrote. For example, how can I modify your code to point to a specific cell in my report? Also, when I insert your codes as part of a subset of an existing Sub and ran the macro, it tells me that it was expecting an End Sub. I’m clueless as to what to do since I’m very green at Excel VBA. Any clues you could provide would be greatly appreciated.

    Ok, but How can I do to write in a cell with different colors ?

    In a project that I am presently working on, I have multiple text values in 1 cell and depending on a pre defined condition, I need to colour code those different values — but all in 1 cell. I understand the syntax: With Cells(1, 2).Characters(Start:=ii, Length:=Len(ss)).Font .Color = -11489280

    but the problem that I am facing is that regardless of what the condition instructs, once the next value enters the same cell with a different colour, the previous value changes it’s colour to the same as the one just entered. Thus all the values have the same colour, except maybe the last value.

    Please advise. Thanks.

    You’ll need to create «event handlers.» You want to save the existing color scheme BEFORE the user types something new in the cell, and then re-apply the color scheme after he hits enter.

    Suppose the cell you are interested in is Sheet2!E25. Open up visual basic, and on the top left side of the window you will see a list of all open workbooks. Each is named «VBAProject (SRMDataEntry)» where SRMDataEntry is the name of your workbook. If there is a plus sign next to your workbook, click on it to expand it. You will see a list of all the worksheets and all the modules in the SRMDataEntry workbook.

    Find the spreadsheet that has the cell you want to keep colored in a certain way. Right click on that worksheet & select «view code.» This is where you put event handler code.

    Try playing around with two particular events — worksheet_change and worksheet_selectionchange, like this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    msgbox(«You selected a new cell with your mouse or keyboard — and the cell you selected is » & target.address )
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    msgbox(«You entered new data in cell » & target.address )
    End Sub

    once you get the hang of how events work, the solution to your problem starts to become clear (or at least clearer). Instead of popping up msgboxes, you automatically reapply your preferred formatting whenever the contents change in your special input cell. Here’s an example:

    Private Sub Worksheet_Change(ByVal Target As Range)
    if target.address = «$b$27» then
    target.Characters(1,5).font.color = rgb(255,0,0)
    ‘sets 5 characters starting with first character to red
    ‘i.e. sets characters 1-5 to red
    target.Characters(6,5).font.color = rgb(0,255,0)
    ‘sets 5 characters starting with 6th character to green
    ‘i.e. sets characters 6-10 to green
    target.Characters(11).font.color = rgb(0,255,0)
    ‘sets ALL remaining characters starting with 11th character to blue
    ‘i.e. sets characters 11, 12, 13 . to blue
    End if
    End Sub

    The only downside of the above is that it will crash if the user enters less than 11 characters. Better would be to create a hidden spreadsheet named «Secret», and put your «color template» in cell A1 in Secret. Then you can do as follows:

    Читать еще:  Colorindex в excel

    Private Sub Worksheet_Change(ByVal Target As Range)
    if target.address = «$b$27» then
    for i = 1 to min(target.characters.count, _
    range(«Secret!A1»).characters.count)
    target.Characters(i).font.color = _
    range(«Secret!A1»).characters(i,1).font.color
    next i
    End if
    End Sub
    If the entry in B27 is longer than the entry in Secret!A1, then the last iteration of the loop will set target.characters(n).font.color to the color of the last character in Secret!A1. target.characters(n).font.color sets the color of ALL characters starting at n.

    If you wanted to use the contents of E27 as your color scheme, rather than having a predetermined color scheme, you would use the Worksheet_SelectionChange event to tip off the program that someone has clicked on E27 — BEFORE he makes any changes. Like this:

    Thread: Excel/VBA — change font color of object?

    Thread Tools
    Display
    • Linear Mode
    • Switch to Hybrid Mode
    • Switch to Threaded Mode

    Excel/VBA — change font color of object?

    I have a button on a worksheet which runs some VBA code. Most of it works fine but when it comes to a section that is supposed to change the line, fill and font color of an AutoShape ( a rectangle) containing some text, I have a problem. I can change the line color and fill color okay with the following code:

    However, when I programmed in this code for the font color.

    . I get the error message «object doesn’t support this property or method».

    I take this to mean that you cannot change the font color, but this must be wrong! How can I change the font color if the object doesn’t have the .Font.ColorIndex property?

    If I actually select the object on the worksheet then I can record a macro that will change the color. This is what I get:

    But I don’t want to have to select the object in order to change the font color.

    *Edit* I should also mention that the code is within a worksheet code module, not a standard code module.

    Re: Excel/VBA — change font color of object?

    Never Think Impossible

    If you find my answer helpful then please add to my reputation

    Re: Excel/VBA — change font color of object?

    After a quick look into this there is no Font Object on the Shapes collection, you are going to habe to select it first as this is the only way you can change the font color..

    At Least thats all I’ve found so far..

    Never Think Impossible

    If you find my answer helpful then please add to my reputation

    Re: Excel/VBA — change font color of object?

    That appears to be the case but if so it’s a bit stupid!

    The object obviously has some kind of font colour property (how else would it know what font colour to display otherwise?) but I don’t understand why there is no direct way to refer to it!

    Re: Excel/VBA — change font color of object?

    Found this with a little research. You don’t have to select the shape to change the font.

    I don’t know why you would have to go through this route instead of what you were trying (because that seems like the LOGICAL way of performing this), but it works.

    BAM!

    If you find any of my posts of good help, please rate it

    Re: Excel/VBA — change font color of object?

    VB/Office Guru™ (AKA: Gangsta Yoda™ ® )
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24″ LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

    Re: Excel/VBA — change font color of object?

    And here I was looking at TextEffect.

    You don’t have to use the ColorIndex with the font by the way you can specify the RGB values..

    Here’s another way.. refering to the Font Object

    Never Think Impossible

    If you find my answer helpful then please add to my reputation

    Re: Excel/VBA — change font color of object?

    Thanks for the help guys.

    By the way, when you write the code as:

    Re: Excel/VBA — change font color of object?

    Thanks for the help guys.

    By the way, when you write the code as:

    Well. declaring variables will make VBA run faster as it doesn’t have to do continuous checks to what variables should be dimensioned as.

    But the difference is probably minimal. I would guess. and I said ‘Guess’

    I would declare that object only if I was going to perform more changes to it. If that’s all you’re going to do with that object (is change the font color) then I’d say don’t worry about setting the variable.

    . But that’s just my opinion.

    If you find any of my posts of good help, please rate it

    Ссылка на основную публикацию
    Adblock
    detector