Duplicate declaration in current scope

[jimmyyy]

I just compiled this together it was working fine till i added more ElsIf's

Error says:
"Compile error: Duplicate declaration in current scope"

Code (pardon it a bit long):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE2 As String = "B216,B419,B622,B825,B1028,B1231,B1434,B1637,B1840,B2043,B2246,B2449,B2652,B2855,B3058,B3261,B3464,B3667,B3870,B4073,B4276,B4479,B4682,B4885,B5088"
Const WS_RANGE1 As String = "B15,B421,B624,B827,B1030,B1233,B1436,B1639,B1842,B2045,B2248,B2451,B2654,B2857,B3060,B3263,B3466,B3669,B3872,B4075,B4278,B4481,B4684,B4887,B5090"
Const WS_RANGE2 As String = "B17,B220,B626,B829,B1032,B1235,B1438,B1641,B1844,B2047,B2250,B2453,B2656,B2859,B3062,B3265,B3468,B3671,B3874,B4077,B4280,B4483,B4686,B4889,B5092"
Const WS_RANGE3 As String = "B19,B222,B425,B831,B1034,B1237,B1440,B1643,B1846,B2049,B2252,B2455,B2658,B2861,B3064,B3267,B3470,B3673,B3876,B4079,B4282,B4485,B4688,B4891,B5094"
Const WS_RANGE3 As String = "B21,B224,B427,B630,B1036,B1239,B1442,B1645,B1848,B2051,B2254,B2457,B2660,B2863,B3066,B3269,B3472,B3675,B3878,B4081,B4284,B4487,B4690,B4893,B5096"
Const WS_RANGE3 As String = "B23,B226,B429,B632,B835,B1241,B1444,B1647,B1850,B2053,B2256,B2459,B2662,B2865,B3068,B3271,B3474,B3677,B3880,B4083,B4286,B4489,B4692,B4895,B5098"
Const WS_RANGE3 As String = "B25,B228,B431,B634,B837,B1040,B1446,B1649,B1852,B2055,B2258,B2461,B2664,B2867,B3070,B3273,B3476,B3679,B3882,B4085,B4288,B4491,B4694,B4897,B5100"
Const WS_RANGE3 As String = "B27,B230,B433,B636,B839,B1042,B1245,B1651,B1854,B2057,B2260,B2463,B2666,B2869,B3072,B3275,B3478,B3681,B3884,B4087,B4290,B4493,B4696,B4899,B5102"
Const WS_RANGE3 As String = "B29,B232,B435,B638,B841,B1044,B1247,B1450,B1856,B2059,B2262,B2465,B2668,B2871,B3074,B3277,B3480,B3683,B3886,B4089,B4292,B4495,B4698,B4901,B5104"
Const WS_RANGE3 As String = "B31,B234,B437,B640,B843,B1046,B1249,B1452,B1655,B2061,B2264,B2467,B2670,B2873,B3076,B3279,B3482,B3685,B3888,B4091,B4294,B4497,B4700,B4903,B5106"
Const WS_RANGE3 As String = "B33,B236,B439,B642,B845,B1048,B1251,B1454,B1657,B1860,B2266,B2469,B2672,B2875,B3078,B3281,B3484,B3687,B3890,B4093,B4296,B4499,B4702,B4905,B5108"
Const WS_RANGE3 As String = "B35,B238,B441,B644,B847,B1050,B1253,B1456,B1659,B1862,B2065,B2471,B2674,B2877,B3080,B3283,B3486,B3689,B3892,B4095,B4298,B4501,B4704,B4907,B5110"
Const WS_RANGE3 As String = "B37,B240,B443,B646,B849,B1052,B1255,B1458,B1661,B1864,B2067,B2270,B2676,B2879,B3082,B3285,B3488,B3691,B3894,B4097,B4300,B4503,B4706,B4909,B5112"
Const WS_RANGE3 As String = "B39,B242,B445,B648,B851,B1054,B1257,B1460,B1663,B1866,B2069,B2272,B2475,B2881,B3084,B3287,B3490,B3693,B3896,B4099,B4302,B4505,B4708,B4911,B5114"
Const WS_RANGE3 As String = "B41,B244,B447,B650,B853,B1056,B1259,B1462,B1665,B1868,B2071,B2274,B2477,B2680,B3086,B3289,B3492,B3695,B3898,B4101,B4304,B4507,B4710,B4913,B5116"
Const WS_RANGE3 As String = "B43,B246,B449,B652,B855,B1058,B1261,B1464,B1667,B1870,B2073,B2276,B2479,B2682,B2885,B3291,B3494,B3697,B3900,B4103,B4306,B4509,B4712,B4915,B5118"
Const WS_RANGE3 As String = "B45,B248,B451,B654,B857,B1060,B1263,B1466,B1669,B1872,B2075,B2278,B2481,B2684,B2887,B3090,B3496,B3699,B3902,B4105,B4308,B4511,B4714,B4917,B5120"
Const WS_RANGE3 As String = "B47,B250,B453,B656,B859,B1062,B1265,B1468,B1671,B1874,B2077,B2280,B2483,B2686,B2889,B3092,B3295,B3701,B3904,B4107,B4310,B4513,B4716,B4919,B5122"
Const WS_RANGE3 As String = "B49,B252,B455,B658,B861,B1064,B1267,B1470,B1673,B1876,B2079,B2282,B2485,B2688,B2891,B3094,B3297,B3500,B3906,B4109,B4312,B4515,B4718,B4921,B5124"
Const WS_RANGE3 As String = "B51,B254,B457,B660,B863,B1066,B1269,B1472,B1675,B1878,B2081,B2284,B2487,B2690,B2893,B3096,B3299,B3502,B3705,B4111,B4314,B4517,B4720,B4923,B5126"
Const WS_RANGE3 As String = "B53,B256,B459,B662,B865,B1068,B1271,B1474,B1677,B1880,B2083,B2286,B2489,B2692,B2895,B3098,B3301,B3504,B3707,B3910,B4316,B4519,B4722,B4925,B5128"
Const WS_RANGE3 As String = "B55,B258,B461,B664,B867,B1070,B1273,B1476,B1679,B1882,B2085,B2288,B2491,B2694,B2897,B3100,B3303,B3506,B3709,B3912,B4115,B4521,B4724,B4927,B5130"
Const WS_RANGE3 As String = "B57,B260,B463,B666,B869,B1072,B1275,B1478,B1681,B1884,B2087,B2290,B2493,B2696,B2899,B3102,B3305,B3508,B3711,B3914,B4117,B4320,B4726,B4929,B5132"
Const WS_RANGE3 As String = "B59,B262,B465,B668,B871,B1074,B1277,B1480,B1683,B1886,B2089,B2292,B2495,B2698,B2901,B3104,B3307,B3510,B3713,B3916,B4119,B4322,B4525,B4931,B5134"
Const WS_RANGE3 As String = "B61,B264,B467,B670,B873,B1076,B1279,B1482,B1685,B1888,B2091,B2294,B2497,B2700,B2903,B3106,B3309,B3512,B3715,B3918,B4121,B4324,B4527,B4730,B5136"
Const WS_RANGE3 As String = "B63,B266,B469,B672,B875,B1078,B1281,B1484,B1687,B1890,B2093,B2296,B2499,B2702,B2905,B3108,B3311,B3514,B3717,B3920,B4123,B4326,B4529,B4732,B4935"
If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
   With Target
      Range("B13").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B218").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B628").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B833").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B1038").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B1243").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B1448").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B1653").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B1858").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B2063").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B2268").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B2473").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B2678").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
  With Target
     Range("B2883").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B3088").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B3293").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B3498").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B3703").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B3908").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B4113").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B4318").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B4523").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B4728").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B4933").Select
   End With
   ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
   With Target
      Range("B5138").Select
   End With
