Launching a macro from an cell 1

[WalkerEvans]

I have given up trying to work this one out for myself. There must be an answer to this somewhere in the Excel Help files, but many days of searching there (and here) have left me with nothing but a dull ache behind my eyes.

Is it possible to launch a macro from a cell? One of my users approached me for a simple change to a macro so that the user could add more lines. Writing the changes to the macro was easy, but I'd like to set it up so that, when the user enters the last cell, she would be given a choice whether or not to add more lines. A "Yes" answer would launch the macro.

If this can't be done (and I'm beginning to believe it can't) I can always give her a command bar button, but it would be neater to automate if possible. Any ideas out there?

Thanks for any help you folks can give me on this!

 

[sugarflux]

Use the 'Worksheet_SelectionChange' function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 10 Then 'change 10 to last row of data
myresponse = MsgBox("Insert New Row?", _
vbYesNo + vbQuestion, "Title")
If myresponse = vbYes Then
ActiveCell.Offset(1, 0).Rows.Insert xlDown
End If
End If
End Sub

In this sub check whether Target is the last cell, if so, display an msgbox. If result = Yes then insert new row,

Hope this helps,

sugarflux

 

[WalkerEvans]

Sugarflux,

Like that old commercial for TV Magic Cards, "Its simple once you know the secret!"

Thanks for sharing the "secret" with me. After a little tweaking to fit my specific circumstances this works beautifully.

 

[SkipVought]

Hi,

If I were the user, this would be VERY ANNOYING, having to answer a message box each time I finished entering data in a row. YUK!

Are there formulas that need to be propogated into the new row of data? If so, just have the code put the stuff in there once the user has added a value to a new row.

Skip,

_[red][/red]

 

[WalkerEvans]

Skip,

This was done at the request of the most frequent user of this worksheet. As noted, a bit of tweaking was necessary to get this to do what she needed.

My macro is inserting an addtional five lines at a time into the worksheet, propagating each with all the necessary formulas. Since there is a lot of date manipulation involved, some of the formulas look a bit like a mad scientist's blackboard scribbles.

I concur that the "one row at a time" idea would be frustrating, and I offered to do a ten-row insert, but my primary user said five additional lines would cover most situations; if she needs more she has no problem hitting the "Yes" button again.

Thanks for the feedback!