Make input in cell CX become DX if EX changes to value ”Y” after the input in CX.

[Keyser_Soeze]

Im writing a quiz sheet

CX is answer input
DX is key
FX, GX, HX, IX are also correct answers but ’uglier’ without the, capital letters, parenthesis etc.
EX changes from ”X” to ”Y” based on a formula whenever a valid answer is entered.
I want CX to become DX whenever a valid answer is entered, ie when EX changes to ”Y”.

Thanks a lot if anyone can help

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Range("EX" & Rows.Count).End(xlUp).Row
    For Each rng In Range("EX1:EX" & LastRow)
        If rng = "Y" Then
            Range("CX" & rng.Row).Value = Range("DX" & rng.Row).Value
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Keyser_Soeze]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Range("EX" & Rows.Count).End(xlUp).Row
    For Each rng In Range("EX1:EX" & LastRow)
        If rng = "Y" Then
            Range("CX" & rng.Row).Value = Range("DX" & rng.Row).Value
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Thanks a lot mumps. Looks promising but I get error message "method range for object _Worksheet failed" freely translated from swedish .
I entered your code as:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim LastRow As Long, rng As Range
LastRow = Range("E2" & Rows.Count).End(xlUp).Row
For Each rng In Range("E2:E254" & LastRow)
If rng = "Y" Then
Range("C2" & rng.Row).Value = Range("D2" & rng.Row).Value
End If
Next rng
Application.ScreenUpdating = True
End Sub

First row is headline, there are 253 questions. Thanks a lot again!

 

[mumps]

Could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Keyser_Soeze]

Here is the file:

Music quiz - Google Drive

It's an image quiz so 42 MB. Column A is link to image, Column B is image, Column C i s answer, Column D is the "best" answer, Columns F-J are alternative answers, or RAND() if not used. Column E is formula turning to "Y" when a correct answer is entered (used for conditional formatting etc). Column I is original order of answers (1-253). Column J is RAND() for answer randomizing

Button START: randomizes questions, images will be deleted and reloaded (if only randomized, som images will disappear) so it may take a while
Button GIVE UP 1: shows correct answer of active cell
Button CLEAR: Clears all answers and images
Button REVIEW: puts all correct answers at the bottom, also involves deletion and reloading of images.

I tried to enter your code as stated above so there will be frequent error messaging, it can be found by right clicking Sheet1 (=Blad1) -> show code
All other VBA code is in "modul1" and can be accessed via VBA tools -> macros.

So what I want to happen is that when any of the correct answers is entered in C = one of the answers in D or F-J = E turning from "X" to "Y", that C then turns to D, regardless of if the answer entered is F-J or if it is D with wrong capitalization.

Hope everything works and thanks again!

 

[mumps]

Your file is causing Excel to freeze up on my computer. However, give this macro a try:

Option Compare Text
Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim rng As Range
    For Each rng In Range("E2", Range("E" & Rows.Count).End(xlUp))
        If rng = "Y" Then
            Range("C2" & rng.Row).Value = Range("D2" & rng.Row).Value
        End If
    Next rng
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Keyser_Soeze]

Your file is causing Excel to freeze up on my computer. However, give this macro a try:

Option Compare Text
Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim rng As Range
    For Each rng In Range("E2", Range("E" & Rows.Count).End(xlUp))
        If rng = "Y" Then
            Range("C2" & rng.Row).Value = Range("D2" & rng.Row).Value
        End If
    Next rng
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Closer now. this does it, but for the wrong cells. A correct answer in C2 gives D22 in C22 and D222 in C222 (not the desired D2 in C2), also does D33 in C33 when correct in C3 and so on

 

[mumps]

Try this version. Make a entry in column C and press the RETTURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    If Target.Offset(, 2) = "Y" Then
        Target = Target.Offset(, 1)
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Keyser_Soeze]

Try this version. Make a entry in column C and press the RETTURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    If Target.Offset(, 2) = "Y" Then
        Target = Target.Offset(, 1)
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Solved! thanks a lot. I'm kind of new to VBA, learned a lot through this. Much appreciated!

 

[mumps]

You are very welcome. I just noticed that you are using a Mac. VBA for the Mac is a little different from VBA for Windows and since I'm using Windows, I'm glad to hear that it worked on the Mac.