Simple User Input Button

[HeebieGeebie]

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

[Kelshaer]

Please Upload a file showing your sheets

[Richard Buttrey]

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

[HeebieGeebie]

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

[Richard Buttrey]

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.

[HeebieGeebie]

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

[Richard Buttrey]

Hi Adam,

See the attached,

Regards

[HeebieGeebie]

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

[Richard Buttrey]

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:

Sub AddCost()
    Dim r As Range

    Set r = Sheet2.Range("Years").Find(Range("Year").Value, LookIn:=xlValues)
    r.Cells(2, 1) = Range("Cost")
    With Sheet1
        .Range("A" & Rows.Count).End(xlUp).Cells(2, 1) = Range("Cost")
        .Range("B" & Rows.Count).End(xlUp).Cells(2, 1) = Range("Year")
    End With
End Sub


Regards

Double click on Module1 in the VBE Project window to see it.

Regards

[HeebieGeebie]

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

[HeebieGeebie]

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...

.Range("E" & Rows.Count).End(xlUp).Cells(2, 1) = Range("Cost1")
.Range("F" & Rows.Count).End(xlUp).Cells(2, 1) = Range("Year1")

... to make it function correctly.

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

Adam

[Richard Buttrey]

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.

Sub AddCost()
    Dim r As Range

    Set r = Sheet2.Range("YearsScenario1").Find(Range("Year1").Value, LookIn:=xlValues)
    r.Cells(10, 1) = Range("Cost1")
    With Sheet1
        .Range("E13").End(xlUp).Cells(2, 1) = Range("Cost1")
        .Range("F13").End(xlUp).Cells(2, 1) = Range("Year1")
    End With
End Sub

Regards

[HeebieGeebie]

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

I think that's this one solved.

Adam.