Simple checkbox functions

[JianBean]

I have a worksheet that consists of 32 sheets, one for each day of the month and one for totals. Each of these sheets has 70 lines full of data, each line also has two checkboxes, these checkboxes are supposed to change the color of the line, according to which checkbox is selected.

What I'm trying to avoid is having the same function copied to every checkbox function (140 checkboxes per sheet across 31 sheets at say 4 lines of code a piece! That's 17360 lines of code that frankly, I don't want to...even copy and paste! Not withstanding the fact that the file would be about 100MB when I was done!)

I know that I can have all of the checkboxes call one function, but I don't know how to tell which checkbox was changed, to know which line to change the colors on.

Any ideas? Thanks in advance!

-Bean

 

[Zathras]

You would be far ahead of the game if you forget about check boxes in this case.

I would simply insert a column so that I could enter 0, 1 or 2 (or some other code) and then use Conditional Formatting on the entire sheet to set the colors according to the code.

However (before Skip points it out ), you might want to re-think the entire idea of using 32 sheets with only 70 lines per sheet. You would find it better in the long run to have one sheet with 2240 rows and then use a pivot table or auto data filters to handle/view the data you want.

 

[SkipVought]

Bean,

1) This can be done using VBA with a minumum of 2 checkboxes per sheet. Let me know if you would like to pursue this option.

2) You have chopped up your data into different sheets which is a REALLY poor design. It is typical of spreadsheet users who view their data in terms of a REPORT (what you see) rather than as a database that can be used as the source of many different reports.

More than likely, your data ought to be on ONE SHEET with at least one additional column for DATE. From such a data source, you would be able to prpduce a myriad of reports using many of the built-in functionality of Excel, including, Sort, Filter, Subtotal, PivotTable, Query.

I'm done! And I feel better ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

Z,

You MINDREADER! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[JianBean]

Well, I really don't know how to use PivotTables, but I must point out, that I did not create this spreadsheet, my friend was the one who designed it, I was just given the task of modifying it! But I am willing to change it in anyway, if it is better, and does serve it's purpose!

I wouldn't mind pursuing the 2 checkboxes idea, I'm open to anything! But I also wouldn't mind learning how I might use just a single sheet.

The spreadsheet is used to track daily calling logs. Each day must be displayed/printed separately (...without too much effort, some of my co-workers aren't the brightest bulbs of them all!) But the entire month's worth of calls has to be stored in one file.

The checkboxes are to differentiate what went on during the call/what type of call it was.

For example, if the caller talked to the person, left a message with another person, or left a voice message.

I'm open for suggestions if you have any ideas of ways that I could do it, or any places that I could go for ways to do it I would greatly appreciate it!

-Bean
Thanks again Zathras/Skip!

 

[SkipVought]

Bean,

1) PivotTables - You can read help, but DOING goes a long way toward learning.

Create a new sheet and just copy the data from the FIRST day sheet to the new sheet under these conditions...

A. Heading Row is Row 1 - No empty heading columns
B. Data Starts in Row 2 - No empty data rows
c. Add a Heading for Date and enter the full date 6/24/2004, or whatever in this column. This can be made to happen automatically if the date that the data is entered is the date you always want in the column
D. Copy DATA from the SECOND Day sheet and paste in the first empty cell in Column A on the new sheet.

Here's how you can see ONE DAY's WORTH of data. With only one cell selected in the table, Data/Filter/AutoFilter - and select a Date from the DropDown.

Post some sample data and I can tell you if you will be able to do anything with PivotTable.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244