Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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
0
jtowery
Asked:
jtowery
1 Solution
 
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
 
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now