• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 879
  • Last Modified:

Excel 2016 VBA - userform and z-Order of controls

I have a UserForm and 2 controls. When the form opens I want the ComboBox to be in front of the Label. I can't see where I can set this at design time. Can it be set at design time?

But, I put this in the Initialize

Me.Label1.ZOrder (0)
Me.ComboBox1.ZOrder (1)

Swapping the (0) and the (1) has no effect!

But the ComboBox is behind the Label. However, when I click on the ComboBox it becomes visible in front of the Label. This is odd.

How can I make the ComboBox appear in front all the time?

Thanks. File attached.
EE-question-on-ZOrder.xlsm
0
hindersaliva
Asked:
hindersaliva
  • 3
  • 3
  • 3
  • +1
5 Solutions
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I notice that your label is very wide. Once you put the text you really want in there, select the label and change the Width property to be smaller so the controls don't overlap

To turn on the Properties, press F4 or choose: View, Properties Window from the menu

You can get the combobox to be on top by cutting it and pasting it back.  The newest control is on top.
0
 
Martin LissOlder than dirtCommented:
There are some other things you can do.

  • Set the BackStyle transparent with fmBackStyleTransparent (or 0)
  • Set AutoSize to True
  • Set its Visible property to False until you need it
  • Set its Left property to some large negative number like -500 until you need it
0
 
Roy CoxGroup Finance ManagerCommented:
I'm fairly certain the Z-order cannot be set upon initializing. Why do you want a label behind the combobox?

Use a Frame instead.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
hindersalivaAuthor Commented:
Roy, the idea is, when a Checkbox is clicked the ComboBox appears positioned over where a Label is positioned. When the ComboBox selection is made it disappears and the Label takes the value of the selected item. There are 14 of these on the form. It avoids having 14 ComboBoxes with the same items.

I'm surprised that the Z-Order cannot be set at design time. (If that's so)
0
 
Martin LissOlder than dirtCommented:
Try this workbook.
28982760.xlsm
0
 
hindersalivaAuthor Commented:
Martin, the same happens.
When I click the checkbox nothing visible happens. But when I move the UserForm the combobox appears.

It looks like the combobox appears only when the screen needs to draw itself (redraw).
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
did you read this in my previous post, "You can get the combobox to be on top by cutting it and pasting it back.  The newest control is on top."

the label cannot take the focus either -- but I would still recommend changing its width so there is not overlap into the contents of your combo.
0
 
hindersalivaAuthor Commented:
Crystal, I solved it by making the Label.Visible = False and turning it back again when the ComboBox is made Visible = False.
It is important for me that the ComboBox and the Label are in the same position with the same dimensions.
Thanks for your suggestion.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
You're welcome. Glad you got it! Since you only posted a generic sample, naturally I could not detect your business logic.
0
 
Roy CoxGroup Finance ManagerCommented:
You can continue with the Labels ides, but I would simply disable the comboboxes when a selection has been made.

Option Explicit


Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me
        .Label1.Caption = .ComboBox1.Value
        .Label1.Visible = .ComboBox1.ListIndex > -1
    End With
End Sub


Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Me.ComboBox2.Enabled = Me.ComboBox2.ListIndex >= 1
End Sub

Private Sub UserForm_Initialize()
    With Me
        .ComboBox1.List = Array("Yes", "No")
        .ComboBox2.List = .ComboBox1.List
    End With
End Sub

Open in new window

EE-question-on-ZOrder.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now