Input box invoked by cell change

StevenDunn

New Member
Joined
Sep 29, 2014
Messages
5
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:confused:
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome
Is this the sort of thing your looking for?
Code:
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi StevenDunn,

Try something like this?

Regards,
Howard

Code:
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

New Member
Joined
Sep 29, 2014
Messages
5
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

Code:
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

New Member
Joined
Sep 29, 2014
Messages
5

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Hiya
it seems to loop once invoked
Did you try my code? as it should eliminate this problem
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514

ADVERTISEMENT

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


Code:
 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

New Member
Joined
Sep 29, 2014
Messages
5
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

New Member
Joined
Sep 29, 2014
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Hiya
Based on what you've said above. This will fire when any change is made in Column 3.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,221
Messages
5,594,897
Members
413,950
Latest member
solve22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top