+ Reply to Thread
Results 1 to 2 of 2

Integrating an IF into a Case select

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-14-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    257

    Integrating an IF into a Case select

    Can anyone help me with something:

    I am running a Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) and I am using the Select Case option.

    Select Case (Sh.CodeName)
        Case "Sheet2", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24", "Sheet25", "Sheet25", "Sheet26", "Sheet27", "Sheet28", "Sheet29", "Sheet30", "Sheet31", "Sheet32", "Sheet33", "Sheet34", "Sheet35", "Sheet36", "Sheet37"
            
            Application.EnableEvents = False
            On Error GoTo NoComments
            
            For Each Cell In Cells.SpecialCells(xlCellTypeComments)
                Cell.Comment.Delete
            Next Cell
    
    
            
    NoComments:
    
                    
            For Each Cell In Target
                If Intersect(Range("B6:N12"), Cell) Is Nothing = False Or Intersect(Range("B15:N21"), Cell) Is Nothing = False Or Intersect(Range("B24:N30"), Cell) Is Nothing = False Or Intersect(Range("B33:N39"), Cell) Is Nothing = False Then
                    If Cell.Value <> "" And Cell.Value <> "D/O" And Cell.Value <> "HOLS" And Cell.Value Then
                        If HasComment(Cell) = False Then
                            Cell.AddComment
                        End If
                        Cell.Comment.Text Cell.Value
                        Cell.Comment.Visible = True
                        
                    End If
                End If
            Next Cell
    Whilst I still want the code to run on Sheet2 but on a different range of cells. I was trying to use some sort of
    IF Case = sh.Sheet2

    **range code**

    Else

    **range code**

    But it doesn't like it - I might be doing it completely wrong.
    I'd appreciate any help.

    Thanks

    John

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    John

    Perhaps structure things like this.
    Case "Sheet2"
          If <criteria> Then
              ' do something
          Else
              ' do something different
           End If
    If posting code please use code tags, see here.

+ 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