| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to have errr msg pop up when user clicks on locked cell | Excel Discussion (Misc queries) | |||
| How can I execute a macro right after the user clicks the validation drop down? | Excel Programming | |||
| Open an Outlook folder when a user clicks on a command button ... | Excel Programming | |||
| Invalid Picture Error when user clicks on toolbar | Excel Programming | |||
| How to create a URL and when user clicks it to open a Excel workbook | Excel Programming | |||
Linear Mode
