Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2007: Testing for ComboBox1 versus ComboBox2

Posted on 2011-09-07
7
Medium Priority
?
293 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
[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
  • 2
7 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 668 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 1332 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
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.

 

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 1332 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 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