MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Auto Macro?


Posted by Rob on August 01, 2001 1:30 PM

This code was given to me by Quarlton, but needs a button to be executed. Is there a way to modify this code to have it evaluate as i input data, and without a button? Thanks, heres the code:

Sub Rob()
'start cell
Range("A1").Select

'find last row of data
Dim LastRow
LastRow = ActiveCell.SpecialCells(xlLastCell).Row

'loop
Do While Selection.Row < LastRow + 1
'test
If Selection <> "" Then
'apply border
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
'bold
Selection.Font.Bold = True
'add formula to "C"
Selection.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
End If
'move down one row
Selection.Offset(1, 0).Select
Loop

'clean end
Range("A1").Select

End Sub


Posted by Cory on August 01, 2001 1:50 PM

Maybe you can try putting it in the worksheet_change or _calculate event instead of a button_click event. (right-click on the sheet tab and view code; change "SelectionChange" to "Change" or "Calculate")

Cory

Posted by Rob on August 01, 2001 2:44 PM

I tried putting this in the worksheet_change like you suggested cory, but now it goes into an infinite loop when I enter values into 2 rows...HELP! :)

Thanks

Posted by kevin on August 01, 2001 2:46 PM

Posted by Kevin Skinner on August 01, 2001 2:48 PM

Give this a try

Right-click on the excel icon right next to the file menu and choose view code, and insert this command line along with your macro there:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
(insert your macro here)

Posted by Cory on August 01, 2001 3:00 PM

Rob,
I changed your code a little bit and, though I haven't tested it, I think it should work. I'm off to class, but if it doesn't work just email me and I'll look at it tonight when I have more time...:-)

Cory

Sub Rob()
'start cell
Range("A1").Select

Do until activecell = empty
If Selection <> "" Then
'apply border
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
'bold
Selection.Font.Bold = True
'add formula to "C"
Selection.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
selection.offset(1,0).select
End If
Loop

'clean end
Range("A1").Select

End Sub

Posted by Rob on August 01, 2001 3:28 PM

Thanks, but no luck there :(

Posted by Rob on August 01, 2001 3:29 PM

No Luck here either :(

I tried this cory, but it didn't work :( I do appreciate the help, I'll drop you an email for more help, thanks :)

Posted by Ivan F Moala on August 01, 2001 9:21 PM

Try;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Columns.Count < 2 Then
If Target <> "" Then
With Target
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
'bold
.Font.Bold = True
'add formula to "C"
.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
End With
End If
End If
End If

End Sub

Ivan This code was given to me by Quarlton, but needs a button to be executed. Is there a way to modify this code to have it evaluate as i input data, and without a button? Thanks, heres the code:

Posted by Rob Jackson on August 02, 2001 12:28 AM

As the code seems to scan the entire column and make these changes, why not just run it on the close event or when the book loses focus.
Alternatively get it so that it just changes the cell you are editing (take out the loop).

Just a thought

Rob