+ Reply to Thread
Results 1 to 12 of 12

Form Control Buttons

  1. #1
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    70

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,898

    Re: Form Control Buttons

    The easiest and most flexible way to do this is to click on the cells with the numbers, rather than adding buttons.

    "The number clicked needs to be used in another cell." That's a little vague so I didn't know what to do with the number. In this example the clicked number goes into E5. Modify code to suit.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Form Control Buttons

    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

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,898

    Re: Form Control Buttons

    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.

  5. #5
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Form Control Buttons

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Form Control Buttons

    Sorry I meant to say

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

    Old age getting to me

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,445

    Re: Form Control Buttons

    Please Login or Register  to view this content.
    Click on number in columns D & E
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Form Control Buttons

    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
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,445

    Re: Form Control Buttons

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Form Control Buttons

    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
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,445

    Re: Form Control Buttons

    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.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,445

    Re: Form Control Buttons

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Jittery Form Control Option Buttons
    By BDD2015 in forum Excel General
    Replies: 1
    Last Post: 02-03-2017, 02:36 PM
  2. Hide a Chart with Form Control Buttons
    By shamsul in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-10-2016, 08:16 AM
  3. [SOLVED] Command Buttons Form Control or ActiveX
    By Epscan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2014, 01:41 PM
  4. Form control buttons creep
    By thecdnmole in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2014, 12:59 PM
  5. [SOLVED] How do i replicate Form Control Buttons in a spreadsheet?
    By msalib888 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-11-2014, 11:09 PM
  6. Adding Form Control Buttons
    By wildlifegis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-15-2012, 01:55 AM
  7. How to insert NICE Form Control Buttons
    By sunilmulay in forum Excel General
    Replies: 3
    Last Post: 10-08-2009, 11:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1