Results 1 to 6 of 6

Thread: attendance list

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    attendance list

    Hi All,

    A friend of mine runs a dance school where, of course, attendance needs to be taken. She had an Apple-based spreadsheet (Numbers) last year that allowed her to put in checkboxes (no VBA or equivalent); teachers would access the proper attendance list from iCloud on an iPad and just tap the cell to mark a student present for a given date.

    This year she converted the attendance files to Excel on Apple. But there doesn't seem to be a way of inserting checkboxes. I'm a little familiar with checkboxes with Forms in VBA but the two don't seem equivalent. Is there some way to insert checkboxes in the "native Excel" and tap the screen to check the box? I did a google search and it seems that Excel 2013 with Windows 8 has some capability along these lines. But she also converted to Google Drive (which has some other issues).

    TIA

    Fred

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Fred:

    Your post does not mention if anyone attempted a simple test. In other words, did anyone create a new Apple Excel Workbook and insert a checkbox control? Then upload to cloud and test on an iPhone.

    I ask because sometimes coverting files from "numbers" the to Apple Excel and loading to the Cloud could case issues that may not occur if you start with a new workbook and inset the control.


    If the test works then copy over as much data as possible from the old to the new and then copy and paste the 'functional' checkbox as many times as needed.

    Good Luck
    TD

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I have NO knowledge of excel for apple but in regular excel you could use a worksheet event such as select, double click, right click, etc. to mark a cell.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Fred,

    I know you said no VBA but JIC:
    Code:
    Option Explicit
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("AttendanceRng"), Target)
       If isect Is Nothing Then
    '     MsgBox "Ranges do not intersect"
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
    '     MsgBox "A1 Changed", vbOKOnly + vbInformation, "Cell Changed"
    '***** Your code here *****
         If UCase(Target.Value) = "X" Then
           Target.Value = ""
         Else
           Target.Value = "X"
         End If
         
         Application.EnableEvents = True '*** Reset Events ***
       End If
    
       Cancel = True
       
    End Sub
    Put this in the WorkSheet module then define the rangename for the cells where attendance is taken.
    fred.JPGNow all a user has to do is double-click the desired cell. The cell will toggle between X and blank. For maximum flexibility use a dynamic range name then you can add student names to your hearts content.

    Test File: fred.xls

    HTH

    Update: I saved this as .xls for compatibility then went back to try something and saving to .xls seems to have broken it. Will continue to test but wanted to give you a heads up!

    Update2: Dummy me! I saved the file to my Posting Directory which is NOT a trusted location. Moving it back to a trusted directory solved the problem. Wouldn't have to to all this if it wasn't for those #$@&% hackers!
    Last edited by RetiredGeek; 2014-09-16 at 16:37.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks to all who gave us some good thoughts.

    My friend has an appointment at the local Apple store today, so we'll see what happens.

    TD-no we didn't run that simple test but it may be worthwhile doing. She would not know anything about checkbox controls but I didn't see the equivalent on my Windows Excel - first place I checked in the Insert menu. We'll see what she comes back with.

    As far as VBA solutions, I am familiar with worksheet change events, etc., although I've not done much VBA programming for Excel lately and even less with forms. Although I didn't say a VBA solution was ruled out, I'm thinking such a solution would need a fair amount of testing. For example, can it be run under iCloud or google drive? Also, double clicking is not the most desirable approach - a stylus tap, as was done last year, would be ideal. And from what I saw, that was with Win8 and Excel 2013 (although many commenters in my search did not seem to think highly of what MS was currently offering for touch screen type controls).

    So let me see what she comes back with from the Apple Store. Will keep you posted.

    Fred

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    OK - the issue is now resolved.

    Excel can't handle this in the way she wants - seems the same conclusion is true regardless of Apple or Windows platforms.

    She had used a spreadsheet called Numbers which only runs on Apple platforms. This has a touch feature. So she's going back to it. But she'll probably use Excel for first steps to gather the class rosters from her student DB, filter to get the students for each class, and finally copy-paste each roster into a separate Numbers file. This file goes into the cloud.

    Who would have thought? thanks to all who helped.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •