Audit Spreadsheet

[EdRooney]

Hello There

Firstly, I am a newbie but know a little bit about excel... which I am beginning to think is a very dangerous thing!!!!
Secondly, I have joined the forum in the hope that one of you kind people will be able to help me!

I have an audit spreadsheet that I wish to 'automate' in some ways to help the end user.

Imagine I have 10 questions (Row 1 to 10) and each one has 5 options (Column B to F)

I have tried using radio buttons, in the thought this would be my easiest option) but cannot get the groups to work independently of each other. I dont know if it is because the radio button or group borders overlap or something, but I am just having no joy whatsoever!

Because I have a lot more than 10 questions in reality, I wanted to cut and paste the radio buttons / group of radio buttons, but I couldnt get that to work either!!!

I then found I could highlight a specific cell by double clicking it. I have had success with this in as much as I can now highlight / un-highlight a cell by double clicking it. What I cannot get this method to do is to place a score (1 to 5) at the end of the row so I can score what answer the user selects. eg... select answer in 2nd column would score 2 at the end of the row.

What I am also unable to do is to make it so that if I choose answer in column B and then change my mind and double click column C... then the highlight in column B would disappear and the new choice, Column C, would now be highlighted.

I hope you are still following me?!!!

I am thinking that VBA is my preferred route, if someone could be so kind as to write the script for me?!

I am giving up with the radio button idea as every time I try and copy the group or row... Excel shuts down or hangs on me.

I am using Excel for Mac 2011 if that helps?

I look forward to ANY responses (preferably positive!) that anyone can offer me.

Thank you in advance

 

[mathchick]

You could think about doing drop-down lists using data validation. With the cell highlighted where the user will select their answer do this: in the data tab select data validation, choose list and then enter the options for the question in that row. so if their options are apples, bananas or oranges enter it like this:
apples,bananas,oranges
with no spaces.

EDIT: and I forgot to mention, welcome to the formum!

 

[EdRooney]

Hi and thank you for the welcome

I am conversant with data validation but it wont meet my needs for this spreadsheet.

I want to be able to display each question and the 5 options all on one page so the user can select the right answer against each question, allowing them to change their mind after they have answered also.

Radio Buttons are perfect for my needs but I ma having no joy in getting them to behave for me!

Thank you for your response anyway

 

[mathchick]

looks like your question was already answered here:
http://www.mrexcel.com/archive/Controls/18853.html

 

[EdRooney]

I checked that out but I am using Office for Mac 2011 and cannot see control toolbox this refers to. I have attached a pic of the ribbon I have in my version of Excel

It is the Radio Button and Group Box listed here that I am using... as stated, cant find any control toolbox and not sure if this is the same thing or not?!

OOPS! Not sure what happened to the image I cut/paste. How do I attach an image... its asking for a URL!

 

[mathchick]

I believe you have to add the developer's tab. I'm on office 2010, but I'm sure if you google office 2011 and developer's tab you can find something. You have to upload the picture to another site and then link to it here, this site doesn't actually host images.

 

[mathchick]

I just tested it out and when my radial buttons are in the control boxes they behave the way you're wanting.

 

[EdRooney]

I have the developer tab showing on the ribbon.

So... which do you add first... the group box or the radio buttons? then, once done... can you copy and paste all/any part of the group etc?

As I said... I have lots of questions and dont really want to be having to align lots of radio buttons and groups etc

With what you have managed to get working.. try having a font size of 12... and then placing groups of 5 radio buttons along each row for a couple of rows and see if you can still get the buttons to act independently?

 

[mathchick]

I don't think it matters which you add first, in my test I added the group box first. I will say this though, I feel like it sometimes gets finicky about copying and pasting radial buttons if you don't want them grouped, but I'm not sure. My best suggestion would just be to try stuff and I just test as you go and if it didn't work hit undo and try something else. That's usually how I figure things out. Good luck

 

[EdRooney]

Well thank you for all your suggestions, I do appreciate your patience

Unfortunately I have tried all/most of those ideas without much success... so I will go back to my original question and ask for some help with VBA which will be less of an issue ... once I know what the code looks like!