Formatting Combo Box
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Formatting Combo Box
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.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Combo Box
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.
You can set the font size of ActiveX combo boxes.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Combo Box
To suppress the dropdown arrows, you'd have to use Data Validation with the List option.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Formatting Combo Box
Just a (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.
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Formatting Combo Box
Awesome, Data Validation with the List option is the way to go!!
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Formatting Combo Box
In Excel, how do you turn a list, which is a range, into a table?
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Combo Box
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.
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Formatting Combo Box
Here is a sample workbook...
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Formatting Combo Box
Thank you both
My version of Excel(2010) doesn't have the Developer and Design tabs, what am I missing?
My version of Excel(2010) doesn't have the Developer and Design tabs, what am I missing?
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Combo Box
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.
- 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
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Formatting Combo Box
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.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Combo Box
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
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Formatting Combo Box
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
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Combo Box
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
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Formatting Combo Box
Important point. Thank you Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Formatting Combo Box
How come if you put a dot after ActiveSheet in the Vba window, intellisense doesn't come up listing the relevant properties/methods?
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Combo Box
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.
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
Hans