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
> >
|
Similar Threads
