The main part of the following code works great when I run it
from an icon my machine.
Now trying to add this code to an add-in to be distributed.
When I try F8, it does go into Call Green, but just loops and loops, without
turning any cells green.
When I run the Print Contracts from my custom menu, based on the worksheet
and workbook protection status, the code seems to skip the Call Green and End
sub.
Can anyone please check and advise what I am missing here?

Option Explicit
Sub PrintContract()
' PrintContract Macro
' Keyboard Shortcut: NONE

Application.ScreenUpdating = False

Worksheets("Contract").Select
ActiveSheet.Unprotect Password:="1234"

Cells.Select
Selection.Interior.ColorIndex = -4142

ActiveSheet.Protect Password:="1234"
Worksheets("Contract").PrintOut Copies:=1, Collate:=True

Call Green

Application.ScreenUpdating = True
End Sub

Sub Green()
ActiveWorkbook.Unprotect Password:="4321"
ActiveSheet.Unprotect Password:="1234"
'===============================================
Dim CELL As Range, tempR As Range, rangeToCheck As Range
Cells.Select
Cells.Interior.ColorIndex = -4142
For Each CELL In Intersect(Selection, ActiveSheet.UsedRange)
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If

Next CELL
If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
End
End If

'Select qualifying cells
'TempR.Select
tempR.Interior.ColorIndex = 4 'GREEN
' =================================================
ActiveSheet.Protect Password:="1234"
ActiveWorkbook.Protect Password:="4321"

End Sub

When you put the code into the addin you need to be very explicit in your
references. Make sure theat you reference the activesheet. I also cleaned up
the code just a bit... I removed the selects and I removed the stand alone
End (A stand alone end will clear all global variables). One thing to note is
taht this code could be a problem if the used range gets messed up. That can
be fixed latter if you wish...

Sub Green()
Dim CELL As Range, tempR As Range, rangeToCheck As Range

With ActiveSheet
ActiveWorkbook.Unprotect Password:="4321"
.Unprotect Password:="1234"
'===============================================
.Cells.Interior.ColorIndex = -4142
For Each CELL In .UsedRange
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If
Next CELL

If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
Else
tempR.Interior.ColorIndex = 4
End If

' =================================================
.Protect Password:="1234"
ActiveWorkbook.Protect Password:="4321"
End With
End Sub
--
HTH...

Jim Thomlinson


"BEEJAY" wrote:

The main part of the following code works great when I run it
from an icon my machine.
Now trying to add this code to an add-in to be distributed.
When I try F8, it does go into Call Green, but just loops and loops, without
turning any cells green.
When I run the Print Contracts from my custom menu, based on the worksheet
and workbook protection status, the code seems to skip the Call Green and End
sub.
Can anyone please check and advise what I am missing here?

Option Explicit
Sub PrintContract()
' PrintContract Macro
' Keyboard Shortcut: NONE

Application.ScreenUpdating = False

Worksheets("Contract").Select
ActiveSheet.Unprotect Password:="1234"

Cells.Select
Selection.Interior.ColorIndex = -4142

ActiveSheet.Protect Password:="1234"
Worksheets("Contract").PrintOut Copies:=1, Collate:=True

Call Green

Application.ScreenUpdating = True
End Sub

Sub Green()
ActiveWorkbook.Unprotect Password:="4321"
ActiveSheet.Unprotect Password:="1234"
'===============================================
Dim CELL As Range, tempR As Range, rangeToCheck As Range
Cells.Select
Cells.Interior.ColorIndex = -4142
For Each CELL In Intersect(Selection, ActiveSheet.UsedRange)
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If

Next CELL
If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
End
End If

'Select qualifying cells
'TempR.Select
tempR.Interior.ColorIndex = 4 'GREEN
' =================================================
ActiveSheet.Protect Password:="1234"
ActiveWorkbook.Protect Password:="4321"

End Sub

Thanks Jim:

1: Could you please explain your last statement?
The sheets I use this code on are constantly getting rows inserted, deleted
and moved. When I have "enough" significant changes, I send this latest
version contract(s) to my salesmen. They do not have the capability of adding
or deleting rows - they can only work with the coloured cells - the rest are
locked and there is w/sheet and w/book protection in place.

2: ALSO: I regularily use the code ( I think I got it from 'Contextures')
to clean up (delete) the unused range.
The Question then, Is there other things happening with w/sheets that are
constantly being manipulated (add rows, delete rows, move rows), that one
should (consider) totally rebuilding the sheets from scratch, on a "regular"
basis?
FYI: I am almost done "cleaning up" all my code modules (copy to text,
make new module with new name, copy text into new module, etc.)

Jim, I thank you in advance for your consideration of the above items.


"Jim Thomlinson" wrote:

When you put the code into the addin you need to be very explicit in your
references. Make sure theat you reference the activesheet. I also cleaned up
the code just a bit... I removed the selects and I removed the stand alone
End (A stand alone end will clear all global variables). One thing to note is
taht this code could be a problem if the used range gets messed up. That can
be fixed latter if you wish...

Sub Green()
Dim CELL As Range, tempR As Range, rangeToCheck As Range

With ActiveSheet
ActiveWorkbook.Unprotect Password:="4321"
.Unprotect Password:="1234"
'===============================================
.Cells.Interior.ColorIndex = -4142
For Each CELL In .UsedRange
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If
Next CELL

If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
Else
tempR.Interior.ColorIndex = 4
End If

' =================================================
.Protect Password:="1234"
ActiveWorkbook.Protect Password:="4321"
End With
End Sub
--
HTH...

Jim Thomlinson


"BEEJAY" wrote:

The main part of the following code works great when I run it
from an icon my machine.
Now trying to add this code to an add-in to be distributed.
When I try F8, it does go into Call Green, but just loops and loops, without
turning any cells green.
When I run the Print Contracts from my custom menu, based on the worksheet
and workbook protection status, the code seems to skip the Call Green and End
sub.
Can anyone please check and advise what I am missing here?

Option Explicit
Sub PrintContract()
' PrintContract Macro
' Keyboard Shortcut: NONE

Application.ScreenUpdating = False

Worksheets("Contract").Select
ActiveSheet.Unprotect Password:="1234"

Cells.Select
Selection.Interior.ColorIndex = -4142

ActiveSheet.Protect Password:="1234"
Worksheets("Contract").PrintOut Copies:=1, Collate:=True

Call Green

Application.ScreenUpdating = True
End Sub

Sub Green()
ActiveWorkbook.Unprotect Password:="4321"
ActiveSheet.Unprotect Password:="1234"
'===============================================
Dim CELL As Range, tempR As Range, rangeToCheck As Range
Cells.Select
Cells.Interior.ColorIndex = -4142
For Each CELL In Intersect(Selection, ActiveSheet.UsedRange)
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If

Next CELL
If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
End
End If

'Select qualifying cells
'TempR.Select
tempR.Interior.ColorIndex = 4 'GREEN
' =================================================
ActiveSheet.Protect Password:="1234"
ActiveWorkbook.Protect Password:="4321"

End Sub