Hi,
thanks for your suggestions, however both still return the code with an
error by highlighting it in red,
the content must have someting missing?
can you see if you can find it?
thanks

"Jean-Yves" wrote:

Put it back on one line : Private Sub Workbook_SheetSelectionChange(ByVal Sh
As Object ByVal Target As Range)
or Alternatively, for reading purposes, you can split using the underscore
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object ByVal Target
As _
Range)
HTH
Regards
JY

"Anthony" wrote in message
...
Nick,
thanks for ur help,
before | can try ur code, I have pasted it into the vb explorer and this
part is highlighted in red, ie an error and as my knowledge isn't too good
on
vb I don't know what is wrong, can you check it out for me..

the bit highlighted in red is...

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

thanks

"NickHK" wrote:

Here's one way, using the ThisWorkbook module. However, your description
and
example do not seem to match ??

Dim ClickCount As Long
Private Const STARTCLICKCOUNT As Long = 21

Private Sub Workbook_Open()
ClickCount = STARTCLICKCOUNT
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As Range)
ClickCount = ClickCount - 1

Select Case ClickCount
Case 18, 15, 11, 8, 4, 2
Call Banker(Sh, Target)
Case Else
'Do Nothing ?
End Select

End Sub

Private Function Banker(WhichSheet As Worksheet, WhichCell As Range) As
Long
MsgBox "Cell '" & WhichCell.Address & "' clicked on sheet '" &
WhichSheet.Name & "'"
End Function

NickHK

"Anthony" wrote in message
...
Can anybody provide some code for the following ..

Basically, the user is playing a game which consists of 21 rounds -
after
the first 4 rounds they are forced to 'gamble' (and taken sheet4) after
the
next 3 and every 3 thereafter they are again forced to 'gamble' until
there
are just two rounds left. At this point the game ends and sheet6 is
opened

So what I really need is a 'counter' which starts at 21 and each time
the
user clciks a cell on sheet1 the counter reduces by 1. When this
counter
reaches either 18,15,11,8,4,2 it firstly reduces the counter by 1 then
runs
the 'banker' macro.

I hope I have explained this well and hope even more it can be done!
many thanks in advance