+ Reply to Thread
Results 1 to 3 of 3

how do I Use Greater than ("Cell") to bring up a msgbox

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-31-2007
    MS-Off Ver
    2010
    Posts
    130

    how do I Use Greater than ("Cell") to bring up a msgbox

    I am trying to bring up a msgbox if the number they enter into cell ("F3") is greater than 99999.
    also if this is true I want it to select ("F3") again so they can try again.

    I have the following code and I thought it should work but it doesn't.
    Any thoughts?

    Private Sub Worksheet_Change2()
    Dim JobNo As Integer, result As String
     JobNo = Range("F3").Value
    
    If JobNo > 99999 Then MsgBox "Numbers may not exceed 99999"
    
     Range("F3").Select
    End If
        End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,328

    Re: how do I Use Greater than ("Cell") to bring up a msgbox

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const ciMax As Long = 99999
    
    Dim rInterest As Range
    Set rInterest = Range("F3")
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, rInterest) Is Nothing Then Exit Sub
    
    With Target
        If .Value > ciMax Then
            MsgBox "Numbers may not exceed 99999"
            .Select
        End If
    End With
    
    End Sub

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: how do I Use Greater than ("Cell") to bring up a msgbox

    or

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$3" And Target.Value > 99999 Then
        MsgBox "Numbers may not exceed 99999"
        Application.Goto Reference:=Range("F3"), scroll:=False
    End If
    End Sub
    Kind regards
    Leo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Bring "This Procedure Name" to the A1 Cell
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2015, 01:06 PM
  2. [SOLVED] If Not ActiveSheet.Range("A1").Value Like "apple" Then MsgBox "Error"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2014, 02:16 PM
  3. [SOLVED] IF Formula not working - need to return "NO" if cell is 1% greater or "YES" if less 1%
    By maryren in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2013, 11:34 AM
  4. Replies: 2
    Last Post: 06-25-2013, 12:43 PM
  5. [SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"
    By chriswhite1982 in forum Excel General
    Replies: 3
    Last Post: 06-16-2013, 12:30 AM
  6. countif + "greater than" "cell value" 2
    By vonPalm in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 09-21-2010, 02:42 AM
  7. Date "condition" to bring up a value in a specific cell
    By sunking in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2007, 05:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1