Any ideas? UserForm Initialization doing funny things.

P

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
I have a userform that shows or hides different controls based on what is selected from ListBox1. It also adjusts the left and top values of the appropriate controls depending on that selection, just to make everything look good. So upon initialization it sets the left and top values of each control to a beginning position, and sets each control .visible = False except for Listbox1. When you make a selection from ListBox1, the appropriate controls become visible and the left and top values are adjusted to make it look uniform. Everything was working really well, but now for some reason it's acting a bit funny.

Here's my issue. When I first open the userform and all that is showing is my Listbox1, when I make a selection everything is in the right spot, except the top values are way too much. I have a button on the form that resets the form by calling the UserForm_Initialize sub. If I click that, and then make another selection from listbox1, the top value of the controls is then correct. So basically the first time I make open the form the top values are wrong, but if I re-call the initialization sub it corrects itself. Any idea why it would do this?

Code: 
'Reset Form button
Private Sub CommandButton2_Click()
UserForm_Initialize
End Sub

That is my reset button code, so all it is doing is rerunning the initialize sub again, so I don't know why it won't work right the first time running the code.

Any Ideas?

Here's my Initialize sub, in case you're curious.

Code: 
Private Sub UserForm_Initialize()
With Me
    .Caption = "Select Sample Type"
    .TextBox1.Width = 100
    .TextBox1.Top = 30
    .TextBox1.Left = 10
    .TextBox2.Width = 100
    .TextBox2.Top = 30 + Me.TextBox1.Height + 20
    .TextBox2.Left = 10
    
'Cu
    .TextBox3.Width = 40
    .TextBox3.Top = .ListBox2.Top + .ListBox2.Height + 20
    .TextBox3.Left = Me.TextBox1.Left + Me.TextBox1.Width + 20
    .TextBox3 = "TB3"
    
'Fe
    .TextBox4.Width = 40
    .TextBox4.Top = .TextBox3.Top
    .TextBox4.Left = .TextBox3.Left + .TextBox3.Width + 100
    .TextBox4 = "TB4"
'S
    .TextBox5.Width = 40
    .TextBox5.Top = .TextBox3.Top + .TextBox3.Height + 10
    .TextBox5.Left = .TextBox3.Left
    .TextBox5 = "TB5"
'SiO2
    .TextBox6.Width = 40
    .TextBox6.Top = .TextBox5.Top
    .TextBox6.Left = .TextBox4.Left
    .TextBox6 = "TB6"
    
'CaO
    .TextBox7.Width = 40
    .TextBox7.Top = .TextBox5.Top + .TextBox5.Height + 10
    .TextBox7.Left = .TextBox3.Left
    .TextBox7 = "TB7"
    
'Al2O3
    .TextBox8.Width = 40
    .TextBox8.Top = .TextBox7.Top
    .TextBox8.Left = .TextBox4.Left
    .TextBox8 = "TB8"
    
'ISA Cu
    .TextBox9.Width = 40
    .TextBox9.Top = .TextBox7.Top + .TextBox7.Height + 10
    .TextBox9.Left = .TextBox3.Left
    .TextBox9 = "TB9"
    
'ISA Fe
    .TextBox10.Width = 40
    .TextBox10.Top = .TextBox9.Top
    .TextBox10.Left = .TextBox4.Left
    .TextBox10 = "TB10"
    
'ISA S
    .TextBox11.Width = 40
    .TextBox11.Top = .TextBox9.Top + .TextBox9.Height + 10
    .TextBox11.Left = .TextBox3.Left
    .TextBox11 = "TB11"
    
'ISA SiO2
    .TextBox12.Width = 40
    .TextBox12.Top = .TextBox11.Top
    .TextBox12.Left = .TextBox4.Left
    .TextBox12 = "TB12"
    
'ISA CaO
    .TextBox13.Width = 40
    .TextBox13.Top = .TextBox11.Top + .TextBox11.Height + 10
    .TextBox13.Left = .TextBox3.Left
    .TextBox13 = "TB13"
    
'ISA Al2O3
    .TextBox14.Width = 40
    .TextBox14.Top = .TextBox13.Top
    .TextBox14.Left = .TextBox4.Left
    .TextBox14 = "TB14"
    
'Fe3O4
    .TextBox15.Width = 40
    .TextBox15.Top = .TextBox13.Top + .TextBox13.Height + 10
    .TextBox15.Left = .TextBox4.Left
    .TextBox15 = "TB15"
    
    .TextBox16.Width = 40
    .TextBox16.Top = .TextBox13.Top + .TextBox13.Height + 10
    .TextBox16.Left = .TextBox4.Left
    .TextBox16 = "TextBox16"
    
'Conv. #
With .ComboBox1
        .Clear
        .Width = 40
        .Top = Me.TextBox15.Top + Me.TextBox15.Height + 10
        .Left = Me.TextBox3.Left
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
End With
    .TextBox16.Width = 40
    .TextBox16.Top = .ComboBox1.Top
    .TextBox16.Left = .TextBox4.Left
    .TextBox16 = "TextBox16"
With .ComboBox2
        .Clear
        .Width = 40
        .Top = Me.ComboBox1.Top + Me.ComboBox1.Height + 10
        .Left = Me.TextBox3.Left
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
End With
With .ComboBox3
        .Clear
        .Width = 40
        .Top = Me.ComboBox2.Top
        .Left = Me.TextBox4.Left
End With
With .ListBox1
        .Width = 75
        .Top = 25
        .Left = 20
        .Height = 126
        .Visible = True
        .SetFocus
End With
    .CheckBox1 = True
With .CheckBox1
        .AutoSize = True
        .Top = Me.TextBox3.Top - .Height - 5
        .Left = Me.TextBox3.Left
End With
    .ListBox2.Width = 100
    .ListBox2.Top = 30
    .ListBox2.Left = Me.TextBox1.Left + Me.TextBox1.Width + 20
    
    .ListBox3.Width = 100
    .ListBox3.Top = 30
    .ListBox3.Left = Me.ListBox2.Left + Me.ListBox2.Width + 30
    
    .Width = Me.ListBox1.Width + 60
    .Height = Me.ListBox1.Height + 65
    .Image1.Visible = True
With .Image1
        .Top = 0
        .Left = 0
        .Width = Me.Width - 10
        .Height = Me.Height - 28
End With
    .CommandButton2.Width = 100
    .CommandButton2.Left = 10
    .CommandButton2.Top = .TextBox2.Top + .TextBox2.Height + 10
    
    .CommandButton3.Top = .TextBox3.Top
    .CommandButton3.Left = .TextBox4.Left + .TextBox4.Width + 10
    
    .CommandButton4.Top = .CommandButton3.Top
    .CommandButton4.Left = .CommandButton3.Left
    
    .CommandButton5.Top = .CommandButton4.Top + .CommandButton4.Height + 10
    .CommandButton5.Left = .CommandButton4.Left
    
    .ListBox1.Clear
    .ListBox2.Clear
    .ListBox3.Clear
    .ListBox4.Clear
    For Each fCont In Me.Controls
        If fCont.Name <> "ListBox1" And fCont.Name <> "Image1" Then
            fCont.Visible = False
        End If
    Next fCont
For Each fCont In Me.Controls
    If i = 24 Then Exit For
    If TypeName(fCont) = "Label" Or fCont.Name = "CheckBox1" Then
        With fCont
            .Font.Bold = True
            .Font.Size = 10
            .Font.Name = "Ariel"
            .AutoSize = True
            .Height = fCont.Font.Size + 2
        End With
    End If
Next fCont
    .CheckBox1.Height = 15
With .Label1   'Arrival Time
        .Top = Me.TextBox1.Top - (.Height + 1)
        .Left = (Me.TextBox1.Width / 2) - (.Width / 2) + Me.TextBox1.Left
End With
With .Label2   'Sample Time
        .Top = Me.TextBox2.Top - (.Height + 1)
        .Left = (Me.TextBox2.Width / 2) - (.Width / 2) + Me.TextBox2.Left
End With
With .Label3   'Control Room Operator
        .Top = Me.ListBox2.Top - (.Height + 1)
        .Left = (Me.ListBox2.Width / 2) - (.Width / 2) + Me.ListBox2.Left
End With
With .Label4   'Analyst
        .Top = Me.ListBox3.Top - (.Height + 1)
        .Left = (Me.ListBox3.Width / 2) - (.Width / 2) + Me.ListBox3.Left
