Solved: Possible Format Bug

[zoom38]

Can someone advise if the following is a bug in excel or a repairable problem. I have found that some cells will not allow me to format them. I can right click, scroll down to format cells but when I click on format cells nothing happens. Keep in mind that this does not occur to all cells but only some cells. This is extremely frustrating.

Thanks
Gary

[austenr]

I guess the obvious question, have you tried clear all. Also, does it happen when you try to format any sheet?

[zoom38]

I did not want to use clear all because each sheet is a copy of the previous with slight modifications. This problem occurs on all of my sheets that is a copy of a previous sheet. It does not occur on a new blank inserted sheet. What could be causing this??

Gary

[lucas]

sounds like the sheet is protected....

[zoom38]

True, but it does this even when I unprotect the sheet. When I right click, scroll down to format cells and click nothing happens. This is after the sheet is unprotected (format cells not grayed out).

Gary

[lucas]

is there any code for the sheet?

[zoom38]

Yes Lucus there is code. Here is all of the code.

Sub Dashes()

'Unlock Top Row, Put In Dashes and Lock Cells With Dashes
Cells.Range("b8:n8").Locked = False
If DatePart("d", Range("a8")) = 19 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:n8") = "-"
Cells.Range("b8:n8").Locked = True
ElseIf DatePart("d", Range("a8")) = 20 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:m8") = "-"
Cells.Range("b8:m8").Locked = True
ElseIf DatePart("d", Range("a8")) = 21 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:l8") = "-"
Cells.Range("b8:l8").Locked = True
ElseIf DatePart("d", Range("a8")) = 22 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:k8") = "-"
Cells.Range("b8:k8").Locked = True
ElseIf DatePart("d", Range("a8")) = 23 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:j8") = "-"
Cells.Range("b8:j8").Locked = True
ElseIf DatePart("d", Range("a8")) = 24 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:i8") = "-"
Cells.Range("b8:i8").Locked = True
ElseIf DatePart("d", Range("a8")) = 25 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:h8") = "-"
Cells.Range("b8:h8").Locked = True
ElseIf DatePart("d", Range("a8")) = 26 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:g8") = "-"
Cells.Range("b8:g8").Locked = True
ElseIf DatePart("d", Range("a8")) = 27 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:f8") = "-"
Cells.Range("b8:f8").Locked = True
ElseIf DatePart("d", Range("a8")) = 28 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:e8") = "-"
Cells.Range("b8:e8").Locked = True
ElseIf DatePart("d", Range("a8")) = 29 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:d8") = "-"
Cells.Range("b8:d8").Locked = True
ElseIf DatePart("d", Range("a8")) = 30 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:c8") = "-"
Cells.Range("b8:c8").Locked = True
ElseIf DatePart("d", Range("a8")) = 31 And DatePart("m", Range("a8")) = 3 Then
Cells.Range("b8:b8") = "-"
Cells.Range("b8:b8").Locked = True
End If

