Goodmorning All,

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

Brgds Sige

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sige" wrote in message ups.com...
Goodmorning All,

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

Brgds 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

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

Hi Sige,

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


---
Regards,
Norman



"Sige" wrote in message
oups.com...
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

Hi Sige,

Sorry for the stutter,

: Workbookbook_SheetSelectionChange

should be:

Workbook_SheetSelectionChange.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Sige,

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


---
Regards,
Norman



"Sige" wrote in message
oups.com...
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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Norman Jones" wrote in message ...
Hi Sige,

Sorry for the stutter,

: Workbookbook_SheetSelectionChange

should be:

Workbook_SheetSelectionChange.

---
Regards,
Norman



"Norman Jones" wrote in message ...
Hi Sige,

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


---
Regards,
Norman



"Sige" wrote in message oups.com...
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

:o)

That's it! Thx

Norman Jones wrote:
Hi Sige,

Sorry for the stutter,

: Workbookbook_SheetSelectionChange

should be:

Workbook_SheetSelectionChange.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Sige,

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


---
Regards,
Norman



"Sige" wrote in message
oups.com...
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

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


"Sige" wrote in message
ups.com...
Goodmorning All,

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

Brgds Sige

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

--


Gary


"Sige" wrote in message
ups.com...
Goodmorning All,

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

Brgds 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


Gary Keramidas wrote:
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

--


Gary


"Sige" wrote in message
ups.com...
Goodmorning All,

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

Brgds Sige