Below is code supplied by JBeaucaire and it works exactly as I requested. However, I need this to happen for multiple cells.
The cells that I need to monitor are B10, B12, B14.... through B96 and F, J, N, R columns for the same rows. The input values will be stored in cells AM10, AM12, AM14 .... through 96 for the B column results. Then for F, J, N, R will be AQ, AU, AY and BC respectively.
Additionally, when B10 has an input, and the user input box displays for the number of hours, there is a cancel button available, but it does not work. How can I exit the loop and clear contents of B10 if the "Cancel" button is clicked in the dialogue box.
I know I'm asking alot. thanks in advance.
Option Explicit
Private Sub Worksheet_Activate()
If Range("B10") = "" And Range("AM10") <> "" Then Range("AM10").ClearContents
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyVal As Single
If Not Intersect(Target, Range("B10")) Is Nothing Then
If Range("B10") <> "" Then
Do
MyVal = Application.InputBox("How many hours for this task: " & Range("B10").Value, "Hours", Type:=1)
If MyVal > 0 Then
Range("AM10").Value = MyVal
Exit Do
End If
Loop
Else
Range("AM10").ClearContents
End If
End If
End Sub
Bookmarks