How to count cells coloured by conditional format in Excel

Another non moodle post.  C'est la vie.

Quite a few questions pop up on Google saying they have a spreadsheet that uses conditional formatting to colour cells and they want to be able to count how many cells of a certain colour they have.  All the answers seem to point to this post by C Pearson who has an answer but it's not super obvious what to do with it.

If your spreadsheet press Alt and F11 to open the visual basic editor.  This allows you to add function to Excel.  In the left hand column right click the bold heading, it should say VBA Project(name of your spreadsheet).  Go to Insert > Module.

In the window that opens copy and paste the function code C Pearson provides and click the save icon. Then do the same right click, Insert > Module and do the next block of code. You need to add the Active Conditioning one and then whichever of the others you think would be useful to you.  I did all of them so I could try them out and the used CountOfCF.

So once you've added all the functions, use the formula:

=CountOfCF(C11:S15,3)

The green numbers are the range of cells you are looking at and the red number is the number that corresponds to the colour you're counting.  For me this number is three because it is the third condition in the conditional formatting.

And there you have it, counting conditionally formatted cells.

Adding one user to multiple groups via bulk upload

So I've got a nice women from a council buying 300 courses for nurseries in her area.  She wants to able to keep an eye on them and see how many have completed and how many are being lazy and not doin' their learnins.

I posted before about using groups for enrolment keys and creating a new role with the ability to run reports.  The trouble with using these two methods together is that the person with the special role (I called it Local Authority) needs to be in all the groups that correspond with the enrolment keys she's giving out.  This means  I have to add my council lady to three hundred groups and there isn't just an easy way of doing it in one go.

The way around it is to use the bulk user upload option.  You can upload a CSV file to create new users, enrol them on a course, put them in a group and give them a non default role.  Now my council lady already has a user account so I don't need to create that or give her a password.

I started a spread sheet with these column headings:

username

firstname

lastname

email

course1

role1

group1

I fill in the next line with the existing accounts username, first name, last name and email address.  Then I copy and paste that line into the next 299 rows.  The three hundred enrolment keys are distributed across three courses, one hundred in each.  So in the course1 column, the first hundred rows are the short name of the first course.  I thought at first it would be the course ID but it is the short name you need.  The next hundred rows are the second course and the third hundred rows are the third course.  This will enrol her on the courses as a student.

Under role1 I put the special role short name, in may case it's LA.  Under group1 I copy and pasted the group names from the spreadsheet I used to upload groups to each course.

Then I saved this as a CSV and uploaded it under the Site Administration > Users > Accounts > Upload Users option. When you click upload you'll be taken to a page with a few options.  On this page, depending on your situation, change Upload Type to 'Add new and update existing users' This means it will update the existing account with the new courses, role and groups rather than trying to create the same account a bunch of times.

Hit Upload Users and the job should be done.  If you check enrolled uses on a course the account should have been added to it with the new role and put in all the groups.  So now my council lady can see the activity of everyone that uses one of her enrolment keys.

Mahara as an assignment

If you have got your Mahoodle up and running you can set a Mahara page as an assignment submission.  I intend to use this so I can set a group of learners a bunch of pages with text boxes in, which they can fill in and then have assessed.

I followed the instructions in this wiki but, as with everything Moodle related, it's not completely clear.

There are two Moodle plugins you need to install.  First download the local folder. Unzip this and you'll get a file called moodle21. Unzip this as well and you'll have a file called pax_global_header and a folder called mahara-contrib-local-mahara.  You need to upload the contents of the folder to your moodle in the /local/mahara directory (you might need to create the /mahara folder).

Next you need to download the assignment type plugin. Unzip this and you'll get another moodle21 file which  you also need to unzip.  Again you'll get a file and a folder.  Upload the contents of the folder to your moodle in the mod/assignment/type/mahara directory (again you might need to create the /mahara folder).


Login to your moodle as an admin and visit the notifications page.  Click refresh to complete the installation.


Still as an admin go to site administration > networking > peers > all hosts then click on the services tab.  You should see a new paragraph headed Extra Mahara MNet Module.  You need to click the subscribe box.  From what I understand, ticking the publish box makes no difference so I clicked it too.


If you go into any course you should now be able to set an assignment with an option to submit a Mahara page.

Assign an oversight role

Say the manager of a nursery school buys training courses for all the staff.  We issue enrolment keys, the manager distributes them and sits back happy to let the staff get on with it.  Three weeks later the manager wants to see how far the staff have got.

As an Admin create a new role.  Go to Settings > Site Admin > Users > Permissions > Define roles.  Click Add a new role.  Give the role a name (e.g. Monitor), short name and a description if you want.  In Role Archetype choose what role you want the new role to be based on.  I went with none-editing teacher.  In Context I chose Course, this means the role can be assigned at the course level.

Now go through the permissions and allow any that you want this new role to have.  This took a bit of trial and error for me but I pretty much allowed all reports and logs to be run and allowed them to see participants.

With the new role created you need the person to have an account.  You can do this for them or make them do it themselves.

Go to the course you want the person to be able to monitor.  This is the course they have bought for their staff, if they bought more than one you'll have to do this for each course they have staff enrolled on.  Go to Settings  Course Administration > Users > Enrolled Users.  At the top click Enrol Users.  In the Assign roles drop down choose the new role you created, find the person you want to give monitor rights to on the list and click enrol.  This will allow then to run the reports and see the participants of this course.

I've used separate groups so I can issue individual enrolment keys, these groups are all separate so users can't see each other.  This means that the new monitor won't see them either so we have to ass the monitor to all the groups their staff are in.  Refresh the Enrolled Users page of the course you're in and find the new monitor user.  Click the little plus sign in the groups column and add them to all the same groups as their staff, (bare in mind some of the enrolment keys they bought may not have been activated yet so add them to every group that corresponds with an enrolment key they purchased).  They will now be able to see everyone in the same groups that they are in.

Create new Local Authority role
Have the LA sign up like normal.in the course
In the course go enrolled users
Click Enrol users at the top.
Enrol the LA as the new Local Authority role.
Go to enrolled users
Click the plus sign Enrol that LA in all the relevant groups.
They will now be able to see everyone in those groups and when they last logged in.

How to make a check mark/tick in InDesign

I'll start by saying this has nothing to do with Moodle.

I have been designing book covers and one of them needed a tick mark.  So of course I did some Googling to try and find out how to do it.  I came across numerous posts from people trying to do the same thing and the answers they received varied from "It can't be done" to "It can be done but it's a massive hassle."

I eventually found the answer in the most unlikely of places, a Microsoft Excel help document.  This pointed out that Wingdings has a tick mark as one of its characters.

This is only for Windows by the way, I have no idea what to do on a Mac.

Got to Start > All Programs > Accessories > System Tools and choose Character Map.  In the font drop down choose Wingdings.  Now you can see all the handy little icons that make up Wingdings.  Click the one you want to use (the ticks are at the end) and press Select then Copy.  This will put the character on your clipboard.  Return to InDesign (or whatever program you're using) and paste the character in place.  The character will probably appear as a box because the font you're using doesn't recognise it.  Highlight the character and change its font to Wingdings and there you go.

I went one step further and made the tick mark into a bullet point. Create a new paragraph style for the list you want ticks next to.  In the Bullets and Numbering tab and click Add in the Bullet Character box.  Choose Wingdings from the Font Family drop down, click the tick mark you want.  This mark will now act as a bullet point for this list.

I'm not gonna lie, it was an utter revelation when I realised Wingdings actually had a use.

How to install a Magento extension

For those just joining us, I'm now in the process of setting up a Magento store to integrate with Moodle.

Magento is an e-commerce solution in a similar vein to Moodle, it is free and open source or there are people that will do the dirty work for you.  I will eventually be using Magento to sell Moodle courses but first I have to get Magento to work with our online payment gateway.

Magento has native support for Paypal but we're using World Pay so I have to install a plugin.

First we vist www.magentocommerce.com/magento-connect/ and search for the extension you want, you can choose to look at free or premium extensions.  Clicking on the nice big Install Now button.

Now, this might bring up a dropdown menu for you to choose Magento Connect 1.0 or 2.0.  I'll admit this means nothing to me and if you're reading this post it might not mean anything to you either.  In another tab or window log in to your Magento backend via the admin panel and go to System > Magento Connect > Magento Connect Manager, you'll be asked to log in again.  You'll be taken to the Magento Downloader and if you scroll to the bottom you'll see what version of Magento Connect Manager you're working with.  Mine says 1.5.0.0.

Now I assumed this meant I should choose Magento Connect 1.0 from the drop down on the extension page, but when I did it didn't work.  So I tried choosing 2.0 and that seemed to do the trick.

Choose whichever you think might work, tick the box to agree to the licence agreement and click get extension key.  Copy the code it brings up, paste it into the box on the Magento Downloader marked Paste extension key to install and click Install.

At this point it'll either work or return an error message.  If you get an error message (like I did) try the other extension key.  If it works, return to your Magento dash, see if you have any messages saying a cache needs refreshing and configure your new plugin.  In my case this mean going to System > Configuration > payment methods.

Adding terms and conditions check box to sign up page

Want your users to agree to your terms and conditions before they can create an account? No sweat.

As an admin go to Settings > Site administration > Security > Site policies.  Scroll down to Site Policy URL.  In the box but the URL of wherever your terms and conditions are  hosted online, this can be on your Moodle site or somewhere else if you have a company wide policy.

This puts in a link to the terms and condition and a check box that a new users must check before they can signup.

Simples.