WorkSheet Selecteion Chage Event to prompt Input Box

[Corey]

I am trying to set a few cells within a sheet to trigger an InputBox.

If one of the set cells value is = 'Sheet5.range("B46") then the
..Offset(0,1).value = the InputBox value.

I am having trouble getting the InputBox to show when the cell value = the
Sheet5 value???

Corey.....

 

[Peter T]

In your Sheet5 module (rt-click sheet tab, View code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Const sAddr As String = "$B$46"

Set rCell = Range(sAddr)

On Error GoTo errH

If Not Intersect(Target, rCell) Is Nothing Then
reTry:
res = Application.InputBox("Enter value for cell C46", _
rCell.Address(0, 0) & " has just
changed")
If CStr(res) = CStr(False) Then

If MsgBox("you cancelled, try again ?", vbYesNo) = vbYes Then
Err.Raise 22222
End If

Else
rCell.Offset(, 1).Value = res
End If
End If

cleanUp:
' reset any changed application settings
Exit Sub
errH:
If Err.Number = 22222 Then
Resume reTry
Else
Resume cleanUp
End If
End Sub

Regards,
Peter T