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]

Norie:

I am not really sure why I am using SpecialCells. I would have to check back but I think this came into effect when I requested that my code be revised so that if the user copies the value down (eg enter AR in A17 and Fill Down to A33) the code checks each cell in the range and changes the locked cells. Should I be using something else?

The code is suppose to work on data entered into rows 17:266:

1) Make sure that anything Pasted on to the sheet is PasteSpecial Value
2) Change everything entered on the sheet to UPPERCASE
3) Lock or Unlock cells based on what is input in A17:A266
4) Autofit the rows 17:266

Maybe I have had too many things added to the code? Everything seems to work except for the Case Else portion. Any suggestions?

THANKS,
Mark</SPAN>

 

[Norie]

Mark

I don't see why you should need to use SpecialCells, if the user is copying down a string value no formulas are involved and all the cells contain a formula.

As for it being too much, I don't think so it looks like something that can be handled.

I don't see how some of the code fits in though.

For example the undo stuff, what are you undoing?

 

[Mister H]

I THINK (termed used loosely) that the Undo was added there so that when a user just does a copy and paste that it is reversed so that it is a PasteSpecial Values. That is the only reason I can think of. Like I said I have pieced this together over time and when I put it together if it looked like it worked I just left in whatever code I had added. As you can tell I don't really know whet I am doing.

 

[Norie]

Mark

Couldn't you just have done paste special values without the undo?

 

[Mister H]

Hi Norie:

Probably... but how do I go about doing that? Before the code got revised peopel were just cutting and pasting and it was messing up the spreadsheet. Whoever helped out last time must have suggested that of I found soemthing similar to what I was trying to do in another post and pasted into my code. How do I modify the code or should another event be created for the Paste? As per usual, I am at a loss...

THANKS,
Mark

 

[Norie]

Mark

I thought you had protection on the worksheet?

 

[Mister H]

Yes there is Protection on the worksheet, but not in the area that the user would be cutting and pasting? It sounds like I am missing something?

 

[Norie]

What should the user be allowed to put in the range and what shouldn't they be allowed?

 

[Mister H]

Hi Norie:

Basically they should be able to Paste anything except for formatting, formulas and merged cells. Mostly they would be copy names and amounts from another workbook or sheet and pasting them into this workbook.

THANKS,
Mark

 

[Norie]

Mark

I can't see how you could prevent all that.

Perhaps locking the cells and only allowing data entered into in some other way.

I'm afraid I can think of a way right now, though earlier I was thinking of data validation or a userform but I don't know if they would be at all appropriate.