jkp-ads.com Newsletter for February 2016
The Amsterdam Excel Summit
Registrations for our famous Amsterdam Excel Summit are pooring in. Make sure you reserve your seat soon as we only have limited space available! Join us on May 26th and 27th, 2016 in Amsterdam for the third Amsterdam Excel Summit. This time the Summit itself is a one-day event but we have added a post-conference training day: The Excel Charting And Dashboard Masterclass.
Redmond is there
Note that we'll have people from the Excel dev team at the conference, we will host a keynote from the people building Excel and you can ask them any question you like!
A moving checkbox
If you've ever worked with Yes/No input forms in Excel, you have probably had the pleasure of adding dozens of identical checkboxes to a worksheet, each pointing to its own cell. Like so:
Inserting these controls is a very tedious process, as you have to right-click each of them to assign them to their underlying cells. MOreover, as your data grows, you have to add chekcboxes too.
In the example I am about to show, I created a little event routine that draws a checkbox in the active cell, attached to a small macro that updates the value of the cell with the value of the checkbox.
In the code module behind the worksheet in question:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oCbx As CheckBox
Dim oSh As Worksheet
On Error Resume Next
Set oCbx = Me.CheckBoxes("jkpCbx")
If Intersect(Target, Me.Range("Checks")) Is Nothing Then
oCbx.Visible = False
oCbx.Visible = True
On Error GoTo 0
If oCbx Is Nothing Then
Set oCbx = Me.CheckBoxes.Add(Target.Cells(1, 1).Left, _
Target.Cells(1, 1).Top, Target.Cells(1, 1).Width, _
oCbx.Name = "jkpCbx"
oCbx.Caption = ""
oCbx.OnAction = "cbx_Click"
.Left = Target.Cells(1, 1).Left
.Top = Target.Cells(1, 1).Top
.Width = Target.Cells(1, 1).Width
.Height = Target.Cells(1, 1).Height
.Value = Target.Cells(1, 1).Value <> 0
Note that I use a named range "Checks", which will get the checkboxes automatically.
The macro connected to the checkbox is this one (in a normal module):
Select Case ActiveSheet.CheckBoxes(Application.Caller).Value
ActiveCell.Value = 0
ActiveCell.Value = 1
As soon as you have all pieces in place, as you click in any cell inside the named range "Checks" the event routine will display a checkbox in that same cell.
Download the sample file
Excel Events, courses
New dates for Excel VBA voor Financials (7th run)
We have set the dates for the seventh run of our successful (Dutch) course "Excel VBA voor Financials", which I present together with Tony De Jonker. Mark the dates: June 1, 8 and 15, 2016 For more information, visit topexcelcursus.nl
Financial modelling with Excel
Together with Tony de Jonker I will be hosting this in-house training on February 2, 2016.
Advanced Excel VBA
On February 16 and 23 I will be conducting an in-house training covering advanced VBA subjects such as Error handling, Userform design, Class modules.
Excel Summit South
If coming to our Amsterdam Excel Summit is too much of a trip to you, consider visiting the "Excel Summit South" by our Australian colleagues!