Formatting Combo Box

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Formatting Combo Box

Post by grovelli »

Is there a way to format the two combo boxes in the attached xls file so that the characters have larger fonts, say Arial 14? And would it also be possible to display the selection in the combo boxes but without the drop-down arrow when printing?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Combo Box

Post by HansV »

There is no way to change the font size of a Forms combo box; the only way to enlarge the font is to increase the zoom percentage (you'd probably want to decrease the font size in the cells if you do).

You can set the font size of ActiveX combo boxes.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Combo Box

Post by HansV »

To suppress the dropdown arrows, you'd have to use Data Validation with the List option.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Formatting Combo Box

Post by Rudi »

Just a :2cents: (for Validation Lists)

If the source of your validation dropdown is a list, convert it into a Table (which makes it a dynamic range)
Then in the validation rule, choose List
In the Source textbox type: =INDIRECT("Table1[FieldName]")

This will ensure that your dropdown is always up-to-date with any entries added/removed from the source table.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Formatting Combo Box

Post by grovelli »

Awesome, Data Validation with the List option is the way to go!! :clapping: :fanfare: :thankyou:

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Formatting Combo Box

Post by grovelli »

In Excel, how do you turn a list, which is a range, into a table?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Combo Box

Post by HansV »

Select the list.
On the Insert tab of the ribbon, click Table.
If necessary, tick or clear the check box "My table has headers" to match your setup.
Click OK.
Select the table without the header row.
You'll see the name of the table in the Name box on the left hand side of the formula bar. You can change the name here if you wish.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Formatting Combo Box

Post by Rudi »

Here is a sample workbook...
1.jpg
2.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Formatting Combo Box

Post by grovelli »

Thank you both :joy:
My version of Excel(2010) doesn't have the Developer and Design tabs, what am I missing?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Combo Box

Post by HansV »

You must turn on the Developer tab yourself:
- Select File > Options.
- Click 'Customize Ribbon' in the navigation pane on the left.
- In the list of Main Tabs on the right, tick the check box for Developer.
- Click OK.

The Design tab of the ribbon is a so-called contextual tab. Excel will display it automatically when appropriate, for example when you click inside a table. Otherwise, this tab will remain hidden.
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Formatting Combo Box

Post by grovelli »

Thank you Hans, I've prepared a button on the attached xls file that is supposed to copy a tab and colour it but rather than hard-coding the name of the tab to be copied, what's the vba for specifying the currently selected tab instead?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Combo Box

Post by HansV »

There is no need to select sheets - the code works more efficiently if you don't. The active sheet is ActiveSheet in VBA. So:

Code: Select all

Sub Macro1()
'
' Macro1 Macro
' Copy Tab and Colour it
'

'
    ActiveSheet.Copy After:=Sheets(1)
    With Sheets(2).Tab
        .Color = vbRed
        .TintAndShade = 0
    End With
End Sub
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Formatting Combo Box

Post by grovelli »

Thank you Hans, I've modified it as follows to make it so the new tab is copied after the currently selected one and not after the first one.

Code: Select all

Sub Macro1()
'
' Macro1 Macro
' Copy Tab and Colour it

    ActiveSheet.Copy After:=ActiveSheet
    With Sheets(2).Tab
        .Color = vbRed
        .TintAndShade = 0
    End With
End Sub

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Combo Box

Post by HansV »

Since the copy won't necessarily be the second sheet any more, you should change the macro to

Code: Select all

Sub Macro1()
'
' Macro1 Macro
' Copy Tab and Colour it

    ActiveSheet.Copy After:=ActiveSheet
    With ActiveSheet.Tab
        .Color = vbRed
        .TintAndShade = 0
    End With
End Sub
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Formatting Combo Box

Post by grovelli »

Important point. Thank you Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Formatting Combo Box

Post by grovelli »

How come if you put a dot after ActiveSheet in the Vba window, intellisense doesn't come up listing the relevant properties/methods?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Combo Box

Post by HansV »

ActiveSheet is of the generic Object type, since it could represent either a worksheet or a chart sheet, which have different properties and methods. VBA cannot predict what type of sheet ActiveSheet will be when you run the code, so IntelliSense doesn't work. If you know for certain that you'll be working with a worksheet, you can use

Dim wsh As Worksheet
Set wsh = ActiveSheet

If you type wsh. IntelliSense will pop up a list of properties and methods.

Similarly, IntelliSense doesn't work for the Selection object: it could be a range, or a shape, or a chart object, or an element of a chart, etc.
If you're sure the current selection is a range, you can use

Dim rng As Range
Set rng = Selection

and type rng. in your code to get a list of properties and methods.
Best wishes,
Hans