skip to main content

Macros in Excel

15 years ago

Anyone really good at Macros out there? Or not really good like me and wanting to learn more? I'm formatting tab colors and doing clickboxes and would really like someone with whom to bounce ideas around.

Sort by:Oldest

Comments (3)

abreeze
15 years ago
I did some googling and found this tutorial which may help with formatting tab colors:

http://www.taltech.com/support/sw_tricks/exmacros.htm

The link below is for check boxes. Perhaps there is something there to help you with clickboxes.

Here is a link that might be useful: check boxes
silversword
Original Author
15 years ago
Thank you, I appreciate the link. I found a pretty good site that explained what I needed.

Here's a pretty good explanation for clickboxes:

In Column A row 1, type in True or False.
Add the checkbox:
Go to Developer- Insert - Form Control and choose a clickbox
Move the clickbox into the proper cell and name it (or not)
Connect the clickbox to the True/False statement:
1. Right click on the clickbox and choose Format Control - Control
2. Choose the Value (Checked, for this example)
3. Go into the Cell Link bar and click once, then go to the workbook and select the cells A1:Whichever one ends just before your clickbox, ie A1:C1 which will come up automatically in the box as $A$1:$C$1.

To add color:
1. Select the whole row you want to change. Include the clickbox. Right click on the row you just selected, but not over the clickbox.
2. Go to Home tab - Conditional Formatting - New Rule - Use a Formula to determine which cells to format
3. Click in the rule description box and then select the cell with TRUE in it. The formula that will appear is =$A$1
4. Change that to =$A1=TRUE()
a. If you only want to format one row you may leave the formula as =$A$1=TRUE(). The $ between the numbers and letters means "only", as in A1 ONLY. If you remove the second $, it will be available to apply to as many rows as you like.
5. Click the format button and choose a background color, and click ok.
6. You will be taken back to your spreadsheet.

1. NEXT, to apply your rule to your whole worksheet, select the same row in which you just applied the rule.
2. Go back to Conditional Formatting and select Manage Rules.
3. Click in the Applies To box, and select the whole range you wish to apply the formatting to and click ok.
4. This applies the formatting you just applied to the first row to all of the rows.

And to change tab color based on information in a cell:

Right click on Tab
Click Unprotect Sheet
Right click on Tab
View Code
Type in the following:
Private Sub Worksheet_Calculate()
If Range("A3").Value > 0 Then
Range("A3").Parent.Tab.ColorIndex = 4
Else
Range("A3").Parent.Tab.ColorIndex = 3
End If
End Sub
Change the range ("A3") to the cell to which you wish to link the macro.
Change the color index if desired. The first color index is the color it will turn if FALSE, or more than 0. The second color index is the color it will change to if TRUE, or 0

Click RUN
Go to worksheet and make sure it works.

