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
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 If End Sub
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 If End 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.
See Also: Excel Duplication Manager Add-in | Excel Number Manager Add-in | Excel Text Manager Add-in | Excel Named Range Add-in Manager | Excel OzGrid Plus Add-in | Excel Time Sheet | Excel Time Wage and Pay book
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. ALL purchases 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
Excel Trader Package Technical 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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates