Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
Here's the code:
With ActiveWorkbook.Sheets("Contract Master Order") .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With The Locked statement results in Error 1004, Unable to set the Locked property of the Range class. I've some embedded ActiveX textboxes in the range in question ........is that the problem? If so, can I remove the controls (but retain the user's text) and then protect the sheet and book, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
You sure that the worksheet was unprotected when you started?
Maybe checking first would be beneficial. Option Explicit Sub testme() With ActiveWorkbook.Sheets("Contract Master Order") If .ProtectContents = True _ Or .ProtectDrawingObjects = True _ Or .ProtectScenarios = True Then 'it's protected .Unprotect Password:="SGB" End If .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With End Sub Stuart wrote: Here's the code: With ActiveWorkbook.Sheets("Contract Master Order") .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With The Locked statement results in Error 1004, Unable to set the Locked property of the Range class. I've some embedded ActiveX textboxes in the range in question .......is that the problem? If so, can I remove the controls (but retain the user's text) and then protect the sheet and book, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
On Sat, 10 Jul 2004 12:55:18 +0100, "Stuart"
wrote: With ActiveWorkbook.Sheets("Contract Master Order") .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With The Locked statement results in Error 1004, Unable to set the Locked property of the Range class. I've some embedded ActiveX textboxes in the range in question .......is that the problem? I doubt it. I don't seem to be encountering that problem in a test workbook with embedded ActiveX controls, and I haven't seen it before. This is probably a rather obvious thing to ask, but what the hey... are you sure that the sheet is unprotected at the time that you execute the .Range("A1:N70").Locked = True line? If it's not (perhaps from having run the procedure previously), you'll get a 1004 error. If that's all it is, add an extra line unprotecting the sheet before you change the Locked property. (Do an IF test to make sure that the sheet is in fact protected before executing the Unprotect one.) If so, can I remove the controls (but retain the user's text) and then protect the sheet and book, please? --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
Many thanks to you both.
You're both right. It was protected. I'd set a breakpoint and I then used unprotect code in the Immediate window before continuing on to step through the Locked code. Because it kept throwing the same error, I was looking for some other problem. I'd still be grateful for instruction regarding removing the ActiveX textboxes (but preserving the text entries) before I save the book, please. Regards and thanks. "Dave Peterson" wrote in message ... You sure that the worksheet was unprotected when you started? Maybe checking first would be beneficial. Option Explicit Sub testme() With ActiveWorkbook.Sheets("Contract Master Order") If .ProtectContents = True _ Or .ProtectDrawingObjects = True _ Or .ProtectScenarios = True Then 'it's protected .Unprotect Password:="SGB" End If .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With End Sub Stuart wrote: Here's the code: With ActiveWorkbook.Sheets("Contract Master Order") .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With The Locked statement results in Error 1004, Unable to set the Locked property of the Range class. I've some embedded ActiveX textboxes in the range in question .......is that the problem? If so, can I remove the controls (but retain the user's text) and then protect the sheet and book, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 -- Dave Peterson --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
for each oleObj in ActiveWorkbook.Sheets( _
"Contract Master Order").OleObjects if typeof oleObj.Object is MSForms.TextBox then oleObj.Delete end if Next Assumes the textboxes are linked to cells. Deleting the textbox shouldn't affect the value in the cell. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks to you both. You're both right. It was protected. I'd set a breakpoint and I then used unprotect code in the Immediate window before continuing on to step through the Locked code. Because it kept throwing the same error, I was looking for some other problem. I'd still be grateful for instruction regarding removing the ActiveX textboxes (but preserving the text entries) before I save the book, please. Regards and thanks. "Dave Peterson" wrote in message ... You sure that the worksheet was unprotected when you started? Maybe checking first would be beneficial. Option Explicit Sub testme() With ActiveWorkbook.Sheets("Contract Master Order") If .ProtectContents = True _ Or .ProtectDrawingObjects = True _ Or .ProtectScenarios = True Then 'it's protected .Unprotect Password:="SGB" End If .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With End Sub Stuart wrote: Here's the code: With ActiveWorkbook.Sheets("Contract Master Order") .Range("A1:N70").Locked = True .Protect Password:="SGB" .EnableSelection = xlUnlockedCells End With The Locked statement results in Error 1004, Unable to set the Locked property of the Range class. I've some embedded ActiveX textboxes in the range in question .......is that the problem? If so, can I remove the controls (but retain the user's text) and then protect the sheet and book, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 -- Dave Peterson --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
Many thanks.
Dimmed oleObj As OleObject. Code then removed the textboxes and the entries. I set the textboxes up as follows: select a cell in the sheet click ViewToolbars select Control Toolbox select Textbox click on the same cell and drag to expand the range right click the Control and choose Properties enable Tab, Return and WrapText (nb: couldn't see how to make text align to Top) exit Design mode This must be different to how you envisaged I had created them? Regards and Thanks. "Tom Ogilvy" wrote in message ... for each oleObj in ActiveWorkbook.Sheets( _ "Contract Master Order").OleObjects if typeof oleObj.Object is MSForms.TextBox then oleObj.Delete end if Next Assumes the textboxes are linked to cells. Deleting the textbox shouldn't affect the value in the cell. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks to you both. You're both right. It was protected. I'd set a breakpoint and I then used unprotect code in the Immediate window before continuing on to step through the Locked code. Because it kept throwing the same error, I was looking for some other problem. I'd still be grateful for instruction regarding removing the ActiveX textboxes (but preserving the text entries) before I save the book, please. Regards and thanks. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
Dim oleObj as OleObject
Dim rng as Range for each oleObj in ActiveWorkbook.Sheets( _ "Contract Master Order").OleObjects if typeof oleObj.Object is MSForms.TextBox then set rng = oleObj.TopLeftCell rng.Value = oleObj.Object.Value oleObj.Delete end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks. Dimmed oleObj As OleObject. Code then removed the textboxes and the entries. I set the textboxes up as follows: select a cell in the sheet click ViewToolbars select Control Toolbox select Textbox click on the same cell and drag to expand the range right click the Control and choose Properties enable Tab, Return and WrapText (nb: couldn't see how to make text align to Top) exit Design mode This must be different to how you envisaged I had created them? Regards and Thanks. "Tom Ogilvy" wrote in message ... for each oleObj in ActiveWorkbook.Sheets( _ "Contract Master Order").OleObjects if typeof oleObj.Object is MSForms.TextBox then oleObj.Delete end if Next Assumes the textboxes are linked to cells. Deleting the textbox shouldn't affect the value in the cell. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks to you both. You're both right. It was protected. I'd set a breakpoint and I then used unprotect code in the Immediate window before continuing on to step through the Locked code. Because it kept throwing the same error, I was looking for some other problem. I'd still be grateful for instruction regarding removing the ActiveX textboxes (but preserving the text entries) before I save the book, please. Regards and thanks. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to Lock a Range
Removes them now, many thanks.
Regards. "Tom Ogilvy" wrote in message ... Dim oleObj as OleObject Dim rng as Range for each oleObj in ActiveWorkbook.Sheets( _ "Contract Master Order").OleObjects if typeof oleObj.Object is MSForms.TextBox then set rng = oleObj.TopLeftCell rng.Value = oleObj.Object.Value oleObj.Delete end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks. Dimmed oleObj As OleObject. Code then removed the textboxes and the entries. I set the textboxes up as follows: select a cell in the sheet click ViewToolbars select Control Toolbox select Textbox click on the same cell and drag to expand the range right click the Control and choose Properties enable Tab, Return and WrapText (nb: couldn't see how to make text align to Top) exit Design mode This must be different to how you envisaged I had created them? Regards and Thanks. "Tom Ogilvy" wrote in message ... for each oleObj in ActiveWorkbook.Sheets( _ "Contract Master Order").OleObjects if typeof oleObj.Object is MSForms.TextBox then oleObj.Delete end if Next Assumes the textboxes are linked to cells. Deleting the textbox shouldn't affect the value in the cell. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks to you both. You're both right. It was protected. I'd set a breakpoint and I then used unprotect code in the Immediate window before continuing on to step through the Locked code. Because it kept throwing the same error, I was looking for some other problem. I'd still be grateful for instruction regarding removing the ActiveX textboxes (but preserving the text entries) before I save the book, please. Regards and thanks. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set the FormulaArrary property of the range class | Excel Worksheet Functions | |||
Unable to sort when column is included in range | Excel Discussion (Misc queries) | |||
How do i can lock more than one range in worksheet | Excel Discussion (Misc queries) | |||
VLOOKUP - unable to highlight cell range | Excel Worksheet Functions | |||
Unable to set the Locked property of the range class | Excel Programming |