Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
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 B |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
I think the following code framework will let you do what you want.
"Rick B" wrote in message ... 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 B |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
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 B" wrote in message ... 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 B |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
<<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 "Rick B" wrote in message ... 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 B |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
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, -- Rick B "Chip Pearson" wrote: 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 B" wrote in message ... 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 B |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Activation
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. -- Rick B "Rick Rothstein (MVP - VB)" wrote: <<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 "Rick B" wrote in message ... 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 B |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro activation | Excel Programming | |||
macro on Sheet activation | Excel Programming | |||
Excel macro activation. | Excel Programming | |||
Excel macro activation. | Excel Programming | |||
Excel macro activation. | Excel Programming |