Combo Box Reading InCorrect Selection

T

thenum1roc

Board Regular
Joined
Jun 2, 2002
Messages
77
Hi,

I am trying to utilize a combo box that is just placed in an excel worksheet.

I have two selection options in the combo box:
A. "Business Optimization"
B. "Feedstock Optimization"

The issue that I am having is that if I have "Business Optimization" or "Feedstock Optimization" selected when the workbook is opened or when I select the same selection; ie select Business Optimization right Business Optimization or select Feedstock Optimization right after Feedstock Optimization; the 2nd selection doesn't seem to register in vba.

Initially I used the "CHANGE EVENT" for the combo box but then I chose to use the "CLICK EVENT" for the combo box.

Of the event handlers:

Click, DblCLick, DropButtonClick, Error, Got Focus, KeyDown, KeyPress, KeepUp, LostFocus,MouseDown, mouseMove, MouseUp, BeforeDragover, beforedroporpaste, Change


The current code I have is the following:

Private Sub cmd_busoptMode_Click()
If Sheets("Overview").cmd_busoptMode.Value = "Business Optimization" Then
BusOptMode = True
MsgBox "I'm in Max mode, Bus Opt Mode is:" & BusOptMode
ElseIf Sheets("Overview").cmd_busoptMode.Value = "Feedstock Optimization" Then
BusOptMode = False
MsgBox "I'm in Max mode, Bus Opt Mode is:" & BusOptMode
Else
MsgBox "Select Business Optimization or Feedstock Optimization"
End If
End Sub


Any advice is appreciated.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Perhaps this will work. Making BusOptMode a Public boolean variable might also help
Code: 
Private Sub cmd_busoptMode_Change()
    With Sheets("Overvies").cmd_busoptMode
        If .SelectionIndex < 0 Then
            MsgBox "Select Business Optimization or Feedstock Optimization"
        Else
            BusOptMode = (.Value = "Buisness Optimiztion")
            MsgBox "I'm in Max mode, Bus Opt Mode is:" & BusOptMode
        End If
    End With
End Sub
 
Upvote 0
An additional thought, to help with the persistance of BusOtMode, you could cast it as a function.

Function BusOptMode() As Boolean
BusOptMode = (Sheets("Overvies").cmd_busoptMode.Value = "Buisness Optimiztion")
End If

Anther version of this thought, If there are only two choices, why not a ToggleButton or some other binary control?
 
Upvote 0
How is BusOptMode set to a True or False in your code. It is a boolean in my code based upon the Feedstock Opt (BusOptMode=False) or Business Opt (BusOptMode=True) selection.

Could you explain your code I did not get it?
 
Upvote 0
Code: 
BusOptMode = (Sheets("Overview").cmd_busoptMode.Value = "Buisness Optimiztion")
sets the value of BusOptMode to either True or False
 
Last edited:
Upvote 0
Thanks. i am getting the same issue. If I repeat the selection of the vba code doesn't READ the message.
 
Upvote 0
Selecting the same item from a Combobox twice in a row triggers neither the Change nor the Click event. Which is why I suggested the function, it doesn't depend on an event to set its value, it reads the combobox and determines the value when called.

Similarly a ToggleButton (or Option Buttons) would have their value ready to read when needed.

Setting a boolean global variable with a ComboBox_Change (or Click) routine is an unnessesary complicaiton. Just test if ComboBox.Value = "Buisness Optimisztion" whenever you would read the value of the global variable.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
H
  • Question
Calendar userform-text selection
Replies
1
Views
6K
hissonrr
H
E
  • Question
Passing value from a combo box control created at run time to a worksheet
Replies
0
Views
772
envy86
E
A
  • Question
Cascading Combo Box’s on worksheet (Excel-2003)
Replies
0
Views
916
AikonEnohp
A

Forum statistics

Threads
1,215,737
Messages
6,126,570
Members
449,318
Latest member
Son Raphon

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top