+ Reply to Thread
Results 1 to 8 of 8

compile error: duplicate declaration in current scope

Hybrid View

  1. #1
    Registered User
    Join Date
    03-20-2013
    Location
    Grimesland, North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    16

    compile error: duplicate declaration in current scope

    My code is below.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim i As Long
        Dim cnt As Long
        cnt = 0
        With Sheet1
            For i = 1 To 7
                If .OLEObjects("Checkbox" & i).Object.Value = "True" Then
                    cnt = cnt + 1
                End If
            Next
        End With
        If cnt = 0 Then
            MsgBox "Select at least ONE location."
            Cancel = True
            Exit Sub
        End If
    
        Dim c As Range
            For Each c In Range("F63")
                If c.Value = "0" Then
                    Cancel = True
                    MsgBox "Enter DATE in Minor Problem Behavior."
                    Exit Sub
                End If
            Next c
    
        Dim c As Range
            For Each c In Range("F56")
                If c.Value = "FALSE" Then
                    Cancel = True
                    MsgBox "Enter STUDENT NAME"
                    Exit Sub
                End If
            Next c
    
        Dim c As Range
            For Each c In Range("F57")
                If c.Value = "FALSE" Then
                    Cancel = True
                    MsgBox "Enter GRADE"
                    Exit Sub
                End If
            Next c
    
        Dim c As Range
            For Each c In Range("F58")
                If c.Value = "FALSE" Then
                    Cancel = True
                    MsgBox "Enter DATE/TIME"
                    Exit Sub
                End If
            Next c
    
        Dim c As Range
            For Each c In Range("F59")
                If c.Value = "FALSE" Then
                    Cancel = True
                    MsgBox "Enter REFERRING STAFF"
                    Exit Sub
                End If
            Next c
    
        Dim c As Range
            For Each c In Range("F60")
                If c.Value = "FALSE" Then
                    Cancel = True
                    MsgBox "Enter PARENT/GUARDIAN NAME"
                    Exit Sub
                End If
            Next c
    
        Dim c As Range
            For Each c In Range("F61")
                If c.Value = "FALSE" Then
                    Cancel = True
                    MsgBox "Enter PHONE #"
                    Exit Sub
                End If
            Next c
    
        Dim c As Range
            For Each c In Range("F62")
                If c.Value = "FALSE" Then
                    Cancel = True
                    MsgBox "Enter INCIDENT DESCRIPTION"
                    Exit Sub
                End If
            Next c
    
    End Sub
    I had only the checkbox and Select at least ONE location which worked. I then added the Enter DATE in Minor Problem Behavior. It worked. Then I added the rest. Now I get the error "compile error: duplicate declaration in current scope."

    I do not know what to do to fix.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,320

    Re: compile error: duplicate declaration in current scope

    You have repeated the line:

    Dim c As Range

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    03-20-2013
    Location
    Grimesland, North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: compile error: duplicate declaration in current scope

    Does that mean that I just need to delete the Dim c As Range after the first one and everything else stays the same?

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: compile error: duplicate declaration in current scope

    3earth,

    Try:

    
    Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Long, cnt As Long, c As Range
    cnt = 0
    With Sheet1
      For i = 1 To 7
        If .OLEObjects("Checkbox" & i).Object.Value = "True" Then
          cnt = cnt + 1
        End If
      Next
    End With
    If cnt = 0 Then
      MsgBox "Select at least ONE location."
      Cancel = True
      Exit Sub
    End If
    For Each c In Range("F63")
      If c.Value = "0" Then
        Cancel = True
        MsgBox "Enter DATE in Minor Problem Behavior."
        Exit Sub
      End If
    Next c
    For Each c In Range("F56")
      If c.Value = "FALSE" Then
        Cancel = True
        MsgBox "Enter STUDENT NAME"
        Exit Sub
      End If
    Next c
    For Each c In Range("F57")
      If c.Value = "FALSE" Then
        Cancel = True
        MsgBox "Enter GRADE"
        Exit Sub
      End If
    Next c
    For Each c In Range("F58")
      If c.Value = "FALSE" Then
        Cancel = True
        MsgBox "Enter DATE/TIME"
        Exit Sub
      End If
    Next c
    For Each c In Range("F59")
      If c.Value = "FALSE" Then
        Cancel = True
        MsgBox "Enter REFERRING STAFF"
        Exit Sub
      End If
    Next c
    For Each c In Range("F60")
      If c.Value = "FALSE" Then
        Cancel = True
        MsgBox "Enter PARENT/GUARDIAN NAME"
        Exit Sub
      End If
    Next c
    For Each c In Range("F61")
      If c.Value = "FALSE" Then
        Cancel = True
        MsgBox "Enter PHONE #"
        Exit Sub
      End If
    Next c
    For Each c In Range("F62")
      If c.Value = "FALSE" Then
        Cancel = True
        MsgBox "Enter INCIDENT DESCRIPTION"
        Exit Sub
      End If
    Next c
    End Sub
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Registered User
    Join Date
    03-20-2013
    Location
    Grimesland, North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: compile error: duplicate declaration in current scope

    Thank you works beautifully

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,320

    Re: compile error: duplicate declaration in current scope

    Kind of wondering why you're looping. There's only one cell in each of the ranges,

    For Each c In Range("F63")
      If c.Value = "0" Then
        Cancel = True
        MsgBox "Enter DATE in Minor Problem Behavior."
        Exit Sub
      End If
    Next c

    could be written:

      If Range("F63").Value = "0" Then
        Cancel = True
        MsgBox "Enter DATE in Minor Problem Behavior."
        Exit Sub
      End If

    And if Range("F63") is numeric, it would be better to put:

      If Range("F63").Value = 0 Then
        Cancel = True
        MsgBox "Enter DATE in Minor Problem Behavior."
        Exit Sub
      End If


    Regards, TMS

  7. #7
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: compile error: duplicate declaration in current scope

    TMShucks,

    Nice catch - I must be sleeping today......

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,320

    Re: compile error: duplicate declaration in current scope

    No. I missed it first time. I just focused on the stated problem ... the multiplicity of Dim statements. Ho hum ..., easy done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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