MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combo/List/Text boxes...


Posted by Lucky on December 13, 2001 5:32 AM

ANYONE know if it is possible to tab/enter out of boxes????!!!!!! PLEASE!!


Posted by Joe Was on December 13, 2001 8:28 AM

In your macro trap the Enter key and set it to the same result as Cancel.

The problem is: It may be possible to re-direct the window view, but as long as the box has control it takes precedence over what becomes active. So the Input or Msg box must close before another area can become active. Now id you have a Shape that was Drawn to look like a Box and you have a Msg or text in it, you can put the focus elsewhere. JSW

Posted by lucky on December 13, 2001 8:37 AM

Is there code where I can change the keystroke of the enter button to be cancel??


Posted by Joe Was on December 13, 2001 9:08 AM

Yes and now, I need to know which box you are using and I need to see the code for it. Most boxes come with options to add buttons for OK, Cancel, etc. It is easy to use the options and to set other actions to these options, if not you can use the "Case" style to build any action you want. If you are using standard xlDialogBoxes then adding control may take some work. Some boxes will not take any actions other than what is pre-defined for that object. When you ask a question always copy uor code with the question. Just use Right-Click Copy Paste. You general unsupported questions just make extra work for those trying to help. JSW Is there code where I can change the keystroke of the enter button to be cancel??


Posted by lucky on December 14, 2001 6:41 AM

All the code at the mo for the boxes....

Private Sub ComboBox2_Change()
ComboBox3.ListIndex = ComboBox2.ListIndex
Exit Sub
End Sub

Private Sub ComboBox3_Change()
ComboBox2.ListIndex = ComboBox3.ListIndex
Exit Sub
End Sub

Then a command button.....
Private Sub CommandButton1_Click()

Workbooks.Open ("J:\xx\xxx\database\" & ComboBox3.Value & ".xls")
ActiveSheet.Range("a11").Select
End If
Exit Sub

So user has option of number selection (ComboBox2) or name selection (ComboBox3). What I was inquiring about was once a selection is made can the user enter out of the box and maybe even just enter to the command button.
Thanks for your time and sorry it wasn't clear.

Yes and now, I need to know which box you are using and I need to see the code for it. Most boxes come with options to add buttons for OK, Cancel, etc. It is easy to use the options and to set other actions to these options, if not you can use the "Case" style to build any action you want. If you are using standard xlDialogBoxes then adding control may take some work. Some boxes will not take any actions other than what is pre-defined for that object. When you ask a question always copy uor code with the question. Just use Right-Click Copy Paste. You general unsupported questions just make extra work for those trying to help. JSW : Is there code where I can change the keystroke of the enter button to be cancel?? :


Posted by Joe Was on December 14, 2001 10:07 AM

This is just the box calls not the control code?

For the user form (UserForm1) that contains your BommboBox add a CommandButton1 the code below will lable the button "Cancel" and assign the keybord shortcut Alt+c to the same as a button click. If the user clicks the "Cancel" command button, the form Exit "X" or any non control portion of the form or hits "Enter" the form will close!

This is for a form named UserForm1, this is module level code to show and hide Form1.

Sub SmyForm1()
Load UserForm1
UserForm1.Show
End Sub


Sub HmyForm1()
UserForm1.Hide
End Sub


This next code is attached to the command button in this case CommandButton1

Private Sub UserForm_Initialize()
'Set Accelerator key to COMMAND + C
CommandButton1.Accelerator = "C"
CommandButton1.Caption = "Clicked"
Load UserForm1
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Cancel" Then
'Set Accelerator key to COMMAND + C (Alt+c) on the keyboard.
'Button also works!
CommandButton1.Accelerator = "C"
Call HmyForm1
Else
CommandButton1.Caption = "Clicked"
CommandButton1.Accelerator = "C"
Call HmyForm1
End If
End Sub


' This is the Click event of UserForm2
Private Sub UserForm_Click()
Call HmyForm1
End Sub


You can add other events to the code and dress it up a bit? JSW All the code at the mo for the boxes.... Private Sub ComboBox2_Change() Workbooks.Open ("J:\xx\xxx\database\" & ComboBox3.Value & ".xls") ActiveSheet.Range("a11").Select End If So user has option of number selection (ComboBox2) or name selection (ComboBox3). What I was inquiring about was once a selection is made can the user enter out of the box and maybe even just enter to the command button.