Macro Activation

[Guest]

Hi, I am relatively new to programming in Excel. Everything I know and don't
know is a result of being self taught through reading and playing around in
visual basic in Excel.
I am trying to make a macro run automatically in a spreadsheet when the user
moves from a particular range of cells. I have recorded the macro as a
module. I realize that the code needs to be placed in the worksheet for this
macro to work. I am just not exactly sure how to generate the necessary code
to activate the macro.

My range of cells is named. When the user fills out their information within
the named range of cells and they navigate from that range, the macro would
initiate.

Any suggestions or help would greatly be appreciated.

 

[Rick Rothstein \(MVP - VB\)]

I think the following code framework will let you do what you want.

 

[Chip Pearson]

The following Worksheet_Selection change will run the code you substitute
for the ' your code here lines whenever you exit the range named "TheRange".
It runs only when the selection move from within the range to outside of the
range. It doesn't run when you click repeated outside the range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static NotInLastTime As Boolean
If Target.Cells.Count > 1 Then
Exit Sub
End If

If Application.Intersect(Target, Range("TheRange")) Is Nothing Then
If NotInLastTime = False Then
'''''''''''''''''''
' your code here
'''''''''''''''''''
NotInLastTime = True
End If
Else
NotInLastTime = False
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rick Rothstein \(MVP - VB\)]

<<Damn, I hit Ctrl+Enter by mistake>>

I think the following code framework will let you do what you want.
Copy/Paste it into the code window for the sheet with your named range on it
(right-click the sheet's tab on the bottom and select View Code to get to
the VB editor window for that sheet)...

Dim WasInsideRange As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NamedRange As Range
Set NamedRange = Range("MyNamedRange")
If Application.Intersect(NamedRange, Target) Is Nothing Then
If WasInsideRange Then
MsgBox "Moved out of named range"
End If
WasInsideRange = False
Else
WasInsideRange = True
End If
End Sub

Just replace the Msgbox statement with the code you want to run when the
user leaves the named range. The code assumes your named range is called
MyNamedRange... change it to match your actual named range's name. The
WasInsideRange variable and testing being done on it is an attempt to run
your code only one time; otherwise, without it, the code would run each time
the user click a cell outside of the named range. As it is now, each time
the user click's into your range, the WasInsideRange variable is set to
True. As long as the user stays within the named range, nothing else
happens. As soon as they click outside the range, the WasInsideRange
variable is reset and your code is run. Any other clicks outside the range
will not run your code again. However, if the user clicks inside the named
range a second time, and then clicks outside it once more, your code will be
re-run.

Rick

 

[Guest]

Thanks for the help.It worked the first time. As I mentioned in the original
post, I am a rookie at this. I basically use the macro recorder to create
code and the view it to try and understand it.

The code you provided works great, although a have a bug that I am not sure
how to fix. I will try to explain.

The code you provided activates a macro that initiates a message box. If the
user clicks yes, this activates another macro that copies the contents of the
cell within the range as well as an adjacent cell. When this macro runs it
reactivates the code behind the worksheet forcing the user to click no this
time to alleviate the code running again and again. Below is the some of the
code I used.

First the Worksheet code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static NotInLastTime As Boolean
If Target.Cells.Count > 1 Then
Exit Sub
End If

If Application.Intersect(Target, Range("Vendor1")) Is Nothing Then
If NotInLastTime = False Then
Application.Run "'P.O. Log.xls'!IntiateMsgbox"
NotInLastTime = True
End If
Else
NotInLastTime = False
End If
End Sub
--------------------------------------------------------------------------------------
The Code for the initiate Msgbox is 2 Parts. the first is like this.
Sub IntiateMsgbox()
'
' IntiateMsgbox Macro
'This macro initiates the message box and when yes is clicked it creates the
new purchase order.
Answer = MsgBox(Prompt:="Create New Purchase Order?", Buttons:=vbYesNo)
If Answer = vbYes Then
Application.Run "'P.O. Log.xls'!SetupPO"

End If
End Sub
-----------------------------------------------------------------------------------------------
The code for SetupPO looks like this.
Sub SetupPO()
'
' SetupPO Macro
' Macro recorded 10/11/2007
'This macro wo\rks to open the template, label it and save it correctly. Do
not change it.
Workbooks.Open Filename:="G:\RickB\P.O. & Sub\New PO.xls", UpdateLinks:=3
Windows("P.O. Log.xls").Activate
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.Copy
Windows("New PO.xls").Activate
Range("F13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("P.O. Log.xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("New PO.xls").Activate
Range("C16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("N17").Select
ActiveWorkbook.SaveAs Filename:="G:\RickB\P.O. & Sub\" &
ActiveCell.Text, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
------------------------------------------------------------------------------------------------
I suspect the problem originates in the SetupPO code. There is probably a
better way of creating the code needed to perform this operation. As I said
in the beginning, your solution works fine. it is somethin that I am doing
wrong on my end.

Thanks,

 

[Guest]

Thanks Rick - I tried your code also and it worked fine the first time. I
have since posted a reply to Chip's solution that created another little bug.
Your code and Chip's ran almost identical. Thanks again fro all your help. it
is greatly appreciated.