Shortening up Code

**Kr4ftw3rk** · 02-08-2008, 01:58 PM

Hello, Is there a way to shorten up, make simpler, the code below. Thnx for any help.

If c.Offset(0, 35).Value = "" Then
                Me.chkDisk.Value = "0"
            Else
                Me.chkDisk.Value = "1"
            End If
            If c.Offset(0, 36).Value = "" Then
                Me.chkViaEmailFtp.Value = "0"
            Else
                Me.chkViaEmailFtp.Value = "1"
            End If
            If c.Offset(0, 37).Value = "" Then
                Me.chkFilm.Value = "0"
            Else
                Me.chkFilm.Value = "1"
            End If
            If c.Offset(0, 38).Value = "" Then
                Me.chkCra.Value = "0"
            Else
                Me.chkCra.Value = "1"
            End If
            If c.Offset(0, 39).Value = "" Then
                Me.chkPhotos.Value = "0"
            Else
                Me.chkPhotos.Value = "1"
            End If
            If c.Offset(0, 40).Value = "" Then
                Me.chkYes.Value = "0"
            Else
                Me.chkYes.Value = "1"
            End If
            If c.Offset(0, 41).Value = "" Then
                Me.chkNo.Value = "0"
            Else
                Me.chkNo.Value = "1"
            End If
            If c.Offset(0, 43).Value = "" Then
                Me.chkEpson.Value = "0"
            Else
                Me.chkEpson.Value = "1"
            End If
            If c.Offset(0, 44).Value = "" Then
                Me.chkImpress.Value = "0"
            Else
                Me.chkImpress.Value = "1"
            End If
            If c.Offset(0, 45).Value = "" Then
                Me.chkPdfFile.Value = "0"
            Else
                Me.chkPdfFile.Value = "1"
            End If

**rylo** · 02-08-2008, 09:32 PM

Hi

Here a possibility.

I've assumed that the default for an array is used (ie 0) but if you want to set the array base index then use the

Option Base

statement to determine what starting number you want to use. If you change the base to 1, then change the +35 to +34.

arr = Array("chkDisk", "chkViaEmailFtp", "chkFilm", "chkCra", "chkPhotos", "chkYes", "chkNo", "chkEpson", "chkImpress", "chkPdfFile")
  For i = LBound(arr) To UBound(arr)
    If c.Offset(0, i + 35).Value = "" Then
      ActiveSheet.Shapes(arr(i)).Value = "0"
    Else
      ActiveSheet.Shapes(arr(i)).Value = "1"
    End If
  Next i

I'm guessing that the items are checkboxes, but I don't know where they are. If on a sheet then the above may work (didn't test....), but if on a form then you will have to rejig along the lines of

me.controls(arr(i)).value

Hope that gives you some ideas.

rylo

**Kr4ftw3rk** · 02-11-2008, 11:38 AM

Hello Rylo,

The code snippet is for part of a userform, and yes the code shown is for multiple checkboxes I have on the userform. The code in it's entirety seems to long (at least it appears long to me and thats why I didn't paste it), anyways - I was looking for a way to simplify things, starting with the checkboxes. I'll definitely give your advice a try. Thnx again.

Shortening up Code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Shortening up Code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions