Solved

Excel 2016 VBA - userform and z-Order of controls

Posted on 2016-11-13
11
54 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 17

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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 17

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 17

Expert Comment

by:Roy_Cox
ID: 41892504
Pleased to help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Formula or Macro to determine variance 17 75
Excel 2010 Text Formatting placing a hyphen in front of text 3 20
Freeze Panes Solution 6 28
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 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

24 Experts available now in Live!

Get 1:1 Help Now