VBA Help Needed

[freeisgood]

Hello,

I am learning VBA, the assignment is to achieve the following:

Column A: Employee/ID
Column B: Review (Validation with the following option: 1)Like it, 2)Don’t Like it 3)See Comment
Column C: Reason (if “Like it” or “Don’t Like it” is chosen “n/a” should be automatically populate in column c and if “See Comment” is chosen then cell in column c should be available for user to enter their comment.)

The workbook will be located in shared drive which is in shared and protected mode. 500 respondents is expected The following code work only if there is no gap/skip “Review”, but if you skip one entry the code would work. I think that I know where is the problem (“If Cells(xRow, 2).Value = "" Then End”, but can’t come with alternative.

Please Help!

Thanks you.

Codes:
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Dim xRow As Integer
xRow = 2

For xRow = 2 To 502
If Cells(xRow, 2).Value = "" Then End
'do nothing

If Cells(xRow, 2).Value = "See Comment" Then
'do nothing

Else
If Cells(xRow, 2).Value <> "" Then Cells(xRow, 3).Value = "n/a"
End If


Next xRow

Application.ScreenUpdating = True

End Sub

 

[repairman615]

Hello,

Try:

Then Exit Sub

 

[freeisgood]

Hello,

Try:

Then Exit Sub

Thanks for trying but your suggested alternative generate an error "Compile error: Next without For"

 

[GTO]

Presuming:

1. The sheet is password protected.
2. Column B (a range therein) is unlocked (the cells)
3. The ID's are already in Column A and other then Column B, all cells are locked.
4. Column B has the Data Validation as you mentioned.

Maybe...

Excel Workbook
	A	B	C
1	ID	Review	Comment
2	eid-000123	Don't like it	n/a
3	eid-000124	Don't like it	n/a
4	eid-000125	Like it	n/a
5	eid-000126	See comment	I have reservations
6	eid-000127
7	eid-000128
8	eid-000129
9	eid-000130
10	eid-000131
11	eid-000132
Sheet1

Excel 2003

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
    
    Me.Protect "MyPassword", True, True, True, True
    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("B2:B502")) Is Nothing Then
        For Each Cell In Target
            Select Case Cell.Value
            Case "See comment"
                Cell.Offset(, 1).ClearContents
                Cell.Offset(, 1).Locked = False
            Case "Like it", "Don't like it"
                Cell.Offset(, 1).Value = "n/a"
                Cell.Offset(, 1).Locked = True
            Case Else
                Cell.Offset(, 1).ClearContents
                Cell.Offset(, 1).Locked = True
            End Select
        Next
    End If
    Application.EnableEvents = True
End Sub

Hope that helps,

Mark

 

[freeisgood]

Presuming:

1. The sheet is password protected.
2. Column B (a range therein) is unlocked (the cells)
3. The ID's are already in Column A and other then Column B, all cells are locked.
4. Column B has the Data Validation as you mentioned.

Maybe...

Excel Workbook
	A	B	C
1	ID	Review	Comment
2	eid-000123	Don't like it	n/a
3	eid-000124	Don't like it	n/a
4	eid-000125	Like it	n/a
5	eid-000126	See comment	I have reservations
6	eid-000127
7	eid-000128
8	eid-000129
9	eid-000130
10	eid-000131
11	eid-000132
Sheet1

Excel 2003

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
    
    Me.Protect "MyPassword", True, True, True, True
    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("B2:B502")) Is Nothing Then
        For Each Cell In Target
            Select Case Cell.Value
            Case "See comment"
                Cell.Offset(, 1).ClearContents
                Cell.Offset(, 1).Locked = False
            Case "Like it", "Don't like it"
                Cell.Offset(, 1).Value = "n/a"
                Cell.Offset(, 1).Locked = True
            Case Else
                Cell.Offset(, 1).ClearContents
                Cell.Offset(, 1).Locked = True
            End Select
        Next
    End If
    Application.EnableEvents = True
End Sub

Hope that helps,

Mark

Its works, thanks Mark!

one quick question what is the meaning of the following two lines?

Me.Protect "MyPassword", True, True, True, True
Application.EnableEvents = False

Regards,
Van

 

[GTO]

Its works, thanks Mark!

one quick question what is the meaning of the following two lines?

Me.Protect "MyPassword", True, True, True, True
Application.EnableEvents = False

Regards,
Van

'Me' refers to the object that the object/class module belongs to, in this case, the worksheet. Technically it is not necessary, as the unqualified Protect would apply to the sheet by default (the same as Range), due to the Protect being run from the sheet's module. Regardless, it seems sensible to me (blonde brain and all...) to keep things explicit and clear.

.Protect of course protects the worksheet. When you look at VBA help, notice the 4th argument UserInterfaceOnly, as this allows the running code to change values and such of cells that are locked.

.EnableEvents = False means that the Worksheet_Change (or any other event) will not be raised. Thus, we prevent a recurs when we change the value of the offset cell.

 

[freeisgood]

Thanks Mark!

Now I understand that I need to use "Select Case" to achieve my goal. For my personal knowledge, I need to rewrite using codes that were taught during class. Could you please look at what I wrote and advise how to make it loop to cover range B2 to B502?

Again Thanks

Codes:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

n = Range("B2:B502").Value

Select Case n

Case "Like it"
ActiveCell.Offset(0, 1).Value = "n/a"

Case "Don't Like it"
ActiveCell.Offset(0, 1).Value.Value = "n/a"

Case "See Comment"
ActiveCell.Offset(0, 1).ClearContents

Case Else
ActiveCell.Offset(0, 1).ClearContents

End Select
End Sub