Results 1 to 11 of 11

Select case on range name

Threaded View

  1. #1
    Forum Contributor mcinnes01's Avatar
    Join Date
    05-25-2010
    Location
    Manchester
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    449

    Select case on range name

    Hi

    I think I am being a bit daft probably because I've been staring at vba for too long today...
    But I cant get my select case to work. basicially it just validates the data. When I run it nothing errors, but nothing happens either?

    Sub VALIDATEINPUT()
    Dim RngCell As Range
    
    For Each RngCell In Range("ingrp,RES,RESO,REQ,REQO,REQD").Cells
        Select Case RngCell.Cells.Name
                
            Case 1
                RngCell.Name = "EMP"
                    If Range("EMP") = "" Then
                        MsgBox ("You must enter an employee number at the top")
                    End If
                    
            Case 2
                RngCell.Name = "RES"
                    If Range("RES") = "" Then
                        MsgBox ("You must select a reason for the change request")
                    Else
                            If Range("RES") = "Other - Please specify" Then
                                If Range("RESO") = "" Then
                                    MsgBox ("You must enter a reason in the 'other reason' box")
                                End If
                            End If
                    End If
                    
            Case 3
                RngCell.Name = "REQ"
                    If Range("REQ") = "" Then
                        MsgBox ("You must select the request for the change")
                    Else
                            If Range("REQ") = "Other - Please specify" Then
                                If Range("REQO") = "" Then
                                    MsgBox ("You must enter the request in the 'other request' box")
                                End If
                            End If
                    End If
                    
            Case 4
                RngCell.Name = "REQD"
                    If Range("REQD") = "" Then
                        MsgBox ("You must detail the nature of the change request in the 'details of request' box")
                    End If
                    
            Case 5
                RngCell.Name = "PSC"
                    If Range("PSC") <> "" Then
                        If Range("PPT") = "" Then
                            MsgBox ("You must select a scale point")
                        End If
                    End If
                    
            Case 6
                RngCell.Name = "PPT"
                    If Range("PPT") <> "" Then
                        If Range("PSC") = "" Then
                            MsgBox ("You must select a scale first")
                        End If
                    End If
                    
            Case 7
                RngCell.Name = "CDESC"
                    If Range("CDESC") <> "" Then
                        If Range("PDESC") = "" Then
                            MsgBox ("You must upload the job description")
                        End If
                    End If
                    
            Case 8
                RngCell.Name = "PDESC"
                    If Range("PDESC") <> "" Then
                        If Range("CDESC") = "" Then
                            Range("CDESC") = "YES"
                        End If
                    End If
        End Select
    Next RngCell
    
    End Sub
    Last edited by mcinnes01; 01-10-2011 at 11:27 AM.

Thread Information

Users Browsing this Thread

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

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