Counter for user clicks
 

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

Counter for user clicks
 

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

Counter for user clicks
 

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

Counter for user clicks
 

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

Counter for user clicks
 

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

Counter for user clicks
 

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

"Anthony" wrote in message
...
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