Input box invoked by cell change

[StevenDunn]

Hello Forum..
Trying to write some simple code to populate a cell based in the value entered using the inputbox feature
Ferinstance if cell a1 = yes then an input box invokes and a number is entered, the cell b1 will then be the value entered in the input box.
Should be simple really, can anyone help

 

[Fluff]

Hi & welcome
Is this the sort of thing your looking for?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

    If Target.Address = "$A$1" And Target.Value = "Yes" Then
        Range("B1") = InputBox("Please enter a number")
    End If

Application.EnableEvents = True

End Sub

 

[L. Howard]

Hi StevenDunn,

Try something like this?

Regards,
Howard

Option Explicit

Sub InPutBox_MessageBox_Response()

Dim Answer As String

If Cells(1, 1) = "Yes" Then
   Answer = InputBox("A-One Equal Yes")
    If StrPtr(Answer) = 0 Then
        Exit Sub
      ElseIf Len(Answer) = 0 Then
        MsgBox "Nothing entered in the InputBox!"
      Else
        Cells(1, 2) = Answer
    End If
End If
 
End Sub

 

[StevenDunn]

Hi There
Could not get it to work im afraid....
I have a time sheet that i am trying to build and the working hours have to be entered weather the have been worked or not. ie if on holiday you enter your contracted hours, and i am trying to make it idiot proof by locking down cells and forcing users to put the data where i want them to put it, if you get my meaning

I thought this wold be simple (not being a coder) i did this:

Private Sub Worksheet_SelectionChange (ByVal Target as range)
Dim Answer As String

If range("C3") = "Day Off" Then
Answer = InputBox(Please Enter Your Total Hours")
If Answer = "" Then Exit Sub
Range("i3") = Answer
End If
End If
End Sub

This works upto a point but it seems to loop once invoked so just clicking on a cell will invoke the inputbox and it never populates cell I3?

Hope that makes sense?

Hi StevenDunn,

Try something like this?

Regards,
Howard

Option Explicit

Sub InPutBox_MessageBox_Response()

Dim Answer As String

If Cells(1, 1) = "Yes" Then
   Answer = InputBox("A-One Equal Yes")
    If StrPtr(Answer) = 0 Then
        Exit Sub
      ElseIf Len(Answer) = 0 Then
        MsgBox "Nothing entered in the InputBox!"
      Else
        Cells(1, 2) = Answer
    End If
End If
 
End Sub

 

[StevenDunn]

Hi There
Could not get it to work im afraid....
I have a time sheet that i am trying to build and the working hours have to be entered weather the have been worked or not. ie if on holiday you enter your contracted hours, and i am trying to make it idiot proof by locking down cells and forcing users to put the data where i want them to put it, if you get my meaning

I thought this wold be simple (not being a coder) i did this:

Private Sub Worksheet_SelectionChange (ByVal Target as range)
Dim Answer As String

If range("C3") = "Day Off" Then
Answer = InputBox(Please Enter Your Total Hours")
If Answer = "" Then Exit Sub
Range("i3") = Answer
End If
End If
End Sub

This works upto a point but it seems to loop once invoked so just clicking on a cell will invoke the inputbox and it never populates cell I3?

Hope that makes sense?

 

[Fluff]

Hiya

it seems to loop once invoked

Did you try my code? as it should eliminate this problem

 

[L. Howard]

You were missing a " in the code and had an extra End If.

Also, as the code is, anytime you SELECT any cell on the sheet, if C3 has "Day Off" in it, then you will get the InputBox. I would think you don't want that, but I'm not sure how you want the entire sheet to function.

Howard

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim Answer As String

 If Range("C3") = "Day Off" Then
 Answer = InputBox([COLOR="#FF0000"]"[/COLOR]Please Enter Your Total Hours")
 If Answer = "" Then Exit Sub
 Range("i3") = Answer
 End If
 
 End Sub

 

[StevenDunn]

Hello there
I basicaly have a time sheet that I need to make idiot proof. column C is a list of reasons for not being at work eg, Day Off, Holiday, Sick, etc...
But what ever they select they still have to enter their contracted hours for that day in column I. It may seem simple to just enter the hours into column I but believe me they still get it wrong.

So as they enter data going down the column for each day of the week I need an input box to prompt them for their hours for that day and put the data in the corresponding row in column I...

does that make sense?
Regards
Steve

Steve

 

[StevenDunn]

Hi Fluff
Yes your code works thanks
I will try and adapt it for my workbook and let you know how I get on
Cheers
Steve

 

[Fluff]

Hiya
Based on what you've said above. This will fire when any change is made in Column 3.

Private Sub Worksheet_Change(ByVal Target As Range)
' StevenDunn
    Dim Hrs As String
    Dim Rw As Long

Application.EnableEvents = False

On Error GoTo Xit
    If Target.Column = 3 Then
        Rw = Target.Row
        Hrs = InputBox("Please Enter Your Total Hours")
        If Hrs = "" Then
            MsgBox ("You have not entered anything")
            GoTo Xit
        End If
        Range("I" & Rw) = Val(Hrs)
    End If

Application.EnableEvents = True
Exit Sub

Xit:
Application.EnableEvents = True

End Sub