TWO THINGS! One Protection ... other Macros

J

Joe Cramblit

PROTECTION

I realize that Excel 2002 workbook protection isn't really stron
security but I'm having a peculiar problem.

Emailing a workbook kills the protection? It's just not there when i
arrives in the other persons inbox? Any ideas? Is this normal?
Using Outlook Express to send it if that has any bearing.

MACROS

I've created a simple macro for clearing the cell contents of an orde
form. Because of formulas the clearing has to be specific cells in
column .... can't just wipe the entire column.

You can tell I'm a whiz. Can this macro be set up to clear cell
corectly when a user deletes a row? In looking at the code the cel
references remain the same however the cell numbers have changed as
result of the row(s) deletion. In other words can the macro adjust t
the worksheet?

Thanks for any help
 
J

jeff

Hi, Joe,

I'm not sure on your first question, but as to your 2nd,
yes, I'd think the clearing can be selective - are
there criteria that the macro can use? such as, for
example, you're clearing cells in Column C, are
there titles/headings in the same row but in another
column ("A"?) that it can key on?

Maybe a small snippet of your spreadsheet would help us.
Regards,
jeff
 
J

Joe Cramblit

Hi Jeff...

Here's a piece of the macro. The rest of it is "more of the same".
If the image I uploaded is visible I need to clear cell contents tha
are yellow and avoid all the cells above and below (white). It work
fine but now my client wants to be able to delete a row and still hav
this work as planned. I'm not sure it can happen.

Range("B33:B36").Select
Selection.ClearContents
Range("B38:B39").Select
Selection.ClearContents
Range("B41").Select
Selection.ClearContents
Range("B43:B44").Select
Selection.ClearContents
Range("B49:B52").Select
Selection.ClearContents
Range("B54:B55").Select
Selection.ClearContents
Range("B57").Select
Selection.ClearContents

[image: http://salesdepartment.com/tlabexcel.gif
 
J

jeff

Hi again,

you may want to play with the '36' which is the
colorindex of the light yellow I used. this macro
just runs thru all cells in column B looking for
a yellow in A (...offset(0,-1)..) and clears B
if found. This lets you get rid of all your
individual assignments.

good luck
jeff

Sub clearYellows()
Dim r As Range
Set r = Range("$B:$B")
For Each c In r
If c.Offset(0, -1).Interior.ColorIndex = 36 Then
c.ClearContents
End If
Next c
End Sub
 
J

Joe Cramblit

I'll try it. Never did I think you could run a macro using color as
criteria.

Thanks
 
J

Joe Cramblit

That worked! Color 36 is evidently right.

Even though I've used Excel from it's virtual birth I've never move
beyond worksheet logic formulas. So, I know very little about VB.

I don't want to push my luck (if you're too busy just say so) but tha
solved a quantity column issue 100%. There are several othe
cells/ranges that it would be very helpful to clear in this sam
manner. Your formula centers on the "B" column. I wondered if thes
cells and ranges could be incorporated in the "CLEAR" using the sam
COLOR 36.
Can I maybe include them in the "$B:$B" range with a separator and mak
the left adjacent cells color 36?

Or would it be more manageble to use another Command to clear the rest
Maybe they're just too scattered to work in or put in one Command.

U4:U7
F12:F18
T12:T18
C21
C103
K103
O103

Thanks very much for your help.

Jo
 
J

jeff

Hi,

Glad we're getting there. I think I may not understand
your last wish. the following would replace what I
sent before; it simply clears cells in columns C,F,K,O,T,U
in the same row if Ax is yellow just like it clears
the B cell.

Perhaps you're after something else? post back - I can
make the adjustment.

jeff
--------------------------
Sub clearYellows()
Dim r As Range
Set r = Range("$B:$B")
For Each c In r
If c.Offset(0, -1).Interior.ColorIndex = 36 Then
c.ClearContents
c.Offset(0, 1).ClearContents ' clear C
c.Offset(0, 4).ClearContents ' clear F
c.Offset(0, 9).ClearContents ' clear K
c.Offset(0, 13).ClearContents ' clear O
c.Offset(0, 18).ClearContents ' clear T
c.Offset(0, 19).ClearContents ' clear U
End If
Next c
End Sub
 
J

Joe Cramblit

Hi Jeff...

I "tried" to delete that extra favor I asked because I think I figure
it out. Because I had cells scattered around I was trying to clear
moved stuff around and inserted columns where I could color th
adjacent cells to be able to use this approach.

It seems to work. As my dad used to say "even a blind hog finds an ea
of corn once in awhile"

Basically just goes down each column looking color 36 and deletes th
cell to the immediate right. It works "I think" because your Offse
formula actually applies to all of them. Each is a -1 from the colum
containing data. I numbered the ranges because it seemed to tak
forever. Then it dawned on me that it was searching every single row o
the sheet. It runs a lot (25 times) faster just searching the applicabl
rows.

Look this over and tell me if you see any bomb in this waiting to g
off. And below this ... can I ask a totally unrelated question.

Private Sub CommandButton3_Click()
Dim r As Range
Set r
Range("$C1:$C150,$G1:$G150,$V1:$V150,$X1:$X150,$L1:$L150,$P1:$P150,$AD1:$AD150")
For Each c In r
If c.Offset(0, -1).Interior.ColorIndex = 36 Then
c.ClearContents
End If
Next c
End Sub

===NEW TOPIC===

This ridiculous spreadsheet automatically saves the file (giving it
generated file name) and prints it with one button. Another butto
saves it, prints it AND emails it. This guy has really been smoking th
brain of someone who knows little about VB.

Believe it or not after stumbling around and also finding other "wis
ones" like you I got all this set up. There's only ONE thing that i
brutally simple that I cannot figure out how to do.

I can't lock in a save path! I've tried 100 combinations! These script
can actually save the files generated in any one of at least 3 places
It literally saves to the last place you were.

Here is the SAVE & PRINT script. The other one is almost the same. if
have enough space left. By the way this is probably "junk coding". I'
sure there are better ways to write this. Some of this looks redundan
even to me. If you want to streamline it .... wonderful. It doe
actually work as is.

It's that "Save As Filename" line that has been driving me nuts
Nothing that looks anywhere near normal to me works.

Another thing I simply do NOT understand is that I cannot move tha
"Print Range" statement down into that "With .. Page SetUp" section
Bombs everytime?


Sub CommandButton1_Click()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "mm-dd-yyyy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Worksheets("Order").PageSetup.PrintArea = "$A1:$AE113"
With Worksheets("Order").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
ActiveWorkbook.Sheets(1).PrintOut _
Copies:=1
Set wb = ActiveWorkbook
With wb
.SaveAs Filename:=Range("G12").Value & "_TLAB" & " " & strdat
& ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub


Again thanks for your help
 
D

Dave Peterson

Where did you want to save your file? In the same folder as the the workbook
containing the code?

if yes:

dim myPath as string
mypath = thisworkbook.path
'or whatever path you want, even:
'mypath = "c:\myfolder1\myfolder2"

'check typing
if right(mypath,1) <> "\" then
mypath = mypath & "\"
end if

'later...
'this
..SaveAs Filename:=Range("G12").Value & "_TLAB" & " " & strdate & ".xls"
becomes
..SaveAs Filename:=mypath & activesheet.Range("G12").Value _
& "_TLAB" & " " & strdate & ".xls"

(I added activesheet to the range("g12") portion. Is that the correct
worksheet?)
 
Top