Sheet code not working for message pop-up

[mikejvir]

Hello All,

I must be doing something stupid because I can not determine why this code is not working. There was another post last month (Pop up box based on certain values within cells) which is very similar to my needs. I need to have a popup message box to inform the user the value has to be within a certain range. Here is my code based on the user JoeMo.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R1 As Range
Dim xNum As Long
Dim x2 As Long

On Error GoTo EXITSUB
Set R1 = Intersect(Range("B1:B3"), Target)   'Assume that if B3 is changed R1 is a valid range.

If Not R1 Is Nothing Then
  Me.Calculate

  xNum = Cells(B3).Value
      x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", "Gate Voltage Test")
 
  Select Case xNum
  Case Is > -1.642
    x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", "Gate Voltage Test")
  Case Is < -6
    x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", "Gate Voltage Test")
  End Select
End If
EXITSUB:
End Sub

Here is the dat in the cells. I need to check the value of cell B3

	A	B	C
1	Select Device (drop down)	A3AR1
2	Measured Gate Voltage at J21	-3.2​	Volts
3	Measured Gate Voltage at Pad B1	-3​	Volts
4

I did the the steps required.
1) on the worksheet tab name, I right clicked and choose "View Code".
2) I entered the above code.
3) closed and saved the workbook with a .xlsm (using Excel 2016).
4) reopened in two different location. One that asked for the macro to be enabled, the other a trusted folder.

If I type a value outside the range (less than -6 or greater than -1.642) nothing happens.

I am out of ideas?

One last thing. I assume that if I copy the worksheet, then the code will also following the worksheet.

Thanks in advance.

Mike Virostko

 

[JLGWhiz]

th

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R1 As Range
Dim xNum As Long
Dim x2 As Variant
On Error GoTo EXITSUB
Set R1 = Intersect(Range("B1:B3"), Target)   'Assume that if B3 is changed R1 is a valid range.
    If Not R1 Is Nothing Then
        Me.Calculate
        xNum = Cells(3, 2).Value
       x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", , "Gate Voltage Test") 
       Select Case xNum
           Case Is > -1.642
             x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", "Gate Voltage Test")
          Case Is < -6
             x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", "Gate Voltage Test")
         End Select
     End If
EXITSUB:
End Sub

ere were two or three syntax error which I fixed. I don't understant the your objective on a couple of others, but try the code and post back with comments.

 

[mikejvir]

th

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R1 As Range
Dim xNum As Long
Dim x2 As Variant
On Error GoTo EXITSUB
Set R1 = Intersect(Range("B1:B3"), Target)   'Assume that if B3 is changed R1 is a valid range.
    If Not R1 Is Nothing Then
        Me.Calculate
        xNum = Cells(3, 2).Value
       x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", , "Gate Voltage Test")
       Select Case xNum
           Case Is > -1.642
             x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", "Gate Voltage Test")
          Case Is < -6
             x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", "Gate Voltage Test")
         End Select
     End If
EXITSUB:
End Sub

ere were two or three syntax error which I fixed. I don't understant the your objective on a couple of others, but try the code and post back with comments.

Hi JL,

Thanks for the reply. I changed X2 from Long to Variant.
Also the cell reference for Cells(B3) to Cells(3,2).

I also removed XNum. either change showed a popup window when I enter -1 or -7.
The message box just after xNum was just for troubleshooting.

I did find the issue. I had to add a button to let the message box stay viewable. Here is the new code

Set R1 = Intersect(Range("B1:B3"), Target)
If Not R1 Is Nothing Then
  Me.Calculate

  'xNum = Cells(3, 2).Value
 
  Select Case Cells(3, 2).Value
  Case Is > -1.642
    x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", _
    vbOKOnly, "Gate Voltage Test")
  Case Is < -6
    x2 = MsgBox("The value must be between -6.0 Volts and -1.642 Volts, please enter again! ", _
    vbOKOnly, "Gate Voltage Test")
  End Select
End If
EXITSUB:
End Sub

Again thanks for helping me find my silly mistake.

Mike Virostko

 

[JLGWhiz]

You're welcome,
regards, JLG