help with macro
Posted by Jimmy on January 05, 2001 1:37 PM
I have included the macro below. This is my first real effort in writing these things. I actually recorded it first and then kinda played around with it until I got it to do what I want, but I have a couple of questions.
1 - I now have it working on a button click, but would like to have it fire automaticly. I presently have a comment box which is know more that merged cells(A84:I93) named supcomments. This allows only ten lines of text to be entered and be printed correctley. This is the reason for the macro. My questions is can it be made to fire automaticly when this limit of ten lines is reached or better yet when the user exits the merged cells or I have placed a print button on the same form so the user can print the completed form, could another small macro be attached to it so when they print the form it will fire this macro and then print.
2. Since I am really new at this I would appreciate one of the gurus taking a look and seeing if this can be done a better way(i am sure there is), and if so how?
Thanks in advance
' Button5_Click Macro
' Macro recorded 1/5/01 by Jimmy Hickey
Sheets.Add , After:=Sheets("Eval Form")
Sheets("Sheet6").Name = "Supervisor Comments"
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
Selection.PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:=False _
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = True
Selection.Locked = False
Selection.FormulaHidden = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "SEE ATTACHED SHEET"
With ActiveCell.Characters(Start:=1, Length:=18).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic