UserForm Combobox DropButtonClick Event Bug?

[misted]

Hi I am wondering if this is a bug or just poor programming from my
part. In its simplest form what appears to me as a bug occurs when
there are 2 userforms. On UserForm1 we have a combobox with a
dropbuttonclick event code as:

Private Sub ComboBox1_DropButtonClick()
Me.Hide
UserForm2.Show
End Sub

Userform2 has a commandbutton coded as:

Private Sub CommandButton1_Click()
Me.Hide
UserForm1.Show
End Sub

First I load userform1. I click the dropdown button on the combobox,
userform2 appears. I press the commandbutton on userform2, and am
presented with userform1 again.

All good so far. However when I press the dropdownbutton again, the
click event is not fired.

I have switched the event to a doubleclick, which doesn't have this
problem, and works smoothly.

When switching the code for the dropbuttonclick event code to something
simple like msgbox "hello", and transfer the switching of forms to a
commandbutton, then there is no problem. The problem seems to be
linked to the hiding of the of the form and loading of another form
within the dropbuttonclick event.

If an additional button is added to the first form, UserForm1, which
hides the form, and another button is pressed (placed on a spreadsheet
perhaps) which shows the form again, the click event is funcitonal
again.

So it appears there is a reset that occurs with this hiding and showing
of the userform.

However I do not seem to be able to replicate this smoothly in the
click event code.

Any light that can be shed on this would be greatly appreciated. Even
if someone could confirm they get the same result / think its a bug as
opposed to poor programming, this would help alot.

I guess the double click event works, but isn't as elegant as a single
click on the dropdownbutton, which I would prefer to use. I am using
this to replicate a RefEdit control, but buggy as that is, I thought
I'd use the closest looking control.

Thanks for your help...

 

[NickHK]

I would imagine the events/repainting is becoming confused because you are
hiding the container (userform) just VBA is trying to paint the dropdown
portion of the combobox. In which state this leaves the combobox is anyone's
guess.
From a logical point of view, what is the point of this code ?
The combox box serves no purpose, as the user cannot see its entries.

Also, as you are using the default (vbModal) for the .Show method, the user
will not be able to interact with the worksheet anyway. Use vbModeless.

The RefEdit control is OK, if you do not put it on a frame and handling
canceling correctly.

NickHK

 

[Simon Lloyd]

Hi, Do you have to "Hide" the userform or could you "Unload" it ? if
unloading isn't a problem i would go with that or perhaps you could use
something like
Code:
--------------------
ComboBox1.DropButtonClick.SetFocus
--------------------
I'm no expert by far but perhaps it gives you another avenue to
explore!

Regards,
Simon

 

[misted]

Thanks for the response NickHK, much appreciated. I think your
assessment of the combobox 'remaining in limbo' so to speak is
probably correct, and explains why the double-click event works.
The purpose of the code is to allow the user to enter a range. However
in doing this I wish to raise another form to allow the user to have
addded functionality to enter the range, such as getopenfile method,
Header / No Header assessment, etc. (for actual range input I use an
inputbox type = 8). Moreover I have significant processing & help
files I attach to the programme, and the RefEdit just causes too many
problems. So you are right the combobox serves no purpose other than a
trigger for further forms, and finally containing the address for a
range of data. So maybe a list box or some other control could be
used, I chose the combox for aesthetic reasons as it fits in with the
rest of the form that I use. The modal status is overcome with the
inputbox that I use. I find the application is better contained when
made modal.

ok thanks for your time...

 

[misted]

Hi Simon,

Thanks for your time, I know, it seems like it would be a focus thing,
considering the hiding and showing of the form resets the click event
firing. However, I couldn't fix it that way... guess I'll stick to
double click!!

Thanks anyway....