?
Solved

How To Detect Entry and Respond

Posted on 2003-02-25
7
Medium Priority
?
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month11 days, 7 hours left to enroll

752 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