End If
End Sub

THE VBA help log says:

The specified name is already used at this level of scope. For example, two variables can have the same name if they are defined in different procedures, but not if they are defined within the same procedure. This error has the following causes and solutions:

• A new variable or procedure has the same name as an existing variable or procedure. For example: [vba]Sub MySub()
  Dim A As Integer
  Dim A As Variant
  . . . ' Other declarations or procedure code here.
  End Sub
  [/vba]Check the current procedure, module, or project and remove any duplicate declarations.
• A Const statement uses the same name as an existing variable or procedure. Remove or rename the constant in question.
• You declared a fixed array more than once. Remove or rename one of the arrays.

Search for the duplicate name. When specifying the name to search for, omit any type-declaration character because a conflict occurs if the names are the same and the type-declaration characters are different.
Note that a module-level variable can have the same name as a variable declared in a procedure, but when you want to refer to the module-level variable within the procedure, you must qualify it with the module name. Module names and the names of referenced projects can be reused as variable names within procedures and can also be qualified.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

I was Referd to this type of code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
On Error Goto errH 
Application.EnableEvents = False 
Select Case Target.Address(0, 0) 
   Case "B15" 
      Range("B218").Select 
   Case "B17" 
      Call Range("B219").Select '<== macro2(target)
   Case Else 
      Goto errH 
