Adding "additional" controls programatically

[What-A-Tool]

I have code which creates a form if required - I am able to add standard
form controls this way, but I would like to add an "additional" control
(progress bar) to my programmatically created form. I can't seem to find any
examples of how to do this.
Could anyone help, please?

Thank You - Sean

 

[Doug Glancy]

Sean,

I haven't done this much but I think something like this - I assume a form
with a button on it. When you click the button another button is added:

Private Sub CommandButton1_Click()
Dim test_button As MSForms.CommandButton
Dim ctl As Control

'look under help for "Add" to get the other bstrProgIDs, but they follow the
form below
Set test_button = Me.Controls.Add(bstrProgID:="Forms.CommandButton.1",
Name:="btnTestButton", Visible:=True)
With test_button
.Caption = "Test Button"
End With
'It looks like you can only set location by recasting it as a generic
control
Set ctl = test_button
With ctl
.Top = 20
.Left = 20
End With
End Sub

John Walkenbach has a whole section on this here:

http://www.j-walk.com/ss/excel/tips/tip76.htm

hth,

Doug

 

[What-A-Tool]

Thank you for your help , however :
This works fine, as long as the control to add is part of the Forms
control group. I don't believe there is a Forms.Progressbar. How do I add a
control that isn't in this collection?

 

[Bob Phillips]

If you want a progress bar, as you note there is no built-in control (there
may be commercial controls, but I know of none), but you could just add the
usual type of progress indicator as described here
http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[What-A-Tool]

If you want a progress bar, as you note there is no built-in control
(there may be commercial controls, but I know of none), but you could just
add the usual type of progress indicator as described here
http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm

--

So you are saying there is no way to programatically add the Microsoft
ProgressBar Control, Version 5.0 or Version 6.0, that I have available in
the "Addtional Controls" dialog box accesible from the tools menu? These
controls are contained in comctl32.ocx, or MSCOMCTL.OCX, respectively. Is
there a way to programatically create a reference to 1 of these .ocx files,
and add the control from there?

Thanks Bob -
Sean

 

[Doug Glancy]

Sean,

I don't know how to do what you ask.

I think controls are usurally added at runtime in situations where the exact
number of controls needed is not yet known, for example, one textbox per
record, when the number of records to be entered can't be known at design
time.

If this is not the case with your application. perhaps you could add the
progress bar to the bottom of your form at design time, and just expand the
form during run time when you want the bar to be visible?

just a thought,

Doug

 

[Andy Pope]

Hi,

This works for me. Adds progress bar to userform and when the form is
clicked will do a simple 1 to 100 prorgess.

Private m_objPBar As Object
Private Sub UserForm_Click()

Dim lngIndex As Long
Dim lngLoop As Long

m_objPBar.Value = 0
For lngIndex = 1 To 100
For lngLoop = 1 To 599999
Next
m_objPBar.Value = m_objPBar.Value + 1
Next

End Sub
Private Sub UserForm_Initialize()

Set m_objPBar = _
Me.Controls.Add("MSComctlLib.ProgCtrl.2", "myProg", True)

With m_objPBar
.Top = 5
.Left = 5
.Width = Me.InsideWidth - 10
.Height = 15
.Value = 0
End With

End Sub

Cheers
Andy

 

[Doug Glancy]

Andy,

Your post reminded me that even though Intellisense doesn't show a property
for a control, e.g., ".top" it might still be available. I do wonder
though, why "top" didn't show for my earlier example of an MSForms.Button,
but did when I cast it as a Control? Anyways, a good reminder not to rely
too much on the IDE features, although generally I'd be lost without them.

Doug

 

[Andy Pope]

Hi Doug,

If you go to the Object Browser (F2) and in the libraries dropdown
restrict the search to MSForms you will see the CommandButton does not
appear to have the property Top. I have no idea why but as it's not
exposed here I can understand why intellisense does not know about it.

Cheers
Andy

 

[Doug Glancy]

Thanks Andy,

When I looked in the Object Browser, I can see that it's true for the other
MSForms controls as well.

Doug

 

[What-A-Tool]

Hi,

This works for me. Adds progress bar to userform and when the form is
clicked will do a simple 1 to 100 prorgess.

Private m_objPBar As Object
Private Sub UserForm_Click()

Dim lngIndex As Long
Dim lngLoop As Long

m_objPBar.Value = 0
For lngIndex = 1 To 100
For lngLoop = 1 To 599999
Next
m_objPBar.Value = m_objPBar.Value + 1
Next

End Sub
Private Sub UserForm_Initialize()

Set m_objPBar = _
Me.Controls.Add("MSComctlLib.ProgCtrl.2", "myProg", True)

With m_objPBar
.Top = 5
.Left = 5
.Width = Me.InsideWidth - 10
.Height = 15
.Value = 0
End With

End Sub

Cheers
Andy

Interesting - I'll give it a try.
Thanks to all for your input - I'll get back and let you know how it worked.

 

[Tom Ogilvy]

think of it this way.

When in a userform, an MSforms control is in the control container and
inherits those properties such as an exit event and a top property. In a
worksheet, it gets some properties and events from the oleobject container.

 

[Doug Glancy]

Tom,

That makes sense. Is there any way to know what properties it's inherited
besides trial and error? I guess doing what I did - declaring both as
Control/OleObject and the specific control is one way to figure it out.

Thanks,

Doug

 

[Tom Ogilvy]

Looking at the object browser would be what I would do.