Disable Range Selection

[Muninn Crow]

Hi, I'm trying to make it so that the user cannot select certain cells of a worksheet. I've gotten it so that they can't select individual cells, right now if those cells are colored black, and if they try it simply returns their selection to wherever it was before they tried, but unfortunately, you can still click, hold, and drag the cursor to select a range, which can include these black cells. Is there a way to prevent the user from being able to select a range of cells, or a way to at least prevent them from selecting a range that includes one or more black cells?

 

[Guanjin Peter]

how about hiding those rows?

 

[Joe Was]

If selection by cursor or mouse is a black colored cell shift that selection one cell right.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sheet module code, like: Sheet1.

If Target.Interior.ColorIndex = 1 Then Target.Offset(0, 1).Select
End Sub

 

[Muninn Crow]

Yea, that works the same as what I have so far. If it's just one black cell that you select, then it moves the selection over one. But it still allows the user to select a range that includes black cells. If the range is ENTIRELY black cells, it will move over, but if even one cell isn't black, it doesn't act.

I don't know VB that well, but is there maybe a way to prevent the user from selecting cells at all with the mouse? Could I disable the mouse selection of cells, and disable the shift key altogether?

 

[Joe Was]

This will work on single selections or selections of more than one cell. If any cell in the selection is colored black the selection is offset:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sheet module code, like: Sheet1.

If Target.Count = 1 Then
If Target.Interior.ColorIndex = 1 Then Target.Offset(0, 1).Select
End If

If Target.Count > 1 Then

For Each Cell In Target
If Cell.Interior.ColorIndex = 1 Then ActiveCell.Select
Next Cell
End If
End Sub

 

[Muninn Crow]

That works perfectly, thank you very much for your help.