End Select 
errH: 
Application.EnableEvents = True 
End Sub

but couldnt get it to work

ps. thanks for looking, any ideas love to here um.

[mdmackillop]

WS_RANGE3 is the name of a Constant. These type of names can only be used once to refer to a String as you are doing.

Wth regard to the "Referred to" code, I posted an alternative.

[jimmyyy]

thanks MD i was worried about that.

Im sure theres anotherway i can doit. Is it possible to float objects on an excel spreadsheet so when you scroll the object doesn't move w/ the sheet? if not ill have to make a toolbar which i dont prefer.

[lucas]

As far as your error....it starts in the first 3 lines. If you run the first 3 lines you will get the error you said you didn't get until you added the ifs, etc.

Sub a()
Const WS_RANGE2 As String = "B216,B419,....."
Const WS_RANGE1 As String = "B15,B421,B624,......"
Const WS_RANGE2 As String = "B17,B220,B626,B829,......"
End Sub

In this case your giving Const WS_RANGE2 two different constant(as Malcolm mentioned)values

[mdmackillop]

Is it possible to float objects on an excel spreadsheet so when you scroll the object doesn't move w/ the sheet? if not ill have to make a toolbar which i dont prefer.

I'm not clear what you mean. You can freeze the top rows and place buttons etc. there.

[Bob Phillips]

