Solved

Excel 2016 VBA - userform and z-Order of controls

Posted on 2016-11-13
11
149 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 20
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 47

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 19

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 47

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 20

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

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 19

Expert Comment

by:Roy_Cox
ID: 41892504
Pleased to help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

740 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