Select individual cells only?

[Sige]

Goodmorning All,

Is it possible allowing a user to select individual cells only?
No ranges with cells.count>1?

Brgds Sige

 

[Ron de Bruin]

Hi Sige

You can test the area

Sub zzzz()
Dim smallrng As Range
For Each smallrng In Selection.Areas
If smallrng.Cells.Count > 1 Then MsgBox smallrng.Address & " is not a correct selection"
Next
End Sub

 

[Gary Keramidas]

see if this is what you're looking for. right click a sheet tab, select view
code and paste it there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then
MsgBox Target.Address & " is not a correct selection"
End If
End Sub

 

[Gary Keramidas]

modified it a bit to not allow action on the range after the msgbox was
displayed

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then
MsgBox Target.Address & " is not a correct selection"
ActiveCell.CurrentRegion.Cells(1, 1).Select
End If
End Sub

 

[Sige]

Hi Gary,
Is what I am looking for!
Though ..I would like to invoke this on workbooks which I download
every day with around 70 sheets ...

Any ideas?
Sige

 

[Sige]

Hi Ron,
Works fine ... but as worksheet_selectchange it does what I am after.
Or quite. As I would like to have this message on every sheet in my
workbook!
But every day I start with a fresh one with about 70 sheets!

Any ideas?

MVG Sige

 

[Sige]

Hi Ron,

It does what it should ...but as Worksheet_SelectionChange-sub it has
the behaviour which I had in mind... or quite. As I would like to
invoke this behaviour on a fresh workbook with 70 sheets.
No other way than copying it in 1by1?

MVG Sige

 

[Norman Jones]

Hi Sige,

Use the Workbookbook_SheetSelectionChange event instead. This event will
operate on all sheets, existing and new.

 

[Norman Jones]

Hi Sige,

Sorry for the stutter,

: Workbookbook_SheetSelectionChange

should be:

Workbook_SheetSelectionChange.

 

[Ron de Bruin]

Hi Sig

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

In the Thisworkbook module
See
http://www.cpearson.com/excel/events.htm

 

[Sige]

)

That's it! Thx

Hi Sige,

Sorry for the stutter,


should be:

Workbook_SheetSelectionChange.

 

[Sige]

Thanks Ron!

Also for Chip's link.