I am new user. Any help is appreciated.
I have column with drop down selection - If a certain value is chosen, I want to control the format of the next cell.
The Worksheet_Change event seems to capture this event if the user selects the change from the drop-down, but not if the user types the value in the cell and presses <enter>.
How can I make sure that I capture this in both cases
VB and Excel - Change Event
_Bosch_
Here's some additional info from our support engineer. He also sent a sample in Excel. If you'd like to see that file please contact me at budsup@microsoft.com.
First of all, I’d like to confirm the dropdown list mentioned by our buddy a Validation field or a Windows combo box control. Actually, I did some research on both options. For Validation Field, Worksheet_Change event can capture the events. If we select the value from the list, the event is fired directly. But, if we type the valid value in the cell and press <Enter> (left the current cell), the event will also be fired. Since I can not repro our buddy’s problem, I attached a simple sample here for testing.
Another option is by using the Windows combo box control. The key event we need to hook is OnChange & OnKeyDown.
-brenda (ISV Buddy Team)
Truc Nguyen
Hi,
You can draw two types of combo boxes on a worksheet, one is drawn from the Form toolbox and one is drawn from the Control toolbox. The combo from the control toolbox lets you place VBA code behind it. With the other you can only assign a macro. The control toolbox one lets you catch events of the combo. Thats the one you need to use.
You need to handle the KeyDown method and check for the return key. If its pressed then do your formating.
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
MsgBox "Hello World"
End If
End Sub