VBA - Only working on first cell in range

Lee Rabbit

New Member
Joined
Apr 30, 2020
Messages
41
Office Version
  1. 2010
Platform
  1. Windows
Hi All,

Spot of trouble here. I am trying to prevent certain cells from being modified so when they are selected, an error message displays and the user is offset by one cell down.

The code below sort of works, but only for the first cell in the declared range. I require it to work on all cells in the range.

I am probably missing something so silly but I need your guidance as to where I am going wrong.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range
    Set aCell = ActiveSheet.Range("B2,C2,E2,F2,E16,F16,H2,I2,H6,I6,H16,I16,K2,K5,K7,L7,K16,L16,L2")
    
    If Target.Cells = aCell Then
        
        MsgBox " THIS CELL CANNOT BE MODIFIED " _
        , vbCritical, "ERROR MESSAGE"
            Cells(Target.Row, Target.Column).Offset(1, 0).Select
            
    End If
           
End Sub

Thank you in advance for your help.

Regards,
Lee
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range
    Set aCell = ActiveSheet.Range("B2,C2,E2,F2,E16,F16,H2,I2,H6,I6,H16,I16,K2,K5,K7,L7,K16,L16,L2")
    
    If Not Intersect(Target, aCell) Is Nothing Then
        
        MsgBox " THIS CELL CANNOT BE MODIFIED " _
        , vbCritical, "ERROR MESSAGE"
            Target.Offset(1, 0).Select
            
    End If
           
End Sub
 

Lee Rabbit

New Member
Joined
Apr 30, 2020
Messages
41
Office Version
  1. 2010
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range
    Set aCell = ActiveSheet.Range("B2,C2,E2,F2,E16,F16,H2,I2,H6,I6,H16,I16,K2,K5,K7,L7,K16,L16,L2")
   
    If Not Intersect(Target, aCell) Is Nothing Then
       
        MsgBox " THIS CELL CANNOT BE MODIFIED " _
        , vbCritical, "ERROR MESSAGE"
            Target.Offset(1, 0).Select
           
    End If
          
End Sub

Fluff, we must stop meeting like this. But once again, thank you for your help, works perfect!

Cheers,
Lee
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,240
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top