autopopulate based on a cell value

[Art]

I created a spreadsheet in Excel 2007 for instructors to use to prepare
multple choice questions for an exam. Each row represents a different
questions. So, for example,


B1: What is my favorite color?
C1: Red
D1: Incorrect
E1: Yellow
F1: Correct
G1: Blue
H1: Incorrect
I1: Green
J1: Incorrect
K1: Pink
L1: Incorrect

Column B is the question and columns C, E, G, I, K are the five possible
choices and columns D, F, H, J, and L designates whether the previous column
is a correct or incorrect answer. When exported and imported into our
learning management system, students would see this:

1. What is my favorite color?
a. Red
b. Yellow
c. Blue
d. Green
e. Pink

(Note: This is the format we have to use in order for it to be exported and
then imported correctly into our learning management system.)

The spreadsheet works fine if information is entered manually; however, I'd
like to make it easier for instructors so that if once they enter Correct
into any of the columns D, F, H, J, and L, it autopopulates the remaining
columns with Incorrect. This would work even if they change their mind and
change a different column to Correct and then the remaining columns (even the
one originally designated as Correct) to Incorrect.

I played around with some macros but don't know enough to really understand
what the statements in the macros do to adjust for my needs.

Any thoughts?

 

[Chip Pearson]

You can't do it with formulas because a cell can contain either a
formula or a value, not both, so changing a cell from "correct" to
"incorrect" would destroy the formula. You would need to use a VBA
event procedure that will run when a cell is changed. You say that

into any of the columns D, F, H, J, and L, it autopopulates the remaining
columns with Incorrect.

Which are the "remaining columns" to be populated. Just D, F, H, J,
and L? Right-click on the sheet tab and choose View Code. That will
open the VBA editor to the code module for that worksheet. Paste in
the following code. Change the line marked with <<<< to the range of
cells on the worksheet that should trigger the action. If a change is
made in this range, the code will run. If the change is to a cell
outside this range, the code exits without doing anything.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Range
Dim S As String
Dim T As String
Dim ActiveRegion As Range
Set ActiveRegion = Range("B2:L100") '<<<<<<<
If Application.Intersect( _
Target, ActiveRegion) Is Nothing Then
' not in range
Exit Sub
End If
Set R = Target
Application.EnableEvents = False
S = R.Text
If S = "correct" Then
T = "incorrect"
ElseIf S = "incorrect" Then
T = "correct"
Else
Application.EnableEvents = True
Exit Sub
End If
Select Case R.Column
Case 4, 6, 8, 10, 12
R.EntireRow.Cells(1, 4).Value = T
R.EntireRow.Cells(1, 6).Value = T
R.EntireRow.Cells(1, 8).Value = T
R.EntireRow.Cells(1, 10).Value = T
R.EntireRow.Cells(1, 12).Value = T
R.Value = S
Case Else
' do nothing
End Select

Application.EnableEvents = True
End Sub


With this code in place, return to Excel. If the user enters "correct"
(or "incorrect") in column D, F, H, J, or L, these columns on that row
will change to "incorrect" (or "correct")

See http://www.cpearson.com/excel/Events.aspx for much more
information about Excel events.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Art]

You're awesome. Thanks!!!

You can't do it with formulas because a cell can contain either a
formula or a value, not both, so changing a cell from "correct" to
"incorrect" would destroy the formula. You would need to use a VBA
event procedure that will run when a cell is changed. You say that


Which are the "remaining columns" to be populated. Just D, F, H, J,
and L? Right-click on the sheet tab and choose View Code. That will
open the VBA editor to the code module for that worksheet. Paste in
the following code. Change the line marked with <<<< to the range of
cells on the worksheet that should trigger the action. If a change is
made in this range, the code will run. If the change is to a cell
outside this range, the code exits without doing anything.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Range
Dim S As String
Dim T As String
Dim ActiveRegion As Range
Set ActiveRegion = Range("B2:L100") '<<<<<<<
If Application.Intersect( _
Target, ActiveRegion) Is Nothing Then
' not in range
Exit Sub
End If
Set R = Target
Application.EnableEvents = False
S = R.Text
If S = "correct" Then
T = "incorrect"
ElseIf S = "incorrect" Then
T = "correct"
Else
Application.EnableEvents = True
Exit Sub
End If
Select Case R.Column
Case 4, 6, 8, 10, 12
R.EntireRow.Cells(1, 4).Value = T
R.EntireRow.Cells(1, 6).Value = T
R.EntireRow.Cells(1, 8).Value = T
R.EntireRow.Cells(1, 10).Value = T
R.EntireRow.Cells(1, 12).Value = T
R.Value = S
Case Else
' do nothing
End Select

Application.EnableEvents = True
End Sub


With this code in place, return to Excel. If the user enters "correct"
(or "incorrect") in column D, F, H, J, or L, these columns on that row
will change to "incorrect" (or "correct")

See http://www.cpearson.com/excel/Events.aspx for much more
information about Excel events.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com









.