[VBA] Case statement not recognizing 0

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
root
Posts: 88
Joined: Sun May 18, 2008 11:55 pm UTC

[VBA] Case statement not recognizing 0

Postby root » Thu Oct 07, 2010 5:56 pm UTC

Code: Select all

Private Sub Worksheet_Change(ByVal target As Excel.Range)
    On Error GoTo errhandler
    If Intersect(target, Range("ExtraRows")) Then
        Select Case Range("ExtraRows").Value
            Case 0
                ActiveSheet.Rows("12:14").Hidden = True
            Case 1
                ActiveSheet.Rows("12:14").Hidden = False
                ActiveSheet.Rows("13:14").Hidden = True
            Case 2
                ActiveSheet.Rows("12:13").Hidden = False
                ActiveSheet.Rows("14:14").Hidden = True
            Case 3
                ActiveSheet.Rows("12:14").Hidden = False
            Case Else
                ActiveSheet.Rows("12:14").Hidden = True
            End Select
    End If
errhandler:
End Sub


The above is a snippet of code that is not functioning properly for me. I have a spreadsheet where people can decide how many editable rows they want to describe some data. There is a dropbox cell with the name "ExtraRows." The user can select 0, 1, 2, or 3 extra rows. Then, the number of rows they selected should be shown (ie. un-hidden). For values of 1, 2, and 3 it works great. However, I cannot get it to show "0" rows unless I change the code to look like what is below, which I do not want to do as I may have to edit the spreadsheet in the future and I do not want to have to constantly keep changing the address in the code.

Code: Select all

    If target.Address = "$G$11" Then
        Select Case target.Value
            Case 0
                ActiveSheet.Rows("12:14").Hidden = True
            Case 1
                ActiveSheet.Rows("12:14").Hidden = False
                ActiveSheet.Rows("13:14").Hidden = True
            Case 2
                ActiveSheet.Rows("12:13").Hidden = False
                ActiveSheet.Rows("14:14").Hidden = True
            Case 3
                ActiveSheet.Rows("12:14").Hidden = False
            Case Else
                ActiveSheet.Rows("12:14").Hidden = True
            End Select
    End If

:?:

/edit
Missed the sticky. This is now in the "Help" sticky. :oops:

Return to “Coding”

Who is online

Users browsing this forum: No registered users and 7 guests