Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How To Detect Entry and Respond

Posted on 2003-02-25
7
Medium Priority
?
195 Views
Last Modified: 2010-05-01
Is there a way to detect an entry on XL and automatically respond with different macros assigned to different cell locations?  That is, say I have a macro for cell A1, another for cell D6.  When I make an entry on A1, macro1 runs (without any other user action).  Make an entry on D6, macro2 runs.  Enter on any other cell and nothing happens.  Looks like magic to the user.
0
Comment
Question by:Mike Caldwell
  • 4
  • 3
7 Comments
 
LVL 2

Expert Comment

by:NPluis
ID: 8017849
Use something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Excel.Run "Macro1"
End If

If Target.Address = "$D$6" Then
    Excel.Run "Macro2"
End If

End Sub
0
 
LVL 1

Author Comment

by:Mike Caldwell
ID: 8018376
Forgive my newness, but I can't "see" this macro.  I made a quickie macro1 and macro2 (just enter a couple of numbers) so I could see the proposal work, but Worksheet_Change does not show up on my list of macros.  I can see it under "project view", and have it in a module folder for this spreadsheet (I don't want it in PERSONAL.XLS), but still doesn't show up, so how do I launch it?
0
 
LVL 2

Expert Comment

by:NPluis
ID: 8018922
You can just enter it under 'Sheet1' in the VBA editor. (alt+F11) for the workbook you want it in.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Mike Caldwell
ID: 8019885
Duh; part of my problem is that a 'return' without having typed something first is not considered a change so nothing happened.  As soon as I enter '1' it ran fine.  So  justa couple of tweek questions:

1.  Can I arrange for 'enter' to kick it off, whether or not something was actually changed?

2.  I see that it only affects Sheet 1.  What may I do to have it respond to any sheet in the workbook?

3.  More generic: how to make a macro autolaunch when the workbook is opened, before doing anything else?

Thanks for your patience.  I'm more of an 8-bit microcontroller assembly language programming kinda guy.
0
 
LVL 1

Author Comment

by:Mike Caldwell
ID: 8019938
Looks like a "Public" declaration instead of "Private" makes it applicable to all sheets.  Does this also make it available to other workbooks (not what I want), or would that only happen if it were in PERSONAL.XLS??
0
 
LVL 2

Accepted Solution

by:
NPluis earned 400 total points
ID: 8023582
1 You should evaluate the value of target like:
If Len(Target) = 0 then Exit Sub

2 You should enter the code in the following event in ThisWorkbook:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

End Sub

This way it works in every sheet of your workbook. You can check the Sh value to see which sheet is changed.

3. In the workbook you have the Open Event, place your code in there like:
Private Sub Workbook_Open()
   Excel.Run "Macro2"
End Sub

4 Changing the Private to Public doesn't make it applicable to all sheets or workbook. It will be available in your code from another sheet but it doesn't respond to the events
0
 
LVL 1

Author Comment

by:Mike Caldwell
ID: 8023591
Think I've got it now; thanks for the extended help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month12 days, 3 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question