Form Control Buttons

Hello, I need help in designing a sheet that will allow us to Click on a number from 0 to 37.

The number clicked needs to be used in another cell.

A sample is attached.

Thanks

Nick

Hello and thanks. A click of a number would work just great.

I can't upload another sheet or I just don't know how to, but what I need is the 1st number clicked to be put into Cell A5. The
2nd number clicked to be put into Cell A6. The next number clicked to be put into Cell A7. etc.

As always thanks for your help.

Nick

Your numbers are already in column A. I don't think you mean to wipe them out. Take a look at your file format, and your most recent post, and let me know what you need to do. If your file format is different provide the correct file.

To attach a file to a post, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window, and save your post.

Thanks for your reply and help in navigating this site.

I have tried to uploaded another sheet showing we need the Clicked Numbers to appear in Cell A5 to start and then the next Clicked number to be in Cell A6, then next Clicked number in Cell A7, etc.

We need Columns B and C black so we can insert our "bot" codes.

Cheers and thanks again.

Nick

Sorry I meant to say

We need Columns B and C BLANK so we can insert our "bot" codes.

Old age getting to me

Private Sub Worksheet_selectionChange(ByVal Target As Range)
  

  On Error GoTo ErrHandler
  Application.EnableEvents = False
  If Not Intersect(Target, Range("D2:D19,E2:E20")) Is Nothing Then
     lr = WorksheetFunction.Max(Cells(Rows.Count, "A").End(xlUp).Row + 1, 5)
     Cells(lr, "A") = Target.Value
  End If
ErrHandler:
  Application.EnableEvents = True
End Sub

Click on number in columns D & E

Thank you so much for your help.

I have tried the “Clicker” and it works just great.

Per the attachment, you can see the “Clicker” work.

As each number is “Clicked”, the sheet calculates the Standard Deviation.

For the 1st number Clicked which is number 4 and it is shown in Cell A5. The Standard Deviations (SD) for # 4 and all other numbers are shown in Cells EE5 to FO5.

What I need help to do is to put each SD in the SD Columns I2 to I19 and K2 to K20. So after #4 is Clicked those columns would show the SD in Cells EE5 to FO5.

When the next number is clicked, # 22, SD Columns I2 TO i19 AND k2 TO k20 will show the SD in Cells EE6 to FO6. Etc.

Hope I made myself clear and again thanks for all your help.

Cheers
Nick

Private Sub Worksheet_selectionChange(ByVal Target As Range)
  

  On Error GoTo ErrHandler
  Application.EnableEvents = False
  
  If Not Intersect(Target, Range("D2:D19,E2:E20")) Is Nothing Then
  
     lr = WorksheetFunction.Max(Cells(Rows.Count, "A").End(xlUp).Row + 1, 5)
     Cells(lr, "A") = Target.Value
     '
     ' Count number of entries in "A"
     '
     n = WorksheetFunction.Count(Range("a5:a32"))
     '
     ' Assign Standard Deviations from EE onwards
     '
     For r = 2 To 19
        Cells(r, "I") = Cells(n + 4, r + 133)
        Cells(r, "K") = Cells(n + 4, r + 151)
     Next r
        Cells(20, "K") = Cells(n + 4, 171)
    End If
    
ErrHandler:
  Application.EnableEvents = True
End Sub

Hello John,
Thanks so much for your help.
I think I might have messed up the sheet or there might be an error in the code.
The Number 10 was clicked and in the Cell I11 should show .0944 but it does not. The .0944 shows up
In Cell I14 so it seems to be off by a count of 4.
Also notice that nothing appears in Cell I4 but that maybe the result of the above as well.
Do you know how to put in a “Clear Button” ? Which when clicked would Clear the Contents from Cells A5 to end and to Clear Contents of Column I, K,N and Q.
I have attempted to put the contents of Evens into the appropriate cells just like you did with your sub routine but I used Excel functions and as you can see it works but it is quite laborious and will be more so when the sheet extended to 100 rows or more.
Is there another way to transport these numbers using Excel’s functions rather than your sub routine? I am not familiar with them so I can’t make any changes if needed.
Thanks
Nick

It is out by 4 because you changed SD to Start at EI not EE.

And you cannot clear column N as you will delete all the formulas.

Added Clear Button for A, I and K

I left out N As it has formulae and assumed Q would also have formulae.

plus changed formulae in N

in N3

=IFERROR(INDEX(W$5:W$16,COUNT($A$5:$A$32)),"")

Change ranges for N4 to N8

And do you want I and K completed via formulae rather than macro?

in I2

=INDEX($EI$5:$EZ$16,COUNT($A$5:$A$32),ROWS($1:1))

Copy down

in K2

=INDEX($FA$5:$FS$16,COUNT($A$5:$A$32),ROWS($1:1))

Copy down

See sheet "Formulae" which has the VBA code changed to remove update of I and K