+ Reply to Thread
Results 1 to 13 of 13

Simple User Input Button

  1. #1
    Registered User
    Join Date
    01-02-2012
    Location
    Dorset, England
    MS-Off Ver
    Excel 2007
    Posts
    22

    Simple User Input Button

    Hi all,

    I'm new to VBA and activex controls in excel. I've figured out how to create macros and assign then to buttons but now I need to create a simple user input on my spreadsheet.

    I have cells A1 to T1 containing the numbers 1 to 20. These numbers represent years. On a seperate worksheet I would like the user to be able to enter a cost and select a year, then click a button that inputs that cost into the cell directly underneath the year they selected. So it looks a little something like this:

    Cost: *The user enters the cost here*

    Year: *The user selects a year from the drop down list (1-20)*

    ADD - *the user clicks the add button and the cost is inputted into a cell directly below the year they selected (on the other worksheet) - and maybe some sort of confirmation to state it has worked*

    Any help would be greatly appreciated. Many thanks.

    Adam
    Last edited by HeebieGeebie; 01-16-2012 at 11:26 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Simple User Input Button

    Please Upload a file showing your sheets
    Regards,
    Khaled Elshaer
    www.BIMcentre.com

    Remember To Do the Following....
    1. Thank those who have helped you by clicking the Star below their post.
    2. Mark your post SOLVED if it has been answered satisfactorily:
    • Select Thread Tools (on top of your 1st post)
    • Select Mark this thread as Solved

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple User Input Button

    Hi Adam,

    Why do you need VBA for this when there's perfectly good standard Excel functionality?

    1. Name the range A1:T1 on sheet 1 'years'.
    2. Name the Cost Cell on sheet 2 'Cost'
    3. Name the drop down cell on sheet 2 'Year', I assume that you're using Data Validation and have used the 'List' option and entered the formula =years - see 1 above.
    4. In A2 on sheet 1 enter the formula =IF(A1=Year,Cost,"") and copy across to T2

    Regards

  4. #4
    Registered User
    Join Date
    01-02-2012
    Location
    Dorset, England
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Simple User Input Button

    Thanks to both of you for your speedy responses It's very much appreciated. Richard, I have just been playing around with what you suggested but unfortunately that doesn't quite do what I'm after. I'll try to explain myself a bit better. I need the user to enter several costs, for example 20,000 in year 3, 30,000 in year 6 and 40,000 in year 9. So they must enter a cost, click ADD, it adds the cost and they can then add another cost. Is there a way I could do this using the method you have suggested?

    How can I upload an example to show you?

    Thanks

    Adam

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple User Input Button

    Hi Adam,

    Does the attached help. It will respond to changes to the year cell on sheet 2 and update row 2 on sheet1.

    However I have to ask the obvious question, why not just have a vertical list of year numbers and ask the user to complete the cost in the adjacent cell.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-02-2012
    Location
    Dorset, England
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Simple User Input Button

    Thanks for your help Richard. The example you've created is very close to what I'm after. I've attached an example of my own below.

    The reason I don't want the user to Input the costs directly themselves is because the spreadsheet is a little more complicated than I have described. I have a front sheet with a lot of different input variables. These variables apply across a further 10 sheets in the workbook. I would like for the user to be able to input this Cost data on the front sheet along with all of the other variables. The costs will then be applied to certain sheets in the workbook.
    Costs.xlsm
    Ideally, I would like the user to have some sort of confirmation that the data they have entered has been applied.

    Many thanks for all your help so far,

    Adam

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple User Input Button

    Hi Adam,

    See the attached,

    Regards
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-02-2012
    Location
    Dorset, England
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Simple User Input Button

    Richard, that is exactly what I'm after. I have left feedback. If you have a paypal account I can send you 20 for your troubles. PM me your email.
    I'm sorry to be a pain but I can't quite figure out how you've managed to make this work. The only code I can see in relation to the 'Add' button is this:

    'Private Sub CommandButton1_Click()

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub'

    Could you possibly explain to me what you've done?

    Thanks ever so much,

    Adam

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple User Input Button

    Hello HG,

    That is incredibly generous and kind of you but we freely offer our services here.

    If however you would like to make a donation to charity then that would also be appreciated.
    A local cancer charity that we support can be found here:http://www.stroccos.org.uk/Fundraising/Make+a+donation.

    As to your question, the button is attached to a Module level procedure which is simply:

    Please Login or Register  to view this content.
    Double click on Module1 in the VBE Project window to see it.

    Regards

  10. #10
    Registered User
    Join Date
    01-02-2012
    Location
    Dorset, England
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Simple User Input Button

    Many thanks Richard. I will send a donation. Now i'll try to implement this is into my actual spreadsheet!

  11. #11
    Registered User
    Join Date
    01-02-2012
    Location
    Dorset, England
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Simple User Input Button

    I've implented the button into my worksheet although another issue has arisen.

    I'm trying to put more than one of these buttons into one worksheet. The problem lies where the 'Cost' and 'Year' are printed on Sheet 1 (See Attached)

    Costing.xlsm

    I imagine something needs to be changed here...
    Please Login or Register  to view this content.
    ... to make it function correctly.

    Sorry to bother you again but if this can be resolved I'll be set to go!

    Adam

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple User Input Button

    Hi Adam,

    You'll need to change the scenario 1 code as follows. The reason is that the .Rows.Count instruction finds the very last row on the sheet and the .end(xlup) then only finds the next available row in scenario 2 since you've stacked this underneath the scenario 1. Therefore you need to start in row 13 for scenario 1.


    Please Login or Register  to view this content.
    Regards

  13. #13
    Registered User
    Join Date
    01-02-2012
    Location
    Dorset, England
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: Simple User Input Button

    Thanks again Richard, i understand now. I need to learn what the various different commands are.

    I think that's this one solved.

    Adam.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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