Solved

Excel 2007: Testing for ComboBox1 versus ComboBox2

Posted on 2011-09-07
7
237 Views
Last Modified: 2012-05-12
I have the folloing code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "combo change"
End Sub

displaying a single message box for a change to either ComboBox on the page.

Questions:

1) How do I make a unique message (event) for each ComboBox?
2) How do I make this code apply to all pages in the entier Excel spreasheet?

Thanks.
0
Comment
Question by:newbieweb
  • 4
  • 2
7 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 167 total points
ID: 36497522
You can try using and Handler like the example provided by rorya on this question here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27181728.html

jppinto
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 333 total points
ID: 36498067
FYI, the SelectionChange event responds to you selecting a different cell, not to a change in the value of anything.
0
 

Author Comment

by:newbieweb
ID: 36498197
> FYI, the SelectionChange event responds to you selecting a different cell, not to a change in the value of anything.

Right. I was hoping for some way to parse the input parameter to the event and be able to then call two different sections of the code, based on (for example) the row/column where the control is located.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:newbieweb
ID: 36498541
I downloade the test spreadsheet from rorya's link, but can not et the message box to open. I enabled Macro's and also checked "Trust access to the VBA project object model".  But no success. Nothing opens when I click the checkboxes.

So I am a bit lost here...

0
 

Author Comment

by:newbieweb
ID: 36498737
I mean is there a way to extract the information out of the parameter Target?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "combo change to Worksheet 2"
End Sub

I'd like to say

MsgBox "combo change to Worksheet 2 by " + Target.Name
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 333 total points
ID: 36498777
The Target argument is the range you just selected. Not sure what that is going to tell you. I think you really want the Change event instead, where the Target argument is the changed range.
0
 

Author Closing Comment

by:newbieweb
ID: 36498811
Thanks. I think I was confused. Peace.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now