Solved

Excel 2016 VBA - userform and z-Order of controls

Posted on 2016-11-13
11
76 Views
Last Modified: 2016-11-17
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
Comment
Question by:hindersaliva
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 19
ID: 41885446
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
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 125 total points
ID: 41885449
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
 
LVL 18

Assisted Solution

by:Roy_Cox
Roy_Cox earned 250 total points
ID: 41885468
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:hindersaliva
ID: 41885476
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
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 125 total points
ID: 41885492
Try this workbook.
28982760.xlsm
0
 

Author Comment

by:hindersaliva
ID: 41885501
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
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 125 total points
ID: 41885505
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
 

Author Comment

by:hindersaliva
ID: 41885510
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
 
LVL 19
ID: 41885513
You're welcome. Glad you got it! Since you only posted a generic sample, naturally I could not detect your business logic.
0
 
LVL 18

Accepted Solution

by:
Roy_Cox earned 250 total points
ID: 41885859
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
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41892504
Pleased to help
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now