PDA

View Full Version : VBA Cell locked



speedracer
12-25-2006, 04:42 AM
I am using a combo box in a worksheet(A), and using vba code associated with the combobox object to write a value to a cell on another worksheet(B)(...in the same workbook). However, that cell then becomes locked somehow, and cannot be edited manually. Why is this, and how can I correct it.

I am creating a solution to be able to schedule employees and keep track of the number of times in a 90 day period that they are working in a certain area, in an effort to be able to provide fair distribution of these areas between employees. A combo box for each area provides a list of employees, populated from another worksheet. To avoid scheduling the same employee in two different areas, I decided to write an "x" to the adjacent cell to the employee name so that the user would see that that employee has already been selected for another area. Then all of the combo boxes are populated, and the user can look for the employee name without an "x" next to them.

The problem is that at some point, the user will need to manually delete that x in the other worksheet to correct an error. This cannot be done to the value that is written to the cell by the macro. It seems to be locked in some way. I try to delete the value written to the cell by the macro, and am unable to edit the cell at all. Even when deselecting the associated combobox, the cell is still locked.

I have never run accross this before. I am perplexed on why this is happening, and how to correct it.

Thanks for anyones help.

SRM:banghead:

Simon Lloyd
12-25-2006, 07:32 AM
Speedracer, Welcome to VBAX!, usually it is not quite enough to just explain your goals and problems (as in this case), if you would like a full and useable solution to you problem please post your code, workbook or sample of the workbook - please remember to remove any personal data just leave some random data in the workbook so that we may assess what you are trying to do and provide you with a solution!

Regards,
SImon

speedracer
12-25-2006, 08:11 PM
This is in its infancy right now, but I am attaching the file here.

When I change a value in ComboBox1, I want to enter an "x" on the second worksheet next to the name so it is obvious that this person has been assigned a section. I have accomplished this without a problem. However, when an error is made by the user, they need to be able to go back and delete that (or several) "x". For some reason when the macro writes to that cell, the cell is then locked. I cannot do anything to it. I cant delete or edit it. I find this perplexing.

Can you help?

Thanks

SRM:think:

speedracer
12-25-2006, 08:15 PM
I forgot to mention that I placed the "x" markers on another sheet to see if that would be a work-around, or if there was some kind of properties setting on the second sheet that prohibited me from editing a value written by a macro.

If you look at the formulas in the X column of the second sheet, you will probably get what I was trying.

Thanks again

SRM:help

Brandtrock
12-25-2006, 11:33 PM
Could you enter data in any other cells?

It sounds like you may have still been executing your macro when you tried to enter data into the X cell.

I had no difficulty entering an x into the cell by any of the names, but I may not be understanding your problem completely. The code and comments in your example don't appear to be causing what you described though.

Regards,

speedracer
12-26-2006, 12:13 PM
hmmm...we seem to have narrowed it down. If I go to ComboBox1 and select a name, the x is inserted as it should. However, if I go right to that cell to delete the x, it will not let me. If I go back to the combobox and select a different name, then I can go back and delete the x by the previous name. It appears that as long as the combobox has the number value of the name with a corresponding x, it will not let me modify that cell. So I am thinking that I need to add another couple of lines that write the selected name value to some storage location, and then assign a null or zero value to the combobox. I still find it odd.

lucas
12-26-2006, 01:34 PM
I did not have that problem....I was able to place the X and then go directly to the sheet and "clear contents".....what do you mean by "delete" exactly?