Multiple VBAs on a worksheet.

[Worksong]

Hello, I'm setting a worksheet to score likert-scale (responses from 0-3, 1-5, etc.) questionnaires. I've been able to get code working to have one range of cells return the appropriate value when clicked (and 0 when clicked a second time), but haven't been able to get code for multiple columns to work. For example, for a worksheet that has three columns, one each for responses from 1-3, the code for the "1" column is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myTarget As Range
Dim NewVal As Single

Set myTarget = Range("D15:D34")
If Selection.Cells.Count = 1 Then
If Not Intersect(Target, myTarget) Is Nothing Then
Select Case Target.Value
Case 0: NewVal = 1
Case 1: NewVal = 0
Case Else: NewVal = Target.Value
End Select
Target.Value = NewVal
End If
End If
End Sub


This works as expected. I want to add two more target ranges: E15:E34 to return 2 when clicked, and F15:F34 to return 3 when clicked. I've tried a few variations of adding in lines of code for the second (E15:E34, returns "2" or 0), but none have worked thus far. I'm not sure where my syntax is going wrong. Any help is appreciated! (Eventually I want each row to have each row only allow one response, but one thing at a time.)

 

[kevin9999]

You can't have 2 Worksheet_SelectionChange modules on the same sheet, the two separate things you're trying to achieve have to be incorporated in the one sub. Also, what do you expect "0-Target" to achieve? If there's a value in that cell to start with, you'll end up with a negative number, if there's just a zero (or nothing) in the cell - nothing will change.

 

[kevin9999]

This is a significant addition to your additional post which IMO warrants a new thread.

 

[Worksong]

This is a significant addition to your additional post which IMO warrants a new thread.

That all makes sense - thank you. I will begin a new thread.

 

[Worksong]

You can't have 2 Worksheet_SelectionChange modules on the same sheet, the two separate things you're trying to achieve have to be incorporated in the one sub. Also, what do you expect "0-Target" to achieve? If there's a value in that cell to start with, you'll end up with a negative number, if there's just a zero (or nothing) in the cell - nothing will change.

That makes sense. When you put it that way, the 0-Target seems to be an unnecessary line of code. It really serves no purpose b/c the cell is empty to start with. Thanks!