PDA

View Full Version : Adding a ScrollBar to a dynamic UserForm



lionne
01-13-2012, 01:48 AM
Hi guys
I am striving to get the scroll bar on my user form.
I compose the form dynamically, it has 1 to 7 frames in it with lots of labels and text boxes. The problem is, when the form has 7 frames, in other words, gets too big, I need a possibility to scroll to get to the buttons, that I have at the bottom of my form. I can add the scrollbar, but it does not function. Can't figure out, what am I doing wrong?

Here is a piece of my code.
1. Create the form:

Dim MetaForm, NewTextBox As Object
Set MetaForm = Application.VBE.ActiveVBProject.VBComponents.Add(3)

2. Add Controls:

Dim NewFrame1 As MSForms.Frame
Dim NewLabel As MSForms.Label
Set NewFrame1 = MetaParList.designer.Controls.Add("Forms.Frame.1")
'Labels and Text Boxes to the Frame
Set NewLabel = NewFrame1.Controls.Add("Forms.Label.1")
Set NewTextBox = NewFrame1.Controls.Add("Forms.TextBox.1")

3. If I use the same logic for adding a scroll bar to the user form, it does not function:

Dim ScrollBar As MSForms.ScrollBar
Set ScrollBar = MetaParList.designer.Controls.Add("Forms.ScrollBar.1")

Does anyone know, what is the way to get it work?

p45cal
01-13-2012, 03:04 AM
The userform vertical scroll bar is brought up by setting the ScrollBars property of the userform to 2 (frmScrollBarsVertical), not by adding a scrollbar control.

You'll also need to set the ScrollHeight property to a value greater than the userform's height

lionne
01-13-2012, 03:12 AM
Hi p45cal

thanks! I added the following to the code:

With MetaForm
.Properties("ScrollBars") = fmScrollBarsVertical
End with

However, the bar does not function...

p45cal
01-13-2012, 04:02 AM
Hi p45cal

thanks! I added the following to the code:

With MetaForm
.Properties("ScrollBars") = fmScrollBarsVertical
End with
However, the bar does not function...
Again, You'll also need to set the ScrollHeight property to a value greater than the userform's height

lionne
01-13-2012, 04:52 AM
p45cal, you're genious!! just these 2 lines and it makes a miracle :))
thank you so much!!!

lionne
01-13-2012, 05:13 AM
may I ask you about one other problem?
I have some difficulties with tab jumps. My Form (the other one) has 1 TextBox and 5 ComboBoxes. When I click to open the form, the cursor goes to ComboBox2, instead of TextBox1, which is at the top of the form. Here is the code, with which I'd like to set the tab order:

With Form
.TextBox1.TabIndex = 0
.ComboBox1.TabIndex = 1
.ComboBox2.TabIndex = 2
.ComboBox3.TabIndex = 3
.ComboBox4.TabIndex = 4
.ComboBox5.TabIndex = 5
.ComboBox6.TabIndex = 6
End With
The second thing is ComboBoxes. I have a couple of calues there. Instead of choosing them, I just enter the first letter, say 'a'. It shows me the value, say "Acknowledgement", but jumps right away to the next ComboBox.. but what if I'd like to choose the other value, starting from 'a' - now I need to go back and choose.. which is not what I actually wanted... Do you know, what is the problem here?

p45cal
01-13-2012, 06:13 AM
1. You'd normally have an initialize event which runs when the form is first shown, so include a setfocus command:

Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub

2. What values have you for the AutoTab and MaxLength properties of that combobox?

lionne
01-23-2012, 03:47 AM
AutoTab = False; MaxLength = 0

lionne
01-23-2012, 03:48 AM
btw, the SetFocus worked!! Thanks so much, it just great, when everything functions ;)

p45cal
01-23-2012, 04:51 AM
AutoTab = False; MaxLength = 0 Ah. True and 1, respectively, would have given that behaviour.
Is there any event code, especially for that combobox that could set the focus elsewhere? For example, a change event for that combobox might change another control's value, and that control might its own event which might in turn set the focus elsewhere.

lionne
01-23-2012, 05:03 AM
That's it! There was a SetFocus in the Change Event, so after I removed it, everything works pretty well, thanks a lot!

Going back to the first TextBox tabulation.. now I can't tab further from that TextBox..

The parameters are set as follows:
.AutoTab = True
.TabIndex = 0
.TabKeyBehavior = False
.TabStop = True

I just can't get to the next field...

lionne
01-23-2012, 05:18 AM
Don't want to seem impudent, but I have another issue, that I cannot solve:

I have a form, which is dynamically created. Some TextBoxes of this form are filled in at the creation time, and some will be filled in after that.
Now, I loop through the controls, if I have a TxtBox "asset" then I show the Button Control. In the code of this button, I need to check, if the TxtBox "asset" is empty or not. Here is the fragment of code:

UserForm:

Set MetaParList = Application.VBE.ActiveVBProject.VBComponents.Add(3)
Set NewLabel = NewFrame1.Controls.Add("Forms.Label.1")
Set NewTextBox = NewFrame1.Controls.Add("Forms.TextBox.1")
NewTextBox.Name = "asset" 'Here it is variable, but I put the string, otherwise you will have to look through more code, which is not necessary
'Then I add the button, if txtbox.Name = "asset"
Set Find_AssetButton = MetaParList.designer.Controls.Add("Forms.CommandButton.1")


Then in the button code, I check, if the value of the TxtBox is not null


If ctl.Text = "" Then

What is strange, is that when the Form MetaParList is there, I fill in the TxtBox "asset", so the value is not null, I show TxtBox.Value in the MsgBox, and I see the value, BUT if I push the Find_Asset Button, the value is empty there. Somehow, it doesn't remember, that the value was filled in, rsp. changed.

I hope, I could explain in a way, you can understand, what I meant..

p45cal
01-23-2012, 07:37 AM
That's it! There was a SetFocus in the Change Event, so after I removed it, everything works pretty well, thanks a lot!

Going back to the first TextBox tabulation.. now I can't tab further from that TextBox..

The parameters are set as follows:
.AutoTab = True
.TabIndex = 0
.TabKeyBehavior = False
.TabStop = True

I just can't get to the next field...
When this happens, then you repeatedly press Tab, does it bring other controls into focus? I suspect it will.
OR:
If, having pressed tab once to leave that control does typing something just put more text in the text box?
Is the next one in the tab order a list box? If it has nothing in it (perhaps because you're testing) you won't see any evidence that it has the focus.

p45cal
01-23-2012, 07:39 AM
Don't want to seem impudent, but I have another issue, that I cannot solve:

I have a form, which is dynamically created. Some TextBoxes of this form are filled in at the creation time
I think you ought to create a new thread - I don't have knowledge nor the (substantial) time it would take for me to look into this one.