| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy one: On Open selected cells in a worksheet are disabled.
Hi,
If i want to disable particular cells on a worksheet that contain values so that they can't be changed when it opens what is the correct code. i have tried the following but get error 91 or subscript out of range. Option Explicit Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim xlsheet As Object Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Enabled = False Set rng1 = xlsheet.Range("b7:c7") rng1.Enabled = False Set rng2 = xlsheet.Range("a11:g23") rng2.Enabled = False End Sub Thanks WAP |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one: On Open selected cells in a worksheet are disabled.
Ranges don't have an Enabled property, so the code
rng.Enabled = False will fail. Use the Locked property and then protect the worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WAP" wrote in message ... Hi, If i want to disable particular cells on a worksheet that contain values so that they can't be changed when it opens what is the correct code. i have tried the following but get error 91 or subscript out of range. Option Explicit Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim xlsheet As Object Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Enabled = False Set rng1 = xlsheet.Range("b7:c7") rng1.Enabled = False Set rng2 = xlsheet.Range("a11:g23") rng2.Enabled = False End Sub Thanks WAP |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one: On Open selected cells in a worksheet are disabled.
Thanks,
I think I've got it correct but I get error saying that the subscript is out of range in line Set xlsheet = Worksheets("sheet1") of corrected code Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Locked = True Set rng1 = xlsheet.Range("b7:c7") rng1.Locked = True Set rng2 = xlsheet.Range("a11:g23") rng2.Locked = True End Sub ummmm?????? Just a wee bit lost here - be gentle please. Regards WAP -----Original Message----- Ranges don't have an Enabled property, so the code rng.Enabled = False will fail. Use the Locked property and then protect the worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WAP" wrote in message ... Hi, If i want to disable particular cells on a worksheet that contain values so that they can't be changed when it opens what is the correct code. i have tried the following but get error 91 or subscript out of range. Option Explicit Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim xlsheet As Object Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Enabled = False Set rng1 = xlsheet.Range("b7:c7") rng1.Enabled = False Set rng2 = xlsheet.Range("a11:g23") rng2.Enabled = False End Sub Thanks WAP . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one: On Open selected cells in a worksheet are disabled.
Chip,
If I use the following code, Sub auto_open() Worksheets(1).Range("b3:b6").Locked = False Worksheets(1).Range("f6:f8").Locked = False Worksheets(1).Protect End Sub Nothing happens, I can still change cells at will. The cells that are to be unlocked are data entry cells, the cells that are protected are reference data and cells that contain formulii. Regards WAP -----Original Message----- Thanks, I think I've got it correct but I get error saying that the subscript is out of range in line Set xlsheet = Worksheets("sheet1") of corrected code Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Locked = True Set rng1 = xlsheet.Range("b7:c7") rng1.Locked = True Set rng2 = xlsheet.Range("a11:g23") rng2.Locked = True End Sub ummmm?????? Just a wee bit lost here - be gentle please. Regards WAP -----Original Message----- Ranges don't have an Enabled property, so the code rng.Enabled = False will fail. Use the Locked property and then protect the worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WAP" wrote in message ... Hi, If i want to disable particular cells on a worksheet that contain values so that they can't be changed when it opens what is the correct code. i have tried the following but get error 91 or subscript out of range. Option Explicit Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim xlsheet As Object Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Enabled = False Set rng1 = xlsheet.Range("b7:c7") rng1.Enabled = False Set rng2 = xlsheet.Range("a11:g23") rng2.Enabled = False End Sub Thanks WAP . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one: On Open selected cells in a worksheet are disabled.
You need to set the Locked property to True, not False.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WAP" wrote in message ... Chip, If I use the following code, Sub auto_open() Worksheets(1).Range("b3:b6").Locked = False Worksheets(1).Range("f6:f8").Locked = False Worksheets(1).Protect End Sub Nothing happens, I can still change cells at will. The cells that are to be unlocked are data entry cells, the cells that are protected are reference data and cells that contain formulii. Regards WAP -----Original Message----- Thanks, I think I've got it correct but I get error saying that the subscript is out of range in line Set xlsheet = Worksheets("sheet1") of corrected code Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Locked = True Set rng1 = xlsheet.Range("b7:c7") rng1.Locked = True Set rng2 = xlsheet.Range("a11:g23") rng2.Locked = True End Sub ummmm?????? Just a wee bit lost here - be gentle please. Regards WAP -----Original Message----- Ranges don't have an Enabled property, so the code rng.Enabled = False will fail. Use the Locked property and then protect the worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WAP" wrote in message ... Hi, If i want to disable particular cells on a worksheet that contain values so that they can't be changed when it opens what is the correct code. i have tried the following but get error 91 or subscript out of range. Option Explicit Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim xlsheet As Object Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Enabled = False Set rng1 = xlsheet.Range("b7:c7") rng1.Enabled = False Set rng2 = xlsheet.Range("a11:g23") rng2.Enabled = False End Sub Thanks WAP . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one: On Open selected cells in a worksheet are disabled.
Do you in fact have a sheet named "Sheet1"?
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WAP" wrote in message ... Thanks, I think I've got it correct but I get error saying that the subscript is out of range in line Set xlsheet = Worksheets("sheet1") of corrected code Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Locked = True Set rng1 = xlsheet.Range("b7:c7") rng1.Locked = True Set rng2 = xlsheet.Range("a11:g23") rng2.Locked = True End Sub ummmm?????? Just a wee bit lost here - be gentle please. Regards WAP -----Original Message----- Ranges don't have an Enabled property, so the code rng.Enabled = False will fail. Use the Locked property and then protect the worksheet. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "WAP" wrote in message ... Hi, If i want to disable particular cells on a worksheet that contain values so that they can't be changed when it opens what is the correct code. i have tried the following but get error 91 or subscript out of range. Option Explicit Dim rng As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim xlsheet As Object Sub auto_open() Set xlsheet = Worksheets("sheet1") Set rng = xlsheet.Range("b5") rng.Enabled = False Set rng1 = xlsheet.Range("b7:c7") rng1.Enabled = False Set rng2 = xlsheet.Range("a11:g23") rng2.Enabled = False End Sub Thanks WAP . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Protecting selected cells in a worksheet | Excel Discussion (Misc queries) | |||
| Format drop down option won't open on selected cells | Excel Discussion (Misc queries) | |||
| How do I protect selected cells on my worksheet? | Setting up and Configuration of Excel | |||
| can I print selected cells only within a worksheet | New Users to Excel | |||
| Open a window / box showing a selected range of cells only | Excel Programming | |||
Linear Mode
