PDA

View Full Version : VBA for designing your sheet



kiisaaa
02-08-2018, 02:29 AM
Hi,

Is there any possibilities to create a macro to auto format and color your tables?

Example: First row always dark blue with bold, white text
The next rows are shown in millions/thousands/whateverformat
Last cell (a sum cell) should have Top and Bottom border with grey color.


21574

The idea is to customize this whenever I am on a new project and have a shortcut placed in the toolbar of Excel.
It should not be too difficult to code I believe :) ?

Paul_Hossler
02-08-2018, 10:36 AM
I've found that the best way usually is to learn is to record a macro and then to generalize and cleanup the recorded code

If you record a macro and post it here, I'm sure people will help

SamT
02-08-2018, 01:09 PM
One "Macro" could handle the top and bottom row, and, format the body into one particular format.

If I had to create lots of tables, I might write several subs that could format the Column Headers in several different ways. the Table Bodies in several different ways, then create a User Form to select from all the different ways to format Headers and Bodies, and to insert the Sum formulas in the bottom Row.

In that suggestion, I would structure the code with one Macro to call the UserForm and several Standard Module Subs to do the actual Formatting as directed by the User selections on the UserForm.

By keeping the Formatting Subs separate and distinct, it makes it easier to develop, maintain, troubleshoot, and edit the code.

To start your Project


Create a blank UserForm
Record a Macro, with Shortcuts, to Show the UserForm
Add ListBox Controls to the Form to choose the Font Name, Color, Bold. Add only one selection to each Control.
Record a Macro to Format any Top Row of any Table to set the Font Name to the same one you used in the Form
Record a Macro to set the Font Bold to the one used
Repeat with Color and any other attributes you have on the form
Clean up all the Macros and make them Sub-Procedures that accept the appropriate Parameter.
Write a Sub that Calls each "Macro" in turn and passes that macro's needed Parameters
Add a Command Button to your Form
Write code for the CBut to call the "TopRow" Sub and pass all the Parameters from the Controls to the "TopRow" sub.
Test this bare, one use UserForm.


Repeat for any other possible top row formatting

Repeat all the above for all possible Body formatting

Repeat all the above for any possible bottom Row formatting and Formulas.

Record Macros that perform any and all possible formatting and incorporate those cleaned up Macros and ideas into your UserForm and Subs.

Add Ignore options to Top Row, thus giving you the chance to use this same system to add Sums Row Formulas after SubTotal rows.

Always use Worksheet.Selection when a sub is working on the sheet. ie; Selection.Rows(1), Selection. Rows(2) to Selection.Rows.Count-1, and Selection. Rows(Selection.Rows.Count)