+ Reply to Thread
Results 1 to 11 of 11

Select case on range name

Hybrid View

  1. #1
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Select case on range name

    Slight variation on above
    Option Explicit
    Sub VALIDATEINPUT()
        Dim Nm As Name
    
        For Each Nm In Names
            Select Case Nm.Name
    
            Case "EMP": If Range("EMP") = "" Then MsgBox ("You must enter an employee number at the top")
            Case "RES"
                If Range("RES") = "" Then
                    MsgBox ("You must select a reason for the change request")
                ElseIf Range("RES") = "Other - Please specify" Then
                    If Range("RESO") = "" Then MsgBox ("You must enter a reason in the 'other reason' box")
                    End If
                    'etc
         End Select
         Next Nm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

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

    Re: Select case on range name

    Hi Roy thats great it worked a treat, I'm just having one problem in that I don't want the code below to run until non of the case match. Is there a way of saying "IF NO CASES MATCH THEN ..."


    If Range("MANG") = "" Then
                        MsgBox ("Please enter your employee number in the orange box")
                        Sheets(2).CommandButton4.Visible = True
                    ElseIf Sheets(2).CheckBox1.Value = 0 Then
                        MsgBox ("Please tick the checkbox to confirm you wish to make this request")
                        If Sheets(2).CheckBox1.Value = 1 Then ActiveSheet.Unprotect Password:="TMC"
                            Call Module4.AutoComp
                            Sheets(2).CommandButton4.Visible = False
                    
                            For Each RngCell In Range("ingrp").Cells
                                RngCell.Locked = True
                            Next RngCell
                        
                                                              ActiveSheet.Protect Password:="TMC"
                          
                    End If

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

    Re: Select case on range name

    Ok so I have found 2 problems and more or less solved 1 with the select case.

    The problems are that it will go through every case and display every validation prompt that has a matching case.

    The other problem is that I want it to only run the last validation step when all the others pass (ie don't match a case)

    I had a play at just using a more simple approach of nested ifs, this is working how I want it other than it doesn't go all the way through validation if you pass the validation for RES then it stops and wont go on to validation REQ etc

    This is my code

    Option Explicit
    Sub VALIDATEINPUT()
        Dim RngCell As Range
    
                If Range("EMP") = "" Then
                    MsgBox ("You must enter an employee number at the top")
                ElseIf Range("REQ") = "" Then
                    MsgBox ("You must select the request for the change")
                ElseIf Range("REQ") = "Other - please specify" Then
                    If Range("REQO") = "" Then
                        MsgBox ("You must enter the request in the 'other request' box")
                ElseIf Range("RES") = "" Then
                    MsgBox ("You must select a reason for the change request")
                ElseIf Range("RES") = "Other - please specify" Then
                    If Range("RESO") = "" Then
                        MsgBox ("You must enter a reason in the 'other reason' box")
                ElseIf Range("REQD") = "" Then
                    MsgBox ("You must detail the nature of the change request in the 'details of request' box")
                ElseIf Range("PSC") <> "" Then
                    If Range("PPT") = "" Then
                        MsgBox ("You must select a scale point")
                ElseIf Range("PPT") <> "" Then
                    If Range("PSC") = "" Then
                        MsgBox ("You must select a scale first")
                        Range("PPT") = ""
                ElseIf Range("CDESC") <> "" Then
                    If Range("PDESC") = "" Then
                        MsgBox ("You must upload the job description")
                ElseIf Range("PDESC") <> "" Then
                    If Range("CDESC") = "" Then
                        Range("CDESC") = "YES"
                ElseIf Range("MANG") = "" Then
                    MsgBox ("Please enter your employee number in the orange box")
                    Sheets(2).CommandButton4.Visible = True
                ElseIf Sheets(2).CheckBox1.Value = 0 Then
                    MsgBox ("Please tick the checkbox to confirm you wish to make this request")
                
                Else
                        ActiveSheet.Unprotect Password:="TMC"
                        Call Module4.AutoComp
                        Sheets(2).CommandButton4.Visible = False
                        For Each RngCell In Range("ingrp").Cells
                        RngCell.Locked = True
                        Next RngCell
                        ActiveSheet.Protect Password:="TMC"
                
                End If
                End If
                End If
                End If
                End If
                End If
                End If
                
                
    End Sub
    Last edited by mcinnes01; 01-10-2011 at 07:37 AM.

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

    Re: Select case on range name

    Ok here is my problem...

    Basically some cell entry variations have multiple validation reasons.

    For example Cells PSC (salary scale) and PPT (salary point)

    If both PSC and PPT are blank then they pass

    If both PSC and PPT have a value they pass

    If PSC has a value and PPT is blank msgbox (you must select a scale point)

    If PSC is blank and PPT has a value msgbox (you must select a salary scale first)

    This is my code for this particular item in the validation, the problem is, is that it will work if they are both blank and if they both have a value and if PPT has a value and PSC is blank

    BUT*** it doesn't work is PSC has a value and PPT is blank.

    How can I over come this, I have about 4 instances where this happens


    3:          If Range("PPT") <> "" Then
                    If Range("PSC") = "" Then
                        Range("PPT") = ""
                        MsgBox ("You must select a salary scale first")
                If Range("PPT") = "" Then
                    If Range("PSC") <> "" Then
                        MsgBox ("You must select a scale point")
                    Else: GoTo 4

+ Reply to Thread

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