Solved

Excel 2016 VBA - userform and z-Order of controls

Posted on 2016-11-13
11
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 21
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 48

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 20

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 48

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 21

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 21
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 20

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 20

Expert Comment

by:Roy_Cox
ID: 41892504
Pleased to help
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

717 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