If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
autopopulate based on a cell value
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? |
#2
|
|||
|
|||
autopopulate based on a cell value
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 On Mon, 15 Mar 2010 15:06:01 -0700, Art wrote: 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? |
#3
|
|||
|
|||
autopopulate based on a cell value
You're awesome. Thanks!!!
"Chip Pearson" wrote: 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 On Mon, 15 Mar 2010 15:06:01 -0700, Art wrote: 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? . |
Thread Tools | |
Display Modes | |
|
|