For Macro, make sure OPTIONS box is clicked. It will come up by the taskbar when the file first opens. Save Macros to Personal Folder. Make sure Personal folder is "Unhide" in the view tab.
Make sure file is saved as Macro-Enabled File
Related Discussions
if anyone is intersted -excellent clematis sale
Q
Comments (51)
Talk to him, I mean, write to him, Brandy - it would be good to know how long he can ship; they are in a very cold zone. They already had some very cold temps - I asked about Perrin'e pride and Jerome said it was hit by frost but still healthy. I must have that one, I MUST have that one. Dont' you dare to snatch it from me. LOL. Snow Queen is white with blue edges; it has been holding on to that bloom for days. Today Henryi also had a bloom opened but I couldn't find my camera before dark; typical of me. Ascotiensis also arrived with a fat bud - I heard so many good things about that clematis but all the photos I checked are different, so if it opens it will help me to find the best spot for that beauty.
...See More
More macros
Q
Comments (13)
Yes, it's the one I posted a few weeks ago. I'm still not 100% convinced it is T. calcarea because the leaves are very compact and quite small, but the plant has been grown hard this year.
...See More
Macro question in Excel 2007
Q
Comments (1)
You should be able to find the answer at the link below. Here is a link that might be useful: Macros
...See More
users of office 2003
Q
Comments (12)
I KNOW IT'S NOT WINDOWS SP3, ITS OFFICE 2003; BUT DID YOU SEE THIS LIST OF KNOWN ISSUES: Known issues after you install the service pack After you install Office 2003 SP3, you experience problems when you use a Bit field in an Access project (.adp). For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 943967 (http://support.microsoft.com/kb/943967/) After you install Office 2003 SP 3, you encounter various problems in an Access project when you work with a Bit data type column in a linked SQL Server table After you install a Microsoft Office service pack, you are prompted unexpectedly to restart the computer. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 905726 (http://support.microsoft.com/kb/905726/) You are unexpectedly prompted to restart the computer after you install an Office service pack or an Office update After you install Office 2003 SP3, an add-in, ActiveX control, or COM add-in may not work as expected in an Office 2003 program. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 938814 (http://support.microsoft.com/kb/938814/) The functionality of an add-in, an ActiveX control, or a COM add-in is reduced, or the functionality is blocked after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, you may receive an error message when you try to create a new MAPI form in Outlook 2003. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 938816 (http://support.microsoft.com/kb/938816/) Error message when you try to create a new MAPI form in Outlook 2003: "contact the administrator" After you install Office 2003 SP3, attachments that contain the .gadget extension cannot be opened in Outlook 2003. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 938811 (http://support.microsoft.com/kb/938811/) Attachments that contain the .gadget extension cannot be opened in Outlook 2003 after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, the Fast Saves feature does not work as expected in Word 2003. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 938808 (http://support.microsoft.com/kb/938808/) The Fast Saves feature in Word 2003 no longer functions after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, the Microsoft Office Document Imaging program may not work as expected. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 938813 (http://support.microsoft.com/kb/938813/) You may experience issues when you run the Microsoft Office Document Imaging program after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, you may receive an error message when you try to open or to save a file. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 941636 (http://support.microsoft.com/kb/941636/) You receive an error message when you try to open a file or to save a file after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, you cannot open some Microsoft Excel workbooks that contain Microsoft Visual Basic for Applications (VBA) macros. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 938806 (http://support.microsoft.com/kb/938806/) You cannot open Microsoft Excel 5.0/95 Workbook files that contain Visual Basic for Applications macros in Excel 2003 after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, the version number of Office is no longer saved in the properties of the document as expected. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 938807 (http://support.microsoft.com/kb/938807/) Office files that are saved in certain formats no longer contain the version number of Office after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, you experience problems when you add a combo box control or a list box control to a form or to a report. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 945280 (http://support.microsoft.com/kb/945280/) Combo box controls and list box controls display no value or incorrect values in Access 2003 after you install Office 2003 Service Pack 3 After you install Office 2003 SP3, you experience other problems in Access. For more information about this issue, click the following article number to view the article in the Microsoft Knowledge Base: 945674 (http://support.microsoft.com/kb/945674/) Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007 After you install Office SP3 for certain localized versions of Office 2003, the application installation states may become corrupted. This issue may cause malfunctions in Office 2003 applications. To resolve this issue, apply the following update on a system directly after you install Office SP3: 943452 (http://support.microsoft.com/kb/943452/) Critical Update for Office 2003
...See More
abreeze
15 years ago
I'm glad you found what you needed! :)

Related Stories

KITCHEN DESIGN Get a Grip on Kitchen Cabinets With the Right Knobs and Pulls

Here's how to pair the right style, type and finish of cabinet hardware with your kitchen style

GARDENING GUIDES How to Keep Your Citrus Trees Well Fed and Healthy

By Noelle Johnson Landscape Consulting

Ripe for some citrus fertilizer know-how? This mini guide will help your lemon, orange and grapefruit trees flourish

INSIDE HOUZZ Houzz Prizewinners Take Their Kitchen From ‘Atrocious’ to ‘Wow’

By Mitchell Parker

A North Carolina family gets the kitchen they always wanted — and not a minute too soon — courtesy of the Houzz sweepstakes

GARDENING GUIDES Common Myths That May Be Hurting Your Garden

By J. Peterson Garden Design

Discover the truth about fertilizer, soil, staking and more to keep your plants healthy and happy

LANDSCAPE DESIGN Native Plants Help You Find Your Garden Style

By Oakley Gardens

Imagine the garden of your dreams designed with plants indigenous to your region

GARDENING GUIDES Gardening Solutions for Heavy Clay Soils

By Falon Mihalic

What’s a gardener to do with soil that’s easily compacted and has poor drainage? Find out here

REMODELING GUIDES Should You Remodel or Just Move?

By Bud Dietrich, AIA

If you're waffling whether 'tis better to work with what you've got or start fresh somewhere else, this architect's insight can help

KITCHEN OF THE WEEK Kitchen of the Week: A Designer's Budget Wood-and-White Makeover

By Karen Egly-Thompson

Low-cost materials used in a design-minded way turn a depressing space into a light-filled showstopper

DECORATING GUIDES If You’re Looking for a Little Design Inspiration, Think Big

By Toronto Interior Design Group

Oversize lights, prints, kitchen islands and more show that larger-than-life decor is taking off

SELLING YOUR HOUSE 10 Tricks to Help Your Bathroom Sell Your House

By Mink Home Staging

As with the kitchen, the bathroom is always a high priority for home buyers. Here’s how to showcase your bathroom so it looks its best

Sponsored

HomeCraft Remodeling

5 Reviews

Custom Craftsmanship & Build Solutions in Franklin County, Ohio

More Discussions

Tired of MS Office 365 Monthly Fees?

1