End With
With .Label5   'Cu TB3
        .Top = (Me.TextBox3.Height / 2) - (.Height / 2) + Me.TextBox3.Top
        .Left = Me.TextBox3.Left - .Width
End With
With .Label6   'Fe TB4
        .Top = (Me.TextBox4.Height / 2) - (.Height / 2) + Me.TextBox4.Top
        .Left = Me.TextBox4.Left - .Width
End With
With .Label7   'S TB5
        .Top = (Me.TextBox5.Height / 2) - (.Height / 2) + Me.TextBox5.Top
        .Left = Me.TextBox5.Left - .Width
End With
With .Label8   'SiO2 TB6
        .Top = (Me.TextBox6.Height / 2) - (.Height / 2) + Me.TextBox6.Top
        .Left = Me.TextBox6.Left - .Width
End With
With .Label9   'CaO TB7
        .Top = (Me.TextBox7.Height / 2) - (.Height / 2) + Me.TextBox7.Top
        .Left = Me.TextBox7.Left - .Width
End With
With .Label10   'Al2O3 TB8
        .Top = (Me.TextBox8.Height / 2) - (.Height / 2) + Me.TextBox8.Top
        .Left = Me.TextBox8.Left - .Width
End With
With .Label11   'ISA Cu TB9
        .Top = (Me.TextBox9.Height / 2) - (.Height / 2) + Me.TextBox9.Top
        .Left = Me.TextBox9.Left - .Width
End With
With .Label12   'ISA Fe TB10
        .Top = (Me.TextBox10.Height / 2) - (.Height / 2) + Me.TextBox10.Top
        .Left = Me.TextBox10.Left - .Width
End With
With .Label13   'ISA S TB11
        .Top = (Me.TextBox11.Height / 2) - (.Height / 2) + Me.TextBox11.Top
        .Left = Me.TextBox11.Left - .Width
End With
With .Label14   'ISA SiO2 Tb12
        .Top = (Me.TextBox12.Height / 2) - (.Height / 2) + Me.TextBox12.Top
        .Left = Me.TextBox12.Left - .Width
End With
With .Label15   'ISA CaO TB13
        .Top = (Me.TextBox13.Height / 2) - (.Height / 2) + Me.TextBox13.Top
        .Left = Me.TextBox13.Left - .Width
End With
With .Label16   'ISA Al2O3 TB14
        .Top = (Me.TextBox14.Height / 2) - (.Height / 2) + Me.TextBox14.Top
        .Left = Me.TextBox14.Left - .Width
End With
With .Label17   'Fe3O4 TB15
        .Top = (Me.TextBox15.Height / 2) - (.Height / 2) + Me.TextBox15.Top
        .Left = Me.TextBox15.Left - .Width
End With
With .Label18   'Conv # CB1
        .Top = (Me.ComboBox1.Height / 2) - (.Height / 2) + Me.ComboBox1.Top
        .Left = Me.ComboBox1.Left - .Width
End With
With .Label19   'Charge # TB15
        .Top = (Me.TextBox15.Height / 2) - (.Height / 2) + Me.TextBox15.Top
        .Left = Me.TextBox15.Left - .Width
End With
With .Label20   'Skim # TB15
        .Top = (Me.TextBox15.Height / 2) - (.Height / 2) + Me.TextBox15.Top
        .Left = Me.TextBox15.Left - .Width
End With
With .Label21   'Skimmer Name TB15
        .Top = (Me.TextBox15.Height / 2) - (.Height / 2) + Me.TextBox15.Top
        .Left = Me.TextBox15.Left - .Width
End With
With .Label22   'Moisture
        .Top = Me.Label5.Top
        .Left = Me.TextBox3.Left - .Width
End With
End With
 
End Sub

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
644
dmt32
D
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
343
Rhodie72
Rhodie72
D
  • Solved
TextBox looping help required in VBA
Replies
3
Views
513
DanteAmor
DanteAmor
P
  • Question Question
USERFORM button for UPDATE error "block if without end if"
2
Replies
16
Views
855
dmt32
D
C
  • Question Question
PREVENTING LISTBOX SELECTION FROM REVERTING BACK TO FIRST ITEM IN ROWSOURCE WHEN USERFORM LOSES AND REGAINS FOCUS
Replies
0
Views
876
chazrab
C

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top