IU would do it this way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B216,B419,B622,B825,B1028,B1231,B1434,B1637,B1840,B2043,B2246,B2449,B2652, B2855,B3058,B3261,B3464,B3667,B3870,B4073,B4276,B4479,B4682,B4885,B5088")) Is Nothing Then
Range("B13").Select
ElseIf Not Intersect(Target, Range("B15,B421,B624,B827,B1030,B1233,B1436,B1639,B1842,B2045,B2248,B2451,B2654, _
B2857,B3060,B3263,B3466,B3669,B3872,B4075,B4278,B4481,B4684,B4887,B5090")) Is Nothing Then
Range("B218").Select
ElseIf Not Intersect(Target, Range("B17,B220,B626,B829,B1032,B1235,B1438,B1641,B1844,B2047,B2250,B2453,B2656, _
B2859,B3062,B3265,B3468,B3671,B3874,B4077,B4280,B4483,B4686,B4889,B5092")) Is Nothing Then
Range("B628").Select
    ElseIf Not Intersect(Target, Range("B19,B222,B425,B831,B1034,B1237,B1440,B1643,B1846,B2049,B2252,B2455,B2658,B 2861,B3064,B3267,B3470,B3673,B3876,B4079,B4282,B4485,B4688,B4891,B5094")) Is Nothing Then
        Range("B833").Select
    ElseIf Not Intersect(Target, Range("B21,B224,B427,B630,B1036,B1239,B1442,B1645,B1848,B2051,B2254,B2457,B2660,B 2863,B3066,B3269,B3472,B3675,B3878,B4081,B4284,B4487,B4690,B4893,B5096")) Is Nothing Then
        Range("B1038").Select
    ElseIf Not Intersect(Target, Range("B23,B226,B429,B632,B835,B1241,B1444,B1647,B1850,B2053,B2256,B2459,B2662,B2 865,B3068,B3271,B3474,B3677,B3880,B4083,B4286,B4489,B4692,B4895,B5098")) Is Nothing Then
        Range("B1243").Select
    ElseIf Not Intersect(Target, Range("B25,B228,B431,B634,B837,B1040,B1446,B1649,B1852,B2055,B2258,B2461,B2664,B2 867,B3070,B3273,B3476,B3679,B3882,B4085,B4288,B4491,B4694,B4897,B5100")) Is Nothing Then
        Range("B1448").Select
    ElseIf Not Intersect(Target, Range("B27,B230,B433,B636,B839,B1042,B1245,B1651,B1854,B2057,B2260,B2463,B2666,B2 869,B3072,B3275,B3478,B3681,B3884,B4087,B4290,B4493,B4696,B4899,B5102")) Is Nothing Then
        Range("B1653").Select
    ElseIf Not Intersect(Target, Range("B29,B232,B435,B638,B841,B1044,B1247,B1450,B1856,B2059,B2262,B2465,B2668,B2 871,B3074,B3277,B3480,B3683,B3886,B4089,B4292,B4495,B4698,B4901,B5104")) Is Nothing Then
        Range("B1858").Select
    ElseIf Not Intersect(Target, Range("B31,B234,B437,B640,B843,B1046,B1249,B1452,B1655,B2061,B2264,B2467,B2670,B2 873,B3076,B3279,B3482,B3685,B3888,B4091,B4294,B4497,B4700,B4903,B5106")) Is Nothing Then
        Range("B2063").Select
    ElseIf Not Intersect(Target, Range("B33,B236,B439,B642,B845,B1048,B1251,B1454,B1657,B1860,B2266,B2469,B2672,B2 875,B3078,B3281,B3484,B3687,B3890,B4093,B4296,B4499,B4702,B4905,B5108")) Is Nothing Then
        Range("B2268").Select
    ElseIf Not Intersect(Target, Range("B35,B238,B441,B644,B847,B1050,B1253,B1456,B1659,B1862,B2065,B2471,B2674,B2 877,B3080,B3283,B3486,B3689,B3892,B4095,B4298,B4501,B4704,B4907,B5110")) Is Nothing Then
        Range("B2473").Select
    ElseIf Not Intersect(Target, Range("B37,B240,B443,B646,B849,B1052,B1255,B1458,B1661,B1864,B2067,B2270,B2676,B2 879,B3082,B3285,B3488,B3691,B3894,B4097,B4300,B4503,B4706,B4909,B5112")) Is Nothing Then
        Range("B2678").Select
    ElseIf Not Intersect(Target, Range("B39,B242,B445,B648,B851,B1054,B1257,B1460,B1663,B1866,B2069,B2272,B2475,B2 881,B3084,B3287,B3490,B3693,B3896,B4099,B4302,B4505,B4708,B4911,B5114")) Is Nothing Then
        Range("B2883").Select
    ElseIf Not Intersect(Target, Range("B41,B244,B447,B650,B853,B1056,B1259,B1462,B1665,B1868,B2071,B2274,B2477,B2 680,B3086,B3289,B3492,B3695,B3898,B4101,B4304,B4507,B4710,B4913,B5116")) Is Nothing Then
        Range("B3088").Select
    ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
        Range("B3293").Select
    ElseIf Not Intersect(Target, Range("B43,B246,B449,B652,B855,B1058,B1261,B1464,B1667,B1870,B2073,B2276,B2479,B2 682,B2885,B3291,B3494,B3697,B3900,B4103,B4306,B4509,B4712,B4915,B5118")) Is Nothing Then
        Range("B3498").Select
    ElseIf Not Intersect(Target, Range("B45,B248,B451,B654,B857,B1060,B1263,B1466,B1669,B1872,B2075,B2278,B2481,B2 684,B2887,B3090,B3496,B3699,B3902,B4105,B4308,B4511,B4714,B4917,B5120")) Is Nothing Then
        Range("B3703").Select
    ElseIf Not Intersect(Target, Range("B47,B250,B453,B656,B859,B1062,B1265,B1468,B1671,B1874,B2077,B2280,B2483,B2 686,B2889,B3092,B3295,B3701,B3904,B4107,B4310,B4513,B4716,B4919,B5122")) Is Nothing Then
        Range("B3908").Select
    ElseIf Not Intersect(Target, Range("B49,B252,B455,B658,B861,B1064,B1267,B1470,B1673,B1876,B2079,B2282,B2485,B2 688,B2891,B3094,B3297,B3500,B3906,B4109,B4312,B4515,B4718,B4921,B5124")) Is Nothing Then
        Range("B4113").Select
    ElseIf Not Intersect(Target, Range("B51,B254,B457,B660,B863,B1066,B1269,B1472,B1675,B1878,B2081,B2284,B2487,B2 690,B2893,B3096,B3299,B3502,B3705,B4111,B4314,B4517,B4720,B4923,B5126")) Is Nothing Then
        Range("B4318").Select
    ElseIf Not Intersect(Target, Range("B53,B256,B459,B662,B865,B1068,B1271,B1474,B1677,B1880,B2083,B2286,B2489,B2 692,B2895,B3098,B3301,B3504,B3707,B3910,B4316,B4519,B4722,B4925,B5128")) Is Nothing Then
        Range("B4523").Select
    ElseIf Not Intersect(Target, Range("B55,B258,B461,B664,B867,B1070,B1273,B1476,B1679,B1882,B2085,B2288,B2491,B2 694,B2897,B3100,B3303,B3506,B3709,B3912,B4115,B4521,B4724,B4927,B5130")) Is Nothing Then
        Range("B4728").Select
    ElseIf Not Intersect(Target, Range("B57,B260,B463,B666,B869,B1072,B1275,B1478,B1681,B1884,B2087,B2290,B2493,B2 696,B2899,B3102,B3305,B3508,B3711,B3914,B4117,B4320,B4726,B4929,B5132")) Is Nothing Then
        Range("B4933").Select
    ElseIf Not Intersect(Target, Range("B59,B262,B465,B668,B871,B1074,B1277,B1480,B1683,B1886,B2089,B2292,B2495,B2 698,B2901,B3104,B3307,B3510,B3713,B3916,B4119,B4322,B4525,B4931,B5134")) Is Nothing Then
        Range("B5138").Select
    ElseIf Not Intersect(Target, Range("B61,B264,B467,B670,B873,B1076,B1279,B1482,B1685,B1888,B2091,B2294,B2497,B2 700,B2903,B3106,B3309,B3512,B3715,B3918,B4121,B4324,B4527,B4730,B5136")) Is Nothing Then
        'do what
    ElseIf Not Intersect(Target, Range("B63,B266,B469,B672,B875,B1078,B1281,B1484,B1687,B1890,B2093,B2296,B2499,B2 702,B2905,B3108,B3311,B3514,B3717,B3920,B4123,B4326,B4529,B4732,B4935")) Is Nothing Then
        'do what
    End If
End Sub

[johnske]

Hey jimmyyy,

Please note there is a bug in the VBA tags that introduces an unwanted space when the length of the line reaches a certain size. As a result of this bug, you'll find spaces that will introduce errors, this starts at B2652 (which you'll see has become B 2654) in the code xld's given you and carries on at each line directly below this.

So when you paste this code to the code pane you'll then have to manually go through the code and delete all those spaces - it should then work fine for you

Sorry for this inconvenience, but the board coder has been notified about the bug.