+ Reply to Thread
Results 1 to 18 of 18

Visual Basic - Not allowing cell entry

Hybrid View

  1. #1
    Registered User
    Join Date
    07-08-2008
    Location
    Here
    Posts
    15
    Quote Originally Posted by shg View Post
    What column is "Fringe Benefit Type" in? What column is "Hire Season"?

    You're aware that if you use code to modify a worksheet, the Undo stack is flushed, and that's OK?

    Fringe Benefit is Starts at cell P23 thru P62. Hire Season is R23-R62.

    That is fine,
    Thanks!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Right-click on the sheet tab and paste this in the window that opens:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r       As Range
        Dim cell    As Range
    
        Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R")))
        If r Is Nothing Then Exit Sub
    
        For Each cell In r
            With cell
                If .Column = Columns("P").Column Then
                    If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then
                        With Cells(.Row, "R")
                            .ClearContents
                            .Select
                            MsgBox "Must be blank.", vbOKOnly
                        End With
                    End If
    
                Else    ' it's in column R
                    If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then
                        .ClearContents
                        .Select
                        MsgBox "Must be blank.", vbOKOnly
                    End If
                End If
            End With
        Next cell
    End Sub

  3. #3
    Registered User
    Join Date
    07-08-2008
    Location
    Here
    Posts
    15
    Its not working, when I insert it into the field, it tells me "Ambugious name detected:Worksheet_Change"

    Your part is towards the end....
    This is how it looks:

    Private Sub Worksheet_Activate()
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'this is all for validation of user input on the details sheet
    
    'make sure a date has been input in E11 and E12
    If Target.Cells(1, 1).Address(0, 0) = "E11" Then
        If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
            MsgBox "You must input a date!", vbExclamation, "DATE REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        End If
    End If
    If Target.Cells(1, 1).Address(0, 0) = "E12" Then
        If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then
            MsgBox "You must input a valid date!", vbExclamation, "DATE REQUIRED"
            Target.Cells(1, 1) = ""
            Exit Sub
        End If
    End If
    
    'make sure enddate>=startdate
    If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
        If Sheets("Salary Detail").Range("enddate") < Sheets("Salary Detail").Range("startdate") Then
            MsgBox "We've heard of short projects before, but this one really takes the cake!", vbExclamation, "END DATE BEFORE START DATE"
            Sheets("Salary Detail").Range("enddate") = ""
            Exit Sub
        End If
    End If
        
    'make sure enddate<=startdate + 366
    If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then
        If Sheets("Salary Detail").Range("enddate") > Sheets("Salary Detail").Range("startdate") + 366 Then
            MsgBox "This project has a very long initial year!", vbExclamation, "END DATE BEFORE START DATE"
            Sheets("Salary Detail").Range("enddate") = ""
            Exit Sub
        End If
    End If
    
    'make sure FTE's in C23:C62  and the levels of effort in H23:H62 are in the range 0-1
    If Target.Column = 3 Or Target.Column = 8 Then
        If Target.Row >= 23 And Target.Row <= 62 Then
            If Not IsNumeric(Target.Cells(1, 1)) Then
                MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
                Target.Cells(1, 1) = ""
                Exit Sub
            ElseIf Target.Cells(1, 1) < 0 Or Target.Cells(1, 1) > 1 Then
                MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED"
                Target.Cells(1, 1) = ""
                Exit Sub
            End If
        End If
    End If
    
    'make sure the salaries in E23:E62 are positive numbers
    If Target.Column = 5 Then
        If Target.Row >= 23 And Target.Row <= 62 Then
            If Not IsNumeric(Target.Cells(1, 1)) Then
                MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
                Target.Cells(1, 1) = ""
                Exit Sub
            ElseIf Target.Cells(1, 1) < 0 Then
                MsgBox "You're getting paid that badly!?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
                Target.Cells(1, 1) = ""
                Exit Sub
            End If
        End If
    End If
    
    'make sure the fringe benefit types in P23:P62 are appropriate
    If Target.Column = 16 Then
        If Target.Row >= 23 And Target.Row <= 62 Then
            If Target.Cells(1, 1) <> "" And Target.Cells(1, 1) <> "F" And Target.Cells(1, 1) <> "U" And Target.Cells(1, 1) <> "A" And Target.Cells(1, 1) <> "P" And Target.Cells(1, 1) <> "T" And Target.Cells(1, 1) <> "R" And Target.Cells(1, 1) <> "S" And Target.Cells(1, 1) <> "W" And Target.Cells(1, 1) <> "f" And Target.Cells(1, 1) <> "a" And Target.Cells(1, 1) <> "p" And Target.Cells(1, 1) <> "t" And Target.Cells(1, 1) <> "r" And Target.Cells(1, 1) <> "u" And Target.Cells(1, 1) <> "s" And Target.Cells(1, 1) <> "w" Then
                MsgBox "You must enter a fringe benefit code from the list above!", vbExclamation, "'F','A','P','T','R','S', OR 'W' REQUIRED"
                Target.Cells(1, 1) = ""
                Exit Sub
            End If
        End If
    End If
     
        
        If Target.Row >= 23 And Target.Row <= 62 Then
    'make sure the project duration in L10 is a positive number
    If Target.Column = 12 And Target.Row = 10 Then
            If Not IsNumeric(Target.Cells(1, 1)) Then
                MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
                Target.Cells(1, 1) = ""
                Exit Sub
            ElseIf Target.Cells(1, 1) < 0 Then
                MsgBox "Are you sure the project is that short?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED"
                Target.Cells(1, 1) = ""
                Exit Sub
            End If
    End If
    End If
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r       As Range
        Dim cell    As Range
    
        Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R")))
        If r Is Nothing Then Exit Sub
    
        For Each cell In r
            With cell
                If .Column = Columns("P").Column Then
                    If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then
                        With Cells(.Row, "R")
                            .ClearContents
                            .Select
                            MsgBox "Must be blank.", vbOKOnly
                        End With
                    End If
    
                Else    ' it's in column R
                    If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then
                        .ClearContents
                        .Select
                        MsgBox "Must be blank.", vbOKOnly
                    End If
                End If
            End With
        Next cell
    End Sub
    Last edited by shg; 08-20-2008 at 05:56 PM. Reason: deleted spurious quote

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    1. I meant to say this before: Please quit quoting whole posts. It just clutters the forum.

    2. Please read the Forum Rules and then edit your post to wrap your code with Code Tags.

    3. You can only have one Worksheet_Change event.

+ 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. Dynamic Scrolling based on partial cell entry
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-10-2011, 05:48 PM
  2. Message box based on user entry in cell
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2008, 04:41 PM
  3. read cell content and use with Hyperlinks.Add Cell
    By apoc [t.i.m.] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-04-2007, 10:02 AM
  4. excel copy and paste visual basic
    By vakeeper in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2007, 05:13 AM
  5. Changing text to Bold from another cell entry
    By TPD in forum Excel General
    Replies: 2
    Last Post: 09-19-2006, 02:31 PM

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