Need help with code

[master_of_none]

I need help putting another code in the same sub.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set rng = Range("O2:O501")

If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target.Value <> "X" Then
With Target
.Value = "X"
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 3
End With

Plus this one



Set rng = Range("P2:P501")

If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target.Value <> "X" Then
With Target
.Value = "X"
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 5
End With

 

[Jonmo1]

That should be BETWEEN

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("SheetName").Unprotect "PasswordHere"

rest of code here

Sheets("SheetName").Protect "PasswordHere"End Sub

 

[master_of_none]

That should be BETWEEN

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("SheetName").Unprotect "PasswordHere"

rest of code here

Sheets("SheetName").Protect "PasswordHere"End Sub

I get a run time error code 9

Subscript out of range

 

[Jonmo1]

Did you adjust SheetName to reflect the name of the sheet you're working with???

 

[master_of_none]

Did you adjust SheetName to reflect the name of the sheet you're working with???

Yes

 

[Jonmo1]

Check spelling, look for extra spaces. In both the code, and the actual sheet tab.

 

[master_of_none]

Check spelling, look for extra spaces. In both the code, and the actual sheet tab.

Found extra space after tab name.

I have the protection set so you can not select locked cell. How can I do that in vba?

 

[Jonmo1]

by using the code to unprotect the sheet, you won't have to worry about whether they're locked or not...

and when you reprotect the sheet at the end of the code, it will remember the settings that were there when you unprotected it..

 

[master_of_none]

by using the code to unprotect the sheet, you won't have to worry about whether they're locked or not...

and when you reprotect the sheet at the end of the code, it will remember the settings that were there when you unprotected it..

When I protect the sheet the regular way, it prompt if you want to allow editing, select cell that are lock, select cell that are unlock ect. ect.

I have it set up not to select cells that are locked and the sheet is protected. When I use the code that you gave me, the cells are lock but you can still select them. Can I add anything else to the "Sheets("sheetname").protect " line to have the cells not be able to be selected?

 

[Jonmo1]

and when you reprotect the sheet at the end of the code, it will remember the settings that were there when you unprotected it..

It will remember the way it was set before..

 

[master_of_none]

It will remember the way it was set before..

I tried it again. When I fire the code it works the correct way. I close the file and save changes and reopen it, you can select the cells that are locked and you cannot edit them.

I created a macro and view it.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Can I use any of this?