Copy

Newsletter by jkp-ads.com

View this email in your browser

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!

Register now!!!.

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:

Option Explicit

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
        Exit Sub
    Else
        oCbx.Visible = True
    End If
    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, _
            Target.Cells(1, 1).Height)
        oCbx.Name = "jkpCbx"
        oCbx.Caption = ""
        oCbx.OnAction = "cbx_Click"
    Else
        With oCbx
            .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
        End With
    End If
End Sub

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):

Sub cbx_Click()
    Select Case ActiveSheet.CheckBoxes(Application.Caller).Value
    Case -4146
        ActiveCell.Value = 0
    Case 1
        ActiveCell.Value = 1
    End Select
End Sub
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!

 

Auditing of Formulas made easy

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.
 

Mission-critical Excel Model Crashes

Do you have mission-critical Excel files that cause problems? Consider our Excel File Remediation Utility
.
 

Nederlandse cursus: Excel VBA voor Financials

Kost uw maandrapportage u vele uren saai en repeterend werk? Dan is het de hoogste tijd voor onze cursus Excel VBA voor Financials!.
De Volgende cursus wordt gegeven op 1, 8 en 15 juni 2016
 
Copyright © 2016 JKP Application Development Services, All rights reserved.


unsubscribe from this list    update subscription preferences 

Email Marketing Powered by Mailchimp