?
Solved

Excel Combo Boxes

Posted on 2003-03-05
7
Medium Priority
?
330 Views
Last Modified: 2008-03-10
OK...HERE'S MY QUESTION:  I HAVE A WORKSHEET USING COMBO BOXES WHERE THE RANGE IS IN A SEPERATE WORKSHEET.  THIS WORKSHEET CONTAINING THE RANGE IS IN TURN LINKED TO ANOTHER WORKBOOK THAT IS PERIODICALLY UPDATED.  WHENEVER THESE UPDATES ARE ACCEPTED, THE RANGE FOR THE COMBO BOX IS ALSO UPDATED, SHIFTING THINGS AROUND A BIT SO THAT WHATEVER WAS ORIGINALLY "SELECTED" IN THE COMBO BOX SHIFTS AND CHANGES TO.  I UNDERSTAND THAT WHEN YOU SELECT SOMETHING IN THE COMBO BOX IT SHOWS TEXT OF WHATEVER YOU SELECTED, BUT IT ACTUALLY RETURNS THE VALUE FOR THE ROW NUMBER YOUR SELECTION IS ON.

IS THERE ANYWAY TO HAVE EXCEL STAY WITH THE ACTUAL "SELECTED" VALUE (THE ONE THAT THE USER SEES) AND NOT THE ROW VALUE SO THAT WHEN THE RANGE IS ADJUSTED THE USER "SELECTION" WON'T CHANGE?

THANKS
JT
0
Comment
Question by:jtowery
[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
7 Comments
 
LVL 4

Expert Comment

by:Zmey2
ID: 8071780
I think you can read combobox state before changes and set it's value after.
0
 

Author Comment

by:jtowery
ID: 8072109
HOW DO YOU READ BEFORE THE CHANGES?  DOESN'T EXCEL AUTOMATICALLY UPDATE EVERYTHING WHEN YOU OPEN THE WORKBOOK AND UPDATE THE LINK?  THE UPDATE THEN SHIFTS ALL OF THE SELECTIONS THAT WERE PREVIOUSLY MADE IN THE COMBO BOXES BECAUSE THE RANGE IS CHANGED.  THE ROW NUMBER OF THE ITEM YOU SELECTED DOES NOT CHANGE, BUT WHAT IS NOW IN THAT ROW DOES CHANGE DUE TO NEW INSERTIONS AND CHANGES IN THE RANGE.  FOR EXAMPLE...IF I USED THE COMBO BOX TO SELECT THE PHRASE "WORK ORDER" WHICH IS IN ROW 7 OF THE RANGE AND THEN MAKE CHANGES TO THE RANGE SO THAT "WORK ORDER" IS NOW IN ROW 8, THE COMBO BOX STILL PICKS UP WHAT'S IN ROW 7.  I NEED TO FIND A WAY TO HAVE THE COMBO BOX STAY WITH "WORK ORDER," WHEREVER IT MOVES, AND NOT STAY WITH ROW 7.

MAYBE THERE'S SOMETHING BESIDES COMBO BOXES THAT WILL WORK BETTER?

THANKS FOR YOUR HELP!  
0
 
LVL 4

Accepted Solution

by:
Zmey2 earned 200 total points
ID: 8072180
This is from Excel built-in help:

BeforeUpdate event

Occurs before data in a control is changed.
Syntax
Private Sub object_BeforeUpdate( ByVal Cancel As MSForms.ReturnBoolean)

The BeforeUpdate event syntax has these parts:

Part     Description
object     Required. A valid object.
Cancel     Required. Event status. False indicates that the control should handle the event (default). True cancels the update and indicates the application should handle the event.
Remarks

The BeforeUpdate event occurs regardless of whether the control is bound (that is, when the RowSource property specifies a data source for the control). This event occurs before the AfterUpdate and Exit events for the control (and before the Enter event for the next control that receives focus).
If you set the Cancel argument to True, the focus remains on the control and neither the AfterUpdate event nor the Exit event occurs.

Copyright(c) 1996 Microsoft Corporation.



-- so, you should create code for BeforeUpdate event of combo box, remember the value and then, in afterupdate event restore combo box value.

You can find samples in built-in help.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8072999
I suppose you could always use Data Validation list boxes instead as they at least return text... or use the combo box events to write the text to a relevant cell for use and then clear the combo box choice.

Steve
0
 
LVL 1

Expert Comment

by:geofflilley
ID: 10032422
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
delete question, points awarded to Zmey2
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Geoff Lilley
EE Cleanup Volunteer
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 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