'Unlock Bottom Row, Put In Dashes and Lock Cells With Dashes
Cells.Range("c34:n34").Locked = False
If DatePart("d", Range("a34")) = 20 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("n34:n34") = "-"
Cells.Range("n34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 21 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("m34:n34") = "-"
Cells.Range("m34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 22 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("l34:n34") = "-"
Cells.Range("l34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 23 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("k34:n34") = "-"
Cells.Range("k34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 24 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("j34:n34") = "-"
Cells.Range("j34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 25 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("i34:n34") = "-"
Cells.Range("i34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 26 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("h34:n34") = "-"
Cells.Range("h34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 27 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("g34:n34") = "-"
Cells.Range("g34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 28 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("f34:n34") = "-"
Cells.Range("f34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 29 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("e34:n34") = "-"
Cells.Range("e34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 30 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("d34:n34") = "-"
Cells.Range("d34:n34").Locked = True
ElseIf DatePart("d", Range("a34")) = 31 And DatePart("m", Range("a34")) = 3 Then
Cells.Range("c34:n34") = "-"
Cells.Range("c34:n34").Locked = True
End If
End Sub

Sub CreatNewWorksheets()
Dim wsName
wsName = Sheets(Sheets.Count).Name
With CreateObject("VBScript.RegExp")
.Pattern = "\'\d{2}$"
If .Test(wsName) Then
Set mItem = .Execute(wsName)
myyr = CInt(Replace(mItem.Item(0), "'", ""))
Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Range("b8:o34").ClearContents
.Name = "Apr '" & Format(myyr, "00") & " - Mar '" _
& Format(myyr + 1, "00")
.Range("d1").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!H1+1"
.Range("a8").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!A34"
.Range("q3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!R34"
.Range("t3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!U34"
.Range("w3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!X34"
.Range("aa3").Formula = "='" & _
Application.Substitute(wsName, "'", "''") & "'!AB34"
.Range("b8:o34").ClearContents
End With
End If
End With
Call Dashes
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Protect Password:="pass", UserInterfaceOnly:=True
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Sh.Range("B8:O34")) Is Nothing Then
Select Case Target.Value
Case "V.5", "V1" To "V999"
Target.Interior.ColorIndex = 35

Case "PL.5", "PL1" To "PL999"
Target.Interior.ColorIndex = 34

Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
Target.Interior.ColorIndex = 36

Case "BL.5", "BL1" To "BL999"
Target.Interior.ColorIndex = 36

Case Is = "ML.5", "ML1" To "ML999"
Target.Interior.ColorIndex = 24

Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select
End If
End Sub

I don't see how my code is affecting this.

[lucas]

I'm sorry Gary, I can't duplicate your error. There must be more going on unless I'm just not seeing it this early in the morning. could you possibly clean up your file of personal or business info and post it here? Also which cells specifically are being affected.

[zoom38]

Lucus, Ive attached my file. The following are randomly chosen cells that won't allow me to format them by unprotected the sheet, right clicking, scrolling down to format cells(click & nothing happens).

R10,P23,Q29,T22,U26,V27,W22,X26,B12,F18, S5, A23 and I32.

The following are randomly chosen cells that will allow me to format them.

Q3,S8:S34,J2.

A little tougher finding cells that will work.

[lucas]

Unprotect your Sep 16'99-Sep 13'00 sheet first
then on the main menubar go to format then style then uncheck the protection checkbox at the bottom.....

[lucas]

You have some other problems too. You have all of your code in the thisworkbook module. Move all of it to a standard module.
In the vbe look for insert - module cut and paste most of the code into that module. The exception would be the sub that says:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


that is a worksheet change event code that needs to be in the code module for one or the other or both of the sheets in the workbook.

I just went in and commented all of your code out and did what I described in post 10. Yep you got a mess but you can fix it. Nothing wrong with excel and no virus, etc.

[zoom38]

Lucus, I did as you recommended and now the cells are open for formatting. But when I unchecked the protection in the styles, I lost alot of my formats including row heights, cell alignments and borders. Do I now have to redo all of my formats or did I do something that changed them all.

Thanks
gary

[lucas]

hmm...Gary I'm just not sure. You have a backup of the file here to download if you need it but I would say you will probably have to fix things the way you want them again and then protect the sheet. Not absolutly sure, maybe someone else has an opinion. I personally never use styles. One thing I noticed is that you changed the "normal style" Can't you make a custom style for the cells you want different from the others?

[lucas]

...do the formats come back if you go back and check the protection in the style after you make your format changes....thats worth a try.

[zoom38]

I have to admit I had no idea on how to change the styles so It was totally inadvertant. What is the normal style settings supposed to be?? I change them back.
After modifying some formats to what they are supposed to be I checked again and again I can't change the formats, same symptoms.

I have a backup so that is not a problem. This is very frustrating, what am I doing wrong. Is my code doing this to me?

[lucas]

did you put the code back in? If you did, did you move it as I suggested. I would guess its the sheetchange code thats doing it.

[zoom38]

Steve I did move the code as you suggested. I even commented out the sheetchange module and I still run into the same problem. I just cant' figure it out.

[lucas]

ok, don't panic. One step at a time. Did you run the other macro from the button?

[zoom38]

Thanks Steve, with your help I was able to get it right, I think. I moved the code as you suggested, unprotected the worksheets, selected the entire worksheet and went into styles from the format menu and unchecked the locked box. It messed up all of my formats which I had to correct but at least now I can format all of the cells not just select ones.
Please mark this one solved.

Thank You
Gary

[lucas]

Good deal Gary. Glad to hear it worked out. Will mark this thread solved.