ExcelBanter - Input boxes in Excel

Candee

You can use the selection change event. Here's an example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Hrs As Double

If Target.Column = 8 Then
Hrs = Application.InputBox("Enter # of hours", , , , , , , 1)
If Hrs < 0 Then
Target.Formula = "=" & Hrs & "*15"
'Target.Value = Hrs * 15
End If
End If

End Sub

Right click on the sheet's tab and choose View Code. Paste this sub into
the resulting code pane. Note that the sub checks for Hrs < 0. If the
user Cancels the inputbox, the result will be zero, so I ignored that. If
you want to be able to include zeros, remove that If..End If.

Also note that there are two lines in the If block. The one that's not
commented out puts a formula in the cell so you can go back and see what
hours were entered. If you don't care about that, use the second line which
does the math in VBA and just writes the value to the cell.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Candee" wrote in message
...
Good morning all, I'm hoping someone can help me out...

I have a existing spreadsheet spreadsheet in Excel where column H is
cost. I have a constant rate of $15.00. Can I use an input box to
prompt the user to enter a value (hours), and then multiply that value
by the rate and enter the cost into the spreadsheet in the appropriate
cell in column H? This speadsheet will end up having close to 500
records in it by the end of the month, and I would need this to apply
to each cell in H.

Thanks in advance,

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/