Solved Unprotecting a cell when On Focus

August 3, 2012 at 19:41:24
Specs: Windows 7
I have a worksheet that is protected except for the cells that require user input.

In two of those cells, upper case inputs required. I have inserted a macro that converts lower case inputs to upper case in those two cells.

With the worksheet protected, when I input the lower case letters in those cells, a message appears that tells me the cell is protected and I must turn off the worksheet protection in order to input the data.

Does anyone have a macro that will solve this for me? Would this macro be part of the macro that converts the entries to upper case, or would it be a separate macro? I do not want to unprotect the entire worksheet, but only those two cells while they have focus for the data entry.

I confess I know very little about macros or writing macro code, so an explanation can't be too basic...sorry...

Thank you for any help you can be.


See More: Unprotecting a cell when On Focus

Report •

✔ Best Answer
August 5, 2012 at 01:26:59
Dear AlwaysWillingToLearn,

I got it to work. Inside the uppercase subroutine, I unprotected the cells, changed case, then re-protected them upon exiting. It worked.

Thanks for the help!

I have another question about saving a worksheet to a .pdf that I'll put in a new thread, if you don't mind looking for it.

Thanks again for your help!

Sincerely,

Mark



#1
August 4, 2012 at 03:04:08
Sorry i got a bit confused so i will ask a dumb question. Before you protected your sheet did you select those two cells, right click, format cells, and unchecked the box's 'lock cells'?

Basically if you untick/uncheck these cells and then protect your worksheet, you should be able to choose to be able to select unlocked cells.

This means that when your worksheet is protected you can now select and enter values into these two cells.

Hope this makes sense?


Report •

#2
August 4, 2012 at 05:29:00
Dear AlwaysWillingToLearn,

I was happy to see it was you who responded. Not a dumb question at all. I should have been a bit more thorough in my description.

Yes, the cells I want to enter text in are unlocked. When the worksheet is not protected, I can enter my text in the cells in lower case, tab out of the cells, and they change to upper case no problem. When I protect the worksheet, I can still enter the text in lower case in the cells, but upon exiting the cell (with either the tab key or the enter key) I get the message saying that I must unprotect the worksheet in order for it to work.

In short, the cells aren't locked, because I can enter data in them in either case, but the macro won't run if the worksheet is protected.

Thanks for your help. Your print solution worked perfect, and was easy to understand. Thanks again!

Sincerely,

Mark


Report •

#3
August 4, 2012 at 08:33:31
Ahh so it must be the macro that is causing the error, any chance you could post the code, also please tell us which cells you are entering data into.

Report •

Related Solutions

#4
August 4, 2012 at 18:39:49
Dear AlwaysWillingToLearn,

I'd be delighted to.

I don't know if it makes any difference, but in the worksheet I have merged 3 cells for each text input. Cells AA10:AC10 are merged together, as are AH10:AJ10.

Here is the code I used:

Sub auto_open()

' Run the macro UppercaseCell any time a entry is made in a cell in B737-400 Load Form worksheet.

ThisWorkbook.Worksheets("B737-400 Load Form").OnEntry = "UppercaseCell"

End Sub

Sub UppercaseCell()

Dim KeyCells As String

' Define which cells should trigger the ChangeCell macro.

KeyCells = "AA10:AH10"

' If the Activecell is one of the key cells, call the ChangeCell macro.

If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then ChangeCell

End Sub

Sub ChangeCell()

Dim Cell As Object

For Each x In Range("AA10:AH10")

' Change the text in the range to uppercase letters.

x.Value = UCase(x.Value)

Next

End Sub

When the error message appears and I click on "Debug", the line:

x.Value = UCase(x.Value)

is highlighted in yellow

The cells between AC10 and AH10 are completely empty.

Hope this helps. Thanks again for helping me with this.

Mark


Report •

#5
August 5, 2012 at 01:26:59
✔ Best Answer
Dear AlwaysWillingToLearn,

I got it to work. Inside the uppercase subroutine, I unprotected the cells, changed case, then re-protected them upon exiting. It worked.

Thanks for the help!

I have another question about saving a worksheet to a .pdf that I'll put in a new thread, if you don't mind looking for it.

Thanks again for your help!

Sincerely,

Mark


Report •

#6
August 5, 2012 at 02:48:54
Awesome glad you got it working. I'll have a look at your other post and see if i can help

Report •

Ask Question