Sheet code not working for message pop-up

mikejvir

Board Regular
Joined
Jan 3, 2008
Messages
82
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.


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

ABC
1Select Device (drop down)A3AR1
2Measured Gate Voltage at J21
-3.2​
Volts
3Measured 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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
th
VBA Code:
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

Board Regular
Joined
Jan 3, 2008
Messages
82
th
VBA Code:
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

VBA 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
 
Solution

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You're welcome,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,123,456
Messages
5,601,772
Members
414,472
Latest member
Chris_1990

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