VBA code and merged cells

[jdluke6545]

I am needing a VBA code to unlock cells but when looking for an answer I came across a thread that said VBA and merged cells don't work.
Well I have merged cells and there is no way I can redo the sheet and not have merged cells.
is there any way to get VBA code to play nice with merged cells?
I can post images of my sheet that I need help with VBA code If anyone has a possible solution to this problem first.
Or I can try the XL2BB thing.... never used that before tho....

 

[jolivanes]

Re: "will not work for my needs in this sheet"
Hence "In the future you just might end up with a workbook again that has merged cells in one or more of it's sheets."

 

[jdluke6545]

UPDATE...
Sorry for not getting back in a timely manner, but dealing with the family medical issues of late has been time consuming and mentally draining.
Anyway,, @Alex Blakenburg, the code you posted in post #70 does work except for the fact that now its giving error message in my other VBA macro that selects a group of cells and deletes.
I will post that VBA macro code with the error message it gives me when I can get back to that sheet. It may be a day or so.

 

[jdluke6545]

Re: "will not work for my needs in this sheet"
Hence "In the future you just might end up with a workbook again that has merged cells in one or more of it's sheets."

Yes, completely understand. Thanks!

 

[jdluke6545]

Ok, Back to this. Again, I apologize for not being readily available but it has been a very full and busy past few weeks.
So, the code in post #70 does work even when manually selecting a range of cells and using delete. but it does give an error code in my other VBA code.
The error is:
"Run-time error '1004'
the cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password"
This error looks pretty straight forward. I just need to add a bit of code to unprotect the sheet, run the code, and then protect the sheet back again.
My code that have is to save me time in clearing any previous data the may have been entered so I can start with a fresh clean sheet everytime.

The code I have is:

Sub CLEAR_ALL()

'This CLEARS/DELETES all the input data fields and checks or unchecks the check boxes

Dim CB2 As CheckBox
Dim CB3 As CheckBox
Dim CB4 As CheckBox
Dim CB5 As CheckBox
Dim CB6 As CheckBox
Dim CB7 As CheckBox
Dim CB8 As CheckBox
Dim CB9 As CheckBox
Dim CB10 As CheckBox
Dim CB11 As CheckBox
Dim CB12 As CheckBox

Set CB2 = ActiveSheet.CheckBoxes("check Box 2")
Set CB3 = ActiveSheet.CheckBoxes("check Box 3")
Set CB4 = ActiveSheet.CheckBoxes("check Box 4")
Set CB5 = ActiveSheet.CheckBoxes("check Box 5")
Set CB6 = ActiveSheet.CheckBoxes("check Box 6")
Set CB7 = ActiveSheet.CheckBoxes("check Box 7")
Set CB8 = ActiveSheet.CheckBoxes("check Box 8")
Set CB9 = ActiveSheet.CheckBoxes("check Box 9")
Set CB10 = ActiveSheet.CheckBoxes("check Box 10")
Set CB11 = ActiveSheet.CheckBoxes("check Box 11")
Set CB12 = ActiveSheet.CheckBoxes("check Box 12")

CB2.Value = xlOff
CB3.Value = xlOff
CB4.Value = xlOn
CB5.Value = xlOn
CB6.Value = xlOn
CB7.Value = xlOff
CB8.Value = xlOff
CB9.Value = xlOff
CB10.Value = xlOff
CB11.Value = xlOff
CB12.Value = xlOff


Range("O1:BL3,BX1:CL2,BM3:CL3,O4:CL4,A5:CL5,BL6:BY6").Select
Range("BL6").Activate
Selection.ClearContents
Range("CP3:EF27,GI3:JI27").Select
Range("GI3").Activate
Selection.ClearContents
Range("BX10:CE10,BE14:CE16,BX17:CE17,BX20:CE23,BD24:CE28").Select
Range("BD24").Activate
Selection.ClearContents

Range("A35:F71,AM35:AQ71").Select
Range("AM35").Activate
Selection.ClearContents
Range("BA35:BE71").Select
Selection.FormulaR1C1 = "100%"

Range("BA73:BE81").Select
Selection.FormulaR1C1 = "100%"
Range("A77:F81,AM77:AQ81").Select
Range("AM77").Activate
Selection.ClearContents

Range("A85:BF103").Select
Selection.ClearContents
Range("BH85:BL103").Select
Selection.FormulaR1C1 = "100%"

Range("O1:BL1").Select



'This works on ActiveX CheckBox
'ActiveSheet.CheckBox1.Value = True

End Sub

The first instance of "Selection.ClearContents" (in bold and red font) is where the debug is highlighting.
So I am thinking I need a code to "unprotect" the sheet somewhere before this, and then a code again "protect" the sheet at the end of this code.

 

[Michael M]

Dim CB10 As CheckBox
Dim CB11 As CheckBox
Dim CB12 As CheckBox
activesheet. Unprotect password:="your password"
Set CB2 = ActiveSheet.CheckBoxes("check Box 2")
Set CB3 = ActiveSheet.CheckBoxes("check Box 3")

Then

'This works on ActiveX CheckBox
'ActiveSheet.CheckBox1.Value = True
activesheet. protect password:="your password"
End Sub

 

[jdluke6545]

Dim CB10 As CheckBox
Dim CB11 As CheckBox
Dim CB12 As CheckBox
activesheet. Unprotect password:="your password"
Set CB2 = ActiveSheet.CheckBoxes("check Box 2")
Set CB3 = ActiveSheet.CheckBoxes("check Box 3")

Then

'This works on ActiveX CheckBox
'ActiveSheet.CheckBox1.Value = True
activesheet. protect password:="your password"
End Sub

Thanks you!!
I will give that a try!!

 

[jdluke6545]

AND Voilà!!!!
I think this it!! it seems to be working with my "clear" code and everything else.
I really thank all of you that helped with this!! Especially Alex Blakenburg!!
There is no way I could have done this with out the help from all of you!!!
For the thread,, I will mark post#70 as solved.
Thanks again to everyone!!!

JD

 

[Alex Blakenburg]

Thank for the feedback. Glad we could help.