Add a dialogue box

[nkitchen31]

If a cell has a value in it, I would like have a box come up and ask for a value (number). There are a possibility of 15 cells. And I want a different cell to add up all the times that I put in the dialog box.

Thanks in advance!

[nkitchen31]

Like if any of the cells between F5:F20 have an "x" then it'll ask how much time. And it'll keep track of all the times entered and add them up in A5.

[mikerickson]

First put =SUM(F5:F20) in A5


Then this Sub could be run to get your values.

Sub Test()
    Dim oneCell as Range

    For Each oneCell in Range("F5:F20")
        If LCase(oneCell.Value) = "x" Then
            oneCell.Value = Application.InputBox("Enter time")

            If oneCell.Value = False Then
                Rem Cancel pressed
                oneCell.Value = "x"
                Exit Sub
            End If
        End If
    Next oneCell

End Sub

[nkitchen31]

I did that in the Visual Basic editor... and nothing happens when I put an "x" in those columns...

[nkitchen31]

Here is a screenshot. Thanks for your help.

Screen Shot 2017-01-16 at 4.22.00 AM.png

[nkitchen31]

Perhaps there is a different way to do this? B4 needs to be added up automatically, all the times that are entered in the "Enter Credit" if there is an "x" in the I column.

[Logit]

Did you add a CommandButton attached to the macro "Sub Test() so when clicked it will fire the macro ?

[nkitchen31]

Negative ... I haven't done VB since high school bak in the late 90's ... I don't remember much haha. But is there a different way to do this besides VB and having to add in extra steps? Thank you.

[Logit]

You could place the macro in the Sheet Change event.

Both of these look the same but they are not. Closely look at the Sub name at the top of each. This goes in the VBE editor window for the affected worksheet.
In your pic you have Sheet3(Feb) highlighted. If you click on that, as you did to insert the first version of the macro, delete the existing macro, then paste
one of these in its place.

You only need to paste one of the macros. I would start with the second one.


# 1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oneCell As Range
    
        For Each oneCell In Range("F5:F20")
            If LCase(oneCell.Value) = "x" Then
                oneCell.Value = Application.InputBox("Enter time")
    
                If oneCell.Value = False Then
                    Rem Cancel pressed
                    oneCell.Value = "x"
                    Exit Sub
                End If
            End If
        Next oneCell
End Sub

# 2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oneCell As Range
    
        For Each oneCell In Range("F5:F20")
            If LCase(oneCell.Value) = "x" Then
                oneCell.Value = Application.InputBox("Enter time")
    
                If oneCell.Value = False Then
                    Rem Cancel pressed
                    oneCell.Value = "x"
                    Exit Sub
                End If
            End If
        Next oneCell
End Sub

[Logit]

I've tried the two new macros. Neither one works satisfactorily.

Try this :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F5:F20")).Value = "x" Then
        MsgBox "Enter Time Value Only ! ", vbOKOnly & vbCritical, "Wrong Data Entry"
        Intersect(Target, Range("F5:F20")).Select
End If

End Sub

[nkitchen31]

I will check it when I get to my computer. Thank you.

But the value of the cells should still be "x", but the sum of B5 should be all the values that were entered into the "Enter Credit" msgBox ... make sense?

Thanks again!

[Logit]

.
Add this formula to the Formula Bar for cell B5:

=SUM(F5:F20)

Change the macro code to this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

''<---- Two new lines of code --->
If Intersect(Target, Range("F5:F20")) Is Nothing Then Exit Sub
If Intersect(Target, Range("F5:F20")) = "" Then Exit Sub
''<--- Two new lines of code --->

If Intersect(Target, Range("F5:F20")).Value = "x" Then
        MsgBox "Enter Time Value Only ! ", vbOKOnly & vbCritical, "Wrong Data Entry"
        Intersect(Target, Range("F5:F20")).Select
End If

End Sub