How to treat unusual closing of a Combobox list in VBA?

Asked

Viewed 482 times

4

I am creating an important effect for the presentation of a Combobox in particular.

This Combobox is originally produced centralized, and when opening your list I make the text left-aligned so that items in your list are displayed left-aligned, but the idea is that the text of the Combobox box remains centralized. Like it is not possible to do this only with own Combobox, I found a way:

A "Label" with the same text of Combobox and also centralized has the approximate size of this Combobox and becomes visible on it when Combobox is selected, and this is done through the "Enter".

Thus, the user see the centered text in Combobox since the label hides the original text that is aligned to the left by the "Enter" event, and the list is presented automatically through the "Dropdown" left-aligned. So far, so perfect!

I prepared the "Click" Label event in case the user "click" on it instead of selecting an item, then the Label "would be removed" (becoming invisible) and the list would be closed, however, in this case the list is NOT closed, but the label is removed, then need to "click" on the Combobox to close the list (at this time the Combobox text is again centralized).

If there was a control over the "closing of the list", when the label is removed in the first "click" the list would be closed, but in the searches I found nothing that could use or solve the problem.

There is some form of control on closing the Combobox list in VBA, or another way to create this effect?

The code for verification is this:

Private Sub UserForm_activate()

ComboBox1.TextAlign = fmTextAlignCenter

ComboBox1.AddItem "Primeiro"
ComboBox1.AddItem "Segundo"
ComboBox1.AddItem "Terceiro"
ComboBox1.ListIndex = 0

Label1.Left = ComboBox1.Left + 2
Label1.Width = ComboBox1.Width - 4
Label1.Top = ComboBox1.Top + 2
Label1.Height = ComboBox1.Height - 2

Label1.TextAlign = fmTextAlignCenter

Label1.BorderStyle = fmBorderStyleNone

Label1.ZOrder 0

Label1.Visible = False

TextBox1.SetFocus

End Sub


Private Sub ComboBox1_enter()

ComboBox1.TextAlign = fmTextAlignLeft

Label1 = ComboBox1.Text

Label1.Visible = True

ComboBox1.DropDown

End Sub


Private Sub Label1_Click()

ComboBox1.TextAlign = fmTextAlignCenter

Label1.Visible = False

End Sub
excel vba combobox

  • Have you tried using <code> Private Sub Combobox_change() </code>? This might work.

  • Hi Evert, Change is not triggered because there is no selection of an item in the list.

  • Got @Leo... complicated... has how to send the spreadsheet to take a look or the code you are using?

  • I think if you refresh the form maybe? Recording and searching for information already entered...

  • There was a mistake and I changed parts of the text, because in fact what happens is the opposite of what I described, the label is removed, but the list remains open.

  • I think I understand what you want... I think the sequence of the codes needs to be adjusted. One minute I will assemble here to test.

  • I changed the code too, I had the alignments reversed.

Show 2 more comments

1 answer

2


A possible solution would be to use the change:

Private Sub UserForm_Activate()

ComboBox1.AddItem "Primeiro"
ComboBox1.AddItem "Segundo"
ComboBox1.AddItem "Terceiro"
ComboBox1.ListIndex = 0

Label1.Left = ComboBox1.Left + 2
Label1.Width = ComboBox1.Width - 4
Label1.Top = ComboBox1.Top + 2
Label1.Height = ComboBox1.Height - 2
Label1.TextAlign = fmTextAlignCenter
Label1.BorderStyle = fmBorderStyleNone
Label1.ZOrder 0
Label1 = ComboBox1.Text
Label1.Visible = False
TextBox1.SetFocus

End Sub

Private Sub ComboBox1_enter()

Label1 = ComboBox1.Text
Label1.Visible = True

ComboBox1.TextAlign = fmTextAlignCenter
ComboBox1.DropDown
ComboBox1.SetFocus

End Sub

Private Sub ComboBox1_Change()

Label1 = ComboBox1.Text
Label1.Visible = False

ComboBox1.TextAlign = fmTextAlignLeft
TextBox1.SetFocus

End Sub

If you need it, you can use the Afterupdate also for any resolution after update:

Private Sub ComboBox1_AfterUpdate()

End Sub

Another option would be to use a Listbox and a Label, then configure the Label layout to look like a Combobox, follow example:

Private Sub UserForm_Activate()

ListBox1.AddItem "Primeiro"
ListBox1.AddItem "Segundo"
ListBox1.AddItem "Terceiro"
ListBox1.ListIndex = 0

ListBox1.TextAlign = fmTextAlignCenter

Label1.Left = ListBox1.Left + 2
Label1.Width = ListBox1.Width - 4
Label1.Top = ListBox1.Top + 2
Label1.TextAlign = fmTextAlignCenter
Label1.BorderStyle = fmBorderStyleNone
Label1.ZOrder 0
Label1 = ListBox1.Text
ListBox1.Visible = False
Label1.Visible = True

TextBox1.SetFocus

End Sub

Private Sub Label1_Click()

Label1.Visible = False
ListBox1.Visible = True

End Sub

Private Sub ListBox1_Click()

Label1 = ListBox1.Text
ListBox1.Visible = False
Label1.Visible = True

End Sub

See if this works better.

Abs,

  • @Leo, see if that helps you. The only problem is that if the person uses the keyboard he can only change once with the arrow... but I think you can solve this with a Keydown or Keypress check. I didn’t have time to test this part. Let me know if it fits. Abs

  • 1

    Another option may also be a Listbox instead of a combo box... as it is not actually using the Combobox functions. Perhaps it is better to use Listbox, no?

  • Leo but you would not click on the label... I thought it would be hidden always it only shows when the user opens the combobox, no?

  • You can put a Click on the Label to focus on Textbox1 and use the after update to make the label disappear if applicable. I’m making the listbox to test and I’ll post here too.

  • See if this looks better.. I think it will be easier with Listbox

  • 1

    Here is an example I did: https://onedrive.live.com/redir?resid=7A3D691330A912A! 208644&authkey=! Aecrinedfxtzm90&ithint=Folder%2c

  • Evert, the first code worked! However, to be complete you need to put the Label Click event, passing the focus to the Textbox (I would take the Change event, because it works without it), making this edition is perfect the answer. Grateful for the spreadsheet and effort, I will mark the answer after editing. Hug.

  • Leo, I didn’t do a test here and ok to get the answer right... but if the user instead of clicking use the keyboard still works? I could not reproduce here the effect that commented...

  • Leo, I put in the same folder a file with Combobox... I could not play to be functional with both mouse and keyboard, arrows etc... if you can take a look at the file I made... I think Listbox got better. But perhaps in its context it is not the best option.

  • Yeah, it worked partially, but I complemented it. When entering with the "Tab" the list opens and when "click" on the Label the list closes, but if you use the "ESC" with the open list or press something, the Label remains, then I solved with the Keypress event in Combobox, adding Keyascii = 0 and calling Label1_click and then creating the Exit event just by calling Label1_click, IT WORKED! If you want to add to the code will stay 10! Hug.

Show 5 more comments

Browser other questions tagged excel vba combobox

You are not signed in. Login or sign up in order to post.