Help Perfecting this Select Case code.

[Mister H]

Hi All:

I am having some difficulties with a code that I am using. What the code is suppose to do is it looks in the cell in Column A and dependant on what is input there it locks or unlocks cells. This was working fine but now that I have altered the code it is not always working properly. I had altered the code so that you could copy down in column A and it would unlock cells. To make a long story short I am wondering if the code can be altered so that the beginning cell is ALWAYS column A prior to the code calculating the offset. So no matter which cell is active it will always look calculate the offset beginning with Column A.

I hope this makes sense

Here is the piece of code I am using:</SPAN>

If Target.Row < 16 Then Exit Sub</SPAN>
  On Error Resume Next</SPAN>
 
  For Each cel In Intersect(Target, Target.SpecialCells(xlCellTypeConstants, xlTextValues))</SPAN>
    If cel.Row >= 16 Then cel.Value2 = UCase(cel.Value2)</SPAN>
    Select Case cel</SPAN>
        Case "AR", "AR-DIV", "AR-NSL"
[B]
[COLOR=#ff0000]'I am GUESSING something needs to be added here???[/COLOR][/B]</SPAN>[COLOR=#ff0000]   [/COLOR]         

cel.Offset(0, 9).Resize(1, 6).Locked = True</SPAN>
            cel.Offset(0, 17).Resize(1, 4).Locked = False</SPAN>
            ActiveSheet.CopyGLButton.Visible = False</SPAN>
 
        Case "NPC"</SPAN>
            cel.Offset(0, 10).Resize(1, 3).Locked = True</SPAN>
            cel.Offset(0, 14).Resize(1, 6).Locked = True</SPAN>
            ActiveSheet.CopyGLButton.Visible = False</SPAN>
 
        Case Else</SPAN>
            cel.Offset(0, 9).Resize(1, 6).Locked = False</SPAN>
            cel.Offset(0, 17).Resize(1, 4).Locked = False</SPAN>
            ActiveSheet.CopyGLButton.Visible = False</SPAN>
    End Select</SPAN>
Next cel</SPAN>

Any assistance as ALWAYS is APPRECIATED.

THANKS,
Mark

 

[Mister H]

THANKS anyway Norie...

Regarding the first issue with the Case Else not working were saying that it should work if I remove the wording Special Cells from the code? If you do think of anything let me know.

Have a GREAT day,
Mark

 

[Norie]

Mark

That's what I meant, sorry if I didn't mention it or wasn't clear about it.

 

[Mister H]

Hi Norie:

SORRY for taking so long to try out your suggestion but I was pulled away from this project for a bit and now I am trying to unsuccessfully change this line of code to remove the Special Cells. I am not sure on how to alter it without messing up its functionality.

I changeed this:
For Each cel In Intersect(Target, Target.SpecialCells(xlCellTypeConstants, xlTextValues))


to this:
For Each cel In Intersect(Target, Target(xlCellTypeConstants, xlTextValues))

The code does not seem to function anymore in regards to unlocking the cells. Did I do something wrong?

THANKS Norie,
Take Care,
Mark

 

[Norie]

Mark

Try this.

For Each cel In Target

 

[Mister H]

AWESOME...

THANKS Norie.

The code is now functioning when I fill down in column A and it also functions when I delete the codes from Column A

I have not noticed anything detrimental in regards to removing reference to SpecialCells

Have a GREAT day and THANKS AGAIN,
Mark

 

[Norie]

Mark

Glad it worked.