I am currently working on an excel sheet where I am trying to basically have a userform appear once my If...Then Statement is true. My "If, Then" statement bascially consists of the data point being above the maximum or below the minimum.
Once the data point meets those parameters, that is when I want to call the Userform. I am trying to have the Userform to display an entry for a number and once entered, it will make sure that it is not above the maximum or below the minimum. Then if it is not above the maximum or below the minimum, I would want to submit that number that was entered into the cell where the user inputted the data. I know it may seem simple but I am fairly new to VBA and I am trying my best to learn it. So far with the UserForm I got up to designing it. So I just entered text and and entry box. That is about it. Thank you for the Help!
Also here is my code that I have for the "If, Then" Statement. I orginially had it where it sends an email. So after the "Then" term. I made the macro send an email to the owner of that excel sheet. I am trying to use this code as well to make the UserForm:
Option Explicit
Public Sub OutofControl()
Dim lRow As Long
Dim lstRow As Long
Dim data As Variant
Dim ul As Variant
Dim ll As Variant
Dim wb As Workbook
Dim ws As Worksheet
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
Set ws = Sheets(2)
ws.Select
lstRow = WorksheetFunction.Max(1, ws.Cells(Rows.Count, "R").End(xlUp).Row)
For lRow = 1 To lstRow
data = Cells(lRow, "E").Value
ul = Range("K26")
ll = Range("K25")
If data > ul Or data < ll Then
If IsNumeric(data) = True And data Like "" = False Then
' Code for Sending Email after Then
Now Here is my code on the Sheet with the Selection Change After I did some research. However I am getting an infinite loop and the Input box is not entering the data I type. Also the "Run OutofControl" line in the code refers to another macro that sends out an automatic email.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lRow As Long
Dim lstRow As Long
Dim KeyCells As Range
Dim data As Variant
Dim ul As Variant
Dim ll As Variant
Dim ws As Worksheet
Set ws = Sheets(2)
ws.Select
lstRow = WorksheetFunction.Max(1, ws.Cells(Rows.Count, "R").End(xlUp).Row)
For lRow = 1 To lstRow
data = Cells(lRow, "E").Value
ul = Range("K26")
ll = Range("K25")
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("E:E")
If (data > ul) Or (data < ll) Then
Application.EnableEvents = False
If IsNumeric(data) = True And data Like "" = False Then
Run ("OutofControl") 'Macro
Application.EnableEvents = False
' Display a message when one of the designated cells has been
' changed.
Application.EnableEvents = True
On Error GoTo 0
MsgBox ("There was an Out of Control Point at " & Cells(lRow, "C").Value)
Teststr = InputBox("Enter your Control data:")
End If
End If
Next lRow
End Sub
OnChangeevent.