Excel Combo Boxes

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
jtoweryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zmey2Commented:
I think you can read combobox state before changes and set it's value after.
0
jtoweryAuthor Commented:
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
Zmey2Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve KnightIT ConsultancyCommented:
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
geofflilleyCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.