If statement if a cell is locked

[data808]

How do you write VBA if cell is locked then exit sub?

I tried this:

If Target.Range("C2").Locked = True Then
Exit Sub
End If

OR

If Range("C2") = Selection.Locked = True Then
Exit Sub
End If

None of those have worked. Please advise. Thanks.

 

[6StringJazzer]

Please show all of the code for this sub, and tell which module contains it. Neither of the two examples you showed makes any sense. To give you the correct code I have to see what you are trying to do to.

If you just care about cell C2 then this should work, depending on what sheet you are talking about and what module contains your code.

If Range("C2").Locked Then
   Exit Sub
End If

 

[data808]

Please show all of the code for this sub, and tell which module contains it. Neither of the two examples you showed makes any sense. To give you the correct code I have to see what you are trying to do to.

If you just care about cell C2 then this should work, depending on what sheet you are talking about and what module contains your code.

If Range("C2").Locked Then
   Exit Sub
End If

Thanks. Just realized that this is what is causing the issue:

[G4:G34] = [INDEX(UPPER(G4:G34),)]

I tried it in the change and selectionchange event and it seems that every time the cursor moves from cell to cell, whether you type anything or not, it causes the sheet to have a change so if I don't type anything and just move the cursor around the cells and then try to close it, it will ask if I want to save the changes. Would you know how to stop that? I'm thinking I would need another way to go about making the G column to be upper case letters.

 

[6StringJazzer]

You have not given nearly information about your code or what you are trying to do to be able to answer this last question.

SelectionChange will fire every time the cursor moves to another cell. But Change does not do this.

What do you mean "try to close it"? That phrase is just randomly inserted into your sentence out of context. Do you mean close the file?

One way to uppercase a range is the following. I do not know of a way to do it all at once. If you can guarantee that none of the cells have a formula then you can omit that check.

   Dim C As Range
   
   For Each C In [G3:G34]

      If Left(C.Formula, 1) = "=" Then
         ' cannot convert a formula
      Else
         C.Value = LCase(C.Value)
      End If
   
   Next C

 

[data808]

You have not given nearly information about your code or what you are trying to do to be able to answer this last question.

SelectionChange will fire every time the cursor moves to another cell. But Change does not do this.

What do you mean "try to close it"? That phrase is just randomly inserted into your sentence out of context. Do you mean close the file?

One way to uppercase a range is the following. I do not know of a way to do it all at once. If you can guarantee that none of the cells have a formula then you can omit that check.

   Dim C As Range
  
   For Each C In [G3:G34]

      If Left(C.Formula, 1) = "=" Then
         ' cannot convert a formula
      Else
         C.Value = LCase(C.Value)
      End If
  
   Next C

Thanks for the reply. Sorry for the confusion. When I said "try to close it" I meant close the spreadsheet. So usually if there are any changes made to the spreadsheet and you did not click the save icon to save those changes, a prompt will pop up before it closes to ask if you want to save those changes as a last opportunity to save the file before closing it out.

I did notice what you mentioned about the difference between SelectionChange and the Change events where SelectionChange will count moving your mouse cursor as a change to the spreadsheet and so will ask to save if you try to close the spreadsheet even though you didn't type anything. That is not what I want to happen and so I do wish I could put this line [G4:G34] = [INDEX(UPPER(G4:G34),)] into the Change event, however, when I do so, I get an error. I think the error was something like "Out of stack space" or something to that nature. So I'm not sure why because [G4:G34] = [INDEX(UPPER(G4:G34),)] is the only line that I had in the Change event so there is nothing else that could conflict with that line of code. At least from what I can tell. I looked online and one suggestion was that the line may be calling itself or something. Not sure.

So I haven't tried your latest suggestion yet because I wanted to let you know the details first in case you now have a better picture of what is happening and may have another suggestion to try.

Thanks for the help.

 

[6StringJazzer]

I think the error was something like "Out of stack space"

If there is a change in a sheet, and Worksheet_Change is called, and then within that Sub you make another change to the worksheet, that secondary change will cause a recursive call to Worksheet_Change, and then this second call will also make a change, causing a third call..... This will continue to build the stack until your run out of stack space.

When writing a Change handler that makes changes to the sheet you need to disable events:

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
   
' Do your stuff here

   Application.EnableEvents = True

End Sub

 

[data808]

You have not given nearly information about your code or what you are trying to do to be able to answer this last question.

SelectionChange will fire every time the cursor moves to another cell. But Change does not do this.

What do you mean "try to close it"? That phrase is just randomly inserted into your sentence out of context. Do you mean close the file?

One way to uppercase a range is the following. I do not know of a way to do it all at once. If you can guarantee that none of the cells have a formula then you can omit that check.

   Dim C As Range
  
   For Each C In [G3:G34]

      If Left(C.Formula, 1) = "=" Then
         ' cannot convert a formula
      Else
         C.Value = LCase(C.Value)
      End If
  
   Next C

This code actually made excel crash and it took a bit to end task in task manager. Thanks for trying though.

 

[data808]

If there is a change in a sheet, and Worksheet_Change is called, and then within that Sub you make another change to the worksheet, that secondary change will cause a recursive call to Worksheet_Change, and then this second call will also make a change, causing a third call..... This will continue to build the stack until your run out of stack space.

When writing a Change handler that makes changes to the sheet you need to disable events:

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
  
' Do your stuff here

   Application.EnableEvents = True

End Sub

Thank you very much for this Change Handler explanation. It appears to be working now.