Solved

Excel 2007: Testing for ComboBox1 versus ComboBox2

Posted on 2011-09-07
7
284 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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