MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Sub Button5_Click()
'
' Button5_Click Macro
' Macro recorded 1/5/01 by Jimmy Hickey
'

'
Sheets.Add , After:=Sheets("Eval Form")
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Supervisor Comments"
Sheets("Eval Form").Select
Range("A83").Select
Selection.Copy
Sheets("Supervisor Comments").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Sheets("Eval Form").Select
Range("supcomments").Select
Selection.Copy
Sheets("Supervisor Comments").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Range("A2:I40").Select
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = True
End With
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("Eval Form").Select
Range("supcomments").Select
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
End With
Sheets("Eval Form").Select

End Sub


Posted by Dave on January 05, 2001 5:40 PM

Button5_Click Macro Macro recorded 1/5/01 by Jimmy Hickey

Hi Jimmy

Try this modified version:

Sub Button5_Click()
Application.ScreenUpdating = False
On Error GoTo SheetExists
Sheets.Add(After:=Sheets("Eval Form")).Name = "Supervisor Comments"
With Sheets("Eval Form")
.Range("A83").Copy Sheets("Supervisor Comments").Range("A1")
.Range("supcomments").Copy Sheets("Supervisor Comments").Range("A2")
End With
Application.CutCopyMode = False
With Sheets("Supervisor Comments").Range("A2:I40")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
.Locked = False
End With

Range("supcomments") = "SEE ATTACHED SHEET"


With Range("supcomments").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
End With
Sheets("Eval Form").Select
Application.ScreenUpdating = True
Exit Sub
SheetExists:
Application.DisplayAlerts = False
ActiveSheet.Delete
MsgBox "Supervisor Comments already exists"
Application.ScreenUpdating = True
Application.DisplayAlerts = False
End Sub


Dave

OzGrid Business Applications

Posted by Jimmy on January 08, 2001 5:29 AM

Thanks Dave I will give it a try.

Posted by Jimmy on January 08, 2001 1:52 PM

More help Please Dave

When I run this macro I get the message box about the "Supervisor comments already exsist" even if the comment box is empty. I suspect it is because if cell A83 which contains the heading "SUPERVISOR COMMENTS" then the range of cells which actually contains the comments are A84 - I93 (merged cells). I worked past this part of the code (just marked them as comments, so I could check the rest of the code) and then get error messages in the part of the code which copies and paste into the new sheet (A83 and supcomments). The error says something to the effect "Cannot change part of a merged cell". Sorry it is all proabably something real simple, but I can't seem to figure it out. Thanks for any more help you can give me.