Userform command button query

[palaeontology]

I have a UserForm which I intend to use as a 'touchpad' to record swimming races into a workbook with 12 worksheets (there are other worksheets, but these particular 12 need info from the UserForm).

There are 9 Userform Command Buttons on the UserForm which, when pressed, should send information to one of the 9 cells below the most recent selected cell on the worksheet I'm in at that moment.

In the image above, I'm currently in the sheet called '7 Freestyle' so, for example, if I'm about to record the results for Race Number 1, I would click onto cell '7 Freestyle'!C5, then when the swimmers finish their race, I'd be pressing the Lane numbers (the 9 Command Buttons on the UserForm) in the order that they finished.

So, if the finished in the following Lane order ... Lane 4, Lane 5, Lane 1, Lane 6, Lane 9, Lane 3, Lane 7, Lane 2, Lane 8 ... then I'd want ...
* the Lane 4 command button to send a 4 into cell '7 Freestyle'!C6
* the Lane 5 command button to send a 5 into cell '7 Freestyle'!C7
* the Lane 1 command button to send a 1 into cell '7 Freestyle'!C8
* the Lane 6 command button to send a 6 into cell '7 Freestyle'!C9
etc etc
and it would look like this ...

Then, when that entry has finished, I'd be ready for Race number 2, so I'd click onto the '2' in cell '7 Freestyle'!D5, so that when Race 2 finishes and I press the Userform command buttons, again in the order the Lanes finished, I'd need the corresponding command button 'lane numbers' to be sent into cells D6, D7, D8, D9 etc etc

I've dabbled with userform command buttons in the past, but have usually had them send their information to the same location each time.

I don't know how to code to have the information sent to a dynamic location, as described above.

Can anyone give me a push in the right direction ?

Very kind regards,

Chris

 

[palaeontology]

ok, I've played around with the following code to try to aciheve what I need ...

Private Sub CommandButton1_Click()
Range(ActiveCell, ActiveCell.End(xlUp)).Offset(1, 0) = "1"
End Sub

but while it places a 1 into the cell below the one I previously clicked into, it's also placing a 1 into a position 3 cells higher, and in addition to that, if I then press the command button a second time, expecting it to place a 1 into the next cell down from the previous placement, it simply writes over the top of the previously placed 1.

I'm getting closer, but still well-short.

Any help would be greatly appreciated.

 

[Fluff]

Create a new class module & call it ClsCmdBtn and put this code in it

Option Explicit

Public WithEvents CmdBtn As MSForms.CommandButton

Private Sub CmdBtn_Click()
   ActiveCell.Offset(1).Select
   ActiveCell.Value = CmdBtn.Caption
End Sub

The in the Userform_Initialize event put

Private CmdBtnClick As Collection

Private Sub UserForm_Initialize()
   Dim Ctrl As MSForms.Control
   Dim CmdB As ClsCmdBtn
   
   Set CmdBtnClick = New Collection
   For Each Ctrl In Me.Controls
      If TypeName(Ctrl) = "CommandButton" Then
         Set CmdB = New ClsCmdBtn
         Set CmdB.CmdBtn = Ctrl
         CmdBtnClick.Add CmdB
      End If
   Next Ctrl
End Sub

You can then get rid of the individual Command button events.

 

[palaeontology]

Thanks Fluff, I'll give it a try but haven't done class modules before

 

[Fluff]

You're welcome, any problems just shout