Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
Hello -
I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
Without having some more detail this is a shot in the dark - but VBA will
error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
What kind of details do you need? Sorry, I'm new to all this.
I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
Try this
1. Unprotect the *workbook* 2. Unprotect the *worksheet(s)* 3. Does the code now run properly/as expected? If it does then the problem is caused by the macro trying to update a something that is protected the solution is to a) change the macro so it doesnt do any updates or b) change the macro to do the unprotect (ask for a password, unprotect, do update, re-protect with same password) or c) run the worksheet without protection or d) change the macro to do the unprotect and hard code the password into the macro I prefer b) but it takes the most work to do properly so you need to decide if one of the alternates is better for your product hth Reg "manni" wrote: What kind of details do you need? Sorry, I'm new to all this. I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
From the sounds of it you are using event code by Greg Wilson.
Here is revised code to allow for protected sheets. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub Gord Dibben MS Excel MVP On Wed, 24 Mar 2010 10:11:02 -0700, manni wrote: What kind of details do you need? Sorry, I'm new to all this. I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
Hi Gord,
I think I was, I had copied it from somewhere else... Apparatly, this is just not my strong point! So, I put in that new code and then protected the sheet again and another error message popped up. This time it tells me "Run-time error '1004': The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization." The sheet protection has a password, but I dont know why it would refer to a password when I type in an actual cell... Am I doing something very out of the ordinary with what I want to do here? Thank you for your patience and your continued help "Gord Dibben" wrote: From the sounds of it you are using event code by Greg Wilson. Here is revised code to allow for protected sheets. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub Gord Dibben MS Excel MVP On Wed, 24 Mar 2010 10:11:02 -0700, manni wrote: What kind of details do you need? Sorry, I'm new to all this. I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
Dump the first code I posted and replace with this version which I find does
not chuck up "wrong password" error. Change "justme" in two places to whatever your password is. Make sure that Wrap Text and Row Autofit are enabled before protecting. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 'edit to suit Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 ActiveSheet.Protect Password:="justme" 'edit to suit Application.ScreenUpdating = True End If End With End Sub Gord On Thu, 25 Mar 2010 12:48:01 -0700, manni wrote: Hi Gord, I think I was, I had copied it from somewhere else... Apparatly, this is just not my strong point! So, I put in that new code and then protected the sheet again and another error message popped up. This time it tells me "Run-time error '1004': The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization." The sheet protection has a password, but I dont know why it would refer to a password when I type in an actual cell... Am I doing something very out of the ordinary with what I want to do here? Thank you for your patience and your continued help "Gord Dibben" wrote: From the sounds of it you are using event code by Greg Wilson. Here is revised code to allow for protected sheets. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub Gord Dibben MS Excel MVP On Wed, 24 Mar 2010 10:11:02 -0700, manni wrote: What kind of details do you need? Sorry, I'm new to all this. I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
Hi Gord,
Thank you that's perfect! I didn't get any error messages. Buttt..... now I have a new issue... After those cells to which the code applies have been clicked on, I'm not allowed to go back to them. So if I entered information, I can't go back and edit it. And if I clicked on it, didn't enter anything and then went to a different cell and wanted to return to that box, it won't let me. Any thoughts on my latest issue??? Thank you again for all your help! "Gord Dibben" wrote: Dump the first code I posted and replace with this version which I find does not chuck up "wrong password" error. Change "justme" in two places to whatever your password is. Make sure that Wrap Text and Row Autofit are enabled before protecting. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 'edit to suit Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 ActiveSheet.Protect Password:="justme" 'edit to suit Application.ScreenUpdating = True End If End With End Sub Gord On Thu, 25 Mar 2010 12:48:01 -0700, manni wrote: Hi Gord, I think I was, I had copied it from somewhere else... Apparatly, this is just not my strong point! So, I put in that new code and then protected the sheet again and another error message popped up. This time it tells me "Run-time error '1004': The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization." The sheet protection has a password, but I dont know why it would refer to a password when I type in an actual cell... Am I doing something very out of the ordinary with what I want to do here? Thank you for your patience and your continued help "Gord Dibben" wrote: From the sounds of it you are using event code by Greg Wilson. Here is revised code to allow for protected sheets. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub Gord Dibben MS Excel MVP On Wed, 24 Mar 2010 10:11:02 -0700, manni wrote: What kind of details do you need? Sorry, I'm new to all this. I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? . . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
I actually just noticed that once the cell has been clicked on it get's
"locked" even if it was unlocked previously. "manni" wrote: Hi Gord, Thank you that's perfect! I didn't get any error messages. Buttt..... now I have a new issue... After those cells to which the code applies have been clicked on, I'm not allowed to go back to them. So if I entered information, I can't go back and edit it. And if I clicked on it, didn't enter anything and then went to a different cell and wanted to return to that box, it won't let me. Any thoughts on my latest issue??? Thank you again for all your help! "Gord Dibben" wrote: Dump the first code I posted and replace with this version which I find does not chuck up "wrong password" error. Change "justme" in two places to whatever your password is. Make sure that Wrap Text and Row Autofit are enabled before protecting. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 'edit to suit Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 ActiveSheet.Protect Password:="justme" 'edit to suit Application.ScreenUpdating = True End If End With End Sub Gord On Thu, 25 Mar 2010 12:48:01 -0700, manni wrote: Hi Gord, I think I was, I had copied it from somewhere else... Apparatly, this is just not my strong point! So, I put in that new code and then protected the sheet again and another error message popped up. This time it tells me "Run-time error '1004': The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization." The sheet protection has a password, but I dont know why it would refer to a password when I type in an actual cell... Am I doing something very out of the ordinary with what I want to do here? Thank you for your patience and your continued help "Gord Dibben" wrote: From the sounds of it you are using event code by Greg Wilson. Here is revised code to allow for protected sheets. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub Gord Dibben MS Excel MVP On Wed, 24 Mar 2010 10:11:02 -0700, manni wrote: What kind of details do you need? Sorry, I'm new to all this. I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? . . |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA code and protected cells
More revisions...........get rid of all previous code and try this version.
Make sure you first unlock desired merged cells and set them for wraptext. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then With ActiveSheet .Protect Password:="justme", userinterfaceonly:=True .EnableSelection = xlNoRestrictions End With Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 Application.ScreenUpdating = True End If End With End Sub Gord On Tue, 30 Mar 2010 07:09:02 -0700, manni wrote: Hi Gord, Thank you that's perfect! I didn't get any error messages. Buttt..... now I have a new issue... After those cells to which the code applies have been clicked on, I'm not allowed to go back to them. So if I entered information, I can't go back and edit it. And if I clicked on it, didn't enter anything and then went to a different cell and wanted to return to that box, it won't let me. Any thoughts on my latest issue??? Thank you again for all your help! "Gord Dibben" wrote: Dump the first code I posted and replace with this version which I find does not chuck up "wrong password" error. Change "justme" in two places to whatever your password is. Make sure that Wrap Text and Row Autofit are enabled before protecting. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then ActiveSheet.Unprotect Password:="justme" 'edit to suit Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 ActiveSheet.Protect Password:="justme" 'edit to suit Application.ScreenUpdating = True End If End With End Sub Gord On Thu, 25 Mar 2010 12:48:01 -0700, manni wrote: Hi Gord, I think I was, I had copied it from somewhere else... Apparatly, this is just not my strong point! So, I put in that new code and then protected the sheet again and another error message popped up. This time it tells me "Run-time error '1004': The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization." The sheet protection has a password, but I dont know why it would refer to a password when I type in an actual cell... Am I doing something very out of the ordinary with what I want to do here? Thank you for your patience and your continued help "Gord Dibben" wrote: From the sounds of it you are using event code by Greg Wilson. Here is revised code to allow for protected sheets. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range Dim ProtectStatus As Boolean With Target If .MergeCells And .WrapText Then ProtectStatus = Me.ProtectContents If ProtectStatus Then Me.Unprotect "" Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False On Error Resume Next ma.MergeCells = False c.ColumnWidth = MrgeWdth c.EntireRow.AutoFit NewRwHt = c.RowHeight c.ColumnWidth = cWdth ma.MergeCells = True ma.RowHeight = NewRwHt cWdth = 0: MrgeWdth = 0 On Error GoTo 0 Application.ScreenUpdating = True If ProtectStatus Then Me.Protect "" End If End With End Sub Gord Dibben MS Excel MVP On Wed, 24 Mar 2010 10:11:02 -0700, manni wrote: What kind of details do you need? Sorry, I'm new to all this. I tried deleting the original code, unprotected the sheet, put in the code, then i unlocked the cells and then i re-protected the sheet again. I clearly didn't do something right because it got the same message "Run-time error '1004': Unable to set the MergeCells property of the Range class" I have no idea what that means... It asks me if I want to debug and then the view code pops up with "ma.MergeCells = False" highlighted in yellow. Thoughts?? "Reg" wrote: Without having some more detail this is a shot in the dark - but VBA will error if your code is trying to make changes to a protected sheet, you need to unprotect, run the code, re-protect it. hth RegMigrant "manni" wrote: Hello - I created a form template in excel and I unlocked various cells and then protected the sheet so that users who completed the form would only be able to make changes to specific areas. I decided later that in "free answer" sections I wanted the merged cells to expand to fit the data the person enters. So, I wrote a VBA code based on a search that I'd performed on here. Individually both of the above worked. But after writing the VBA code and then protecting the sheet, I no longer had access to click on the free response area (the area with the code). So, I went back in and "unlocked" those cells and protected the sheet again. Unfortunately, I seem to have done something wrong, because I get an error message when data is entered in this section, the cell won't expand and it talks about "debugging" and brings up the "view code" area. Could someone please help me?? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable code in a password protected worksheet | Excel Discussion (Misc queries) | |||
Remove Protected Macro Code from Excel Module | Excel Discussion (Misc queries) | |||
Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
Code fails when sheet is protected | Excel Discussion (Misc queries) | |||
paste locked cells and unlocked cells in protected sheet | Excel Worksheet Functions |