Counter for user clicks

[Guest]

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

 

[NickHK]

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

 

[Guest]

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

 

[Jean-Yves]

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

 

[Guest]

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]

There is a comma missing
Private Sub Workbook_SheetSelectionChange(ByVal Sh , As Object
ByVal Target As Range)
The proper way to create such a macro name is to go to the project exp,orer
Double-click on the ThisWorkbook module
In the window part left top, you should see a dropdown reading General.
Select there Workbook instead.
In the window part righttop, you should see another dropdown reading
Declaration or Open now as you have selected the workbook events just
before.
Select here now Workbook_SheetSelectionChange
all this events are preprogrammed and the code that will be inserted will be
executed/fired when the event takes place.
Standard events often used are Open , Close, before print, before save , etc

HTH
Regards.
JY