|
|
|
|
|
|
|
|
|
Excel
|
Create new style from formatted cell
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000
|
|
Submitted by:
|
johnske
|
|
Description:
|
After running the code the new style will be added to your styles collection
|
|
Discussion:
|
You may have gone to great lengths to format a cell. To add this format 'manually' to your styles collection you would normally have to re-specify all the format conditions to add them to the collection. This provides a quick and easy way to add styles.
|
|
Code:
|
instructions for use
|
Option Explicit
Sub AddaStyle()
Dim NewStyle$
NewStyle = InputBox("What name do you want for this style?" & vbLf & _
"" & vbLf & _
"NOTE: The style will be based entirely on the format" & vbLf & _
"of a cell that you have selected. If the cell currently" & vbLf & _
"selected is not the one you want to use - then Click" & vbLf & _
"Cancel and select the correct cell...", "New Style - Style Name")
If NewStyle = Empty Then Exit Sub
ActiveWorkbook.Styles.Add Name:=NewStyle, BasedOn:=ActiveCell
End Sub
|
|
How to use:
|
- Open an Excel workbook
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Insert/Module
- Copy and paste the code into the Module
- Now select File/Close and Return To Microsoft Excel
- Save your work
|
|
Test the code:
|
- Format a cell in whatever way you want
- Select the cell
- Select Tools/Macro/Macros.../AddaStyle/Run
- Now select a range of other cells
- Go to Format/Styles..., select the style you've added then click OK
- The selected cells are now formatted in this style
- Note: If you have a very large style collection that you want to clean out then go to http://www.vbaexpress.com/kb/getarticle.php?kb_id=367 for a procedure that does this
|
|
Sample File:
|
AddStyles.zip 10.49KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 102 times.
|
|
|