<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Tick Cell Upon Selection

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Back to: Excel VBA. Got any Excel/VBA Questions? Free Excel Help

It is not too uncommon for an Excel Workbook to be used to collect data for such things as surveys etc. Perhaps the most common way this is done, is to offer the user x amount of answer choices and place a Checkbox along-side each choice. The user then checks the appropriate checkboxes. The problem with using this method is that your Workbook can soon end up with hundreds of checkboxes. This can not only make a Workbook unstable but can also greatly increase file size!

Let's look at just how easily we can use some very simple VBA code to have any cell, within a specified range, ticked upon selecting it. If the cell within the specified range, already has a tick, the code will remove it. The trick to the code is the use of the letter "a" in a cell that has had it's font formatted to marlett. When the time comes to add up the results, it is simply a matter of using the COUNTIF Function to count the occurrences of the letter "a". For example

=COUNTIF($A$1:A$100,"a")

The code examples below work only on range A1:A100, but can be easily modified to suit any range. To use this code, activate the Worksheet the ticks should be displayed on, right click on the sheet name tab and select "View Code". Now paste in either CODE 1 (to have the cell ticked by selecting it) or CODE 2 (to have the cell ticked by double clicking it)

'CODE 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    If Target.Cells.Count > 1 Then Exit Sub        If Not Intersect(Target, Range("A1:A100")) Is Nothing Then            Target.Font.Name = "Marlett"                If Target = vbNullString Then                    Target = "a"                Else                    Target = vbNullString                End If        End IfEnd Sub

'CODE 2

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then            Cancel = True 'Prevent going into Edit Mode            Target.Font.Name = "Marlett"                If Target = vbNullString Then                    Target = "a"                Else                    Target = vbNullString                End If    End IfEnd Sub

Once the desired code is in place, simply click the top right X to get back to Excel, save and you are ready to go.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft