Updating Object Properties in EXCEL VBA (EXCEL/VBA v2000)

Topic

New Reply

I attempt to amend the RowSource property of a combo box on my form by issuing the following instruction

frmEntPlayers.cmbSelPlayer.RowSource = “R50:R279”

I get no error from this line and at end of program the property remains unchanged.

Q. Am I attempting to do something here which is not possible in VBA, or am I going about it in the wrong way.

Any assistance that points me in the right direction would be most appreciated.

Regards
Rodopi

Reply | Quote

Replies

To test, I tried something similar, and it worked without a problem, so it certainly is possible in VBA. How and where are you calling the code?

Reply | Quote

To test, I tried something similar, and it worked without a problem, so it certainly is possible in VBA. How and where are you calling the code?

Reply | Quote

Runtime changes to properties of userform controls do not “stick”, they are undone as soon as the form is unloaded from memory.

If you need to make design-time changes to a userform programmatically, you need to set a reference to the VBE extensibility library and use the Designer Object of the userform.

Reply | Quote

May I start by thanking Hans & Jan for their prompt replies to my problem query.

Jan, I have checked my references within my project and I have ‘Microsoft Visual Basic 6.0 Extensibility’ referenced.
As I am currently teaching myself VB/VBA at home my knowledge of windows programming languages are some what wanting to say the least.
Could you advise me on how I would go about programatically using the designer object of my userform to facilitate the change to this property.

If you feel it is too complex a subject to cover this way, then could you point me to an internet site or book where I may acquire the knowledge I seek.

Thanking you once again.
Rodopi.

Reply | Quote

I could go into this and figure out how to do it (I’d have to put something together myself), but let me take the time to step back and discuss:

Why would you want to change things to a form’s design at design time rather than at runtime?

I could only see very few reasons to do that. Making changes at runtime does make sense and since they are relatively simple to do, why try and do it in the form’s designer object?

Note, that if your project is protected, making changes at design time by code is not possible.

Reply | Quote

It is at runtime I wish to carry out this change. However; as you pointed out my attempt at this won’t stick after userform closure.

Why do I want to do this?

The object happens to be a ComboBox who’s data is derived from cells on the worksheet and data is dynamically loaded at runtime via the RowSource definition specified when form was designed.
This data unfortunately is not of a fixed number and will, through time shrink and grow and I wanted to dynamically manage this via VBA’s elegant RowSource definition method.

I could of course, loop and load each item into my ComboBox until a null value is detected to achieve my objectives.
My only concern then would be how efficient that would be in terms of performance & time.
I guess you must be thinking…Well why don’t you try it and find out?…………I have no suitable answer to that question so I shall do just that.

As I am a newcomer to this language, it would have been nice to know how it could have been achieved.

You do raise a good point in terms of security about allowing design objects to be updated during runtime and I have taken that onboard….Thank you.

Reply | Quote

An alternate idea
You can set the combo box row source to be a named range.
The named range can grow/shrink and whatever on you and the control doesn’t care

Reply | Quote

An alternate idea
You can set the combo box row source to be a named range.
The named range can grow/shrink and whatever on you and the control doesn’t care

Reply | Quote

Developing gibbindr’s idea, try the following.

Assuming your data starts in cell R50 of a sheet named Data, and is terminated by a blank cell, you could include the following code in the UserForm module :

    Private Sub UserForm_Initialize()
        With Sheets("Data")
            .Range(.[R50], .[R50].End(xlDown)).Name = "DataList"
        End With
        Me.ComboBox1.RowSource = "DataList"
    End Sub

Changing the names as appropriate, this should achieve what you want.

Andrew C

Reply | Quote

Developing gibbindr’s idea, try the following.

Assuming your data starts in cell R50 of a sheet named Data, and is terminated by a blank cell, you could include the following code in the UserForm module :

    Private Sub UserForm_Initialize()
        With Sheets("Data")
            .Range(.[R50], .[R50].End(xlDown)).Name = "DataList"
        End With
        Me.ComboBox1.RowSource = "DataList"
    End Sub

Changing the names as appropriate, this should achieve what you want.

Andrew C

Reply | Quote

I hardly ever use the controlsource property and link that to a cell. If anything breaks, you encounter a problem.

I prefer to use VBA to fill the controls with their data, works fast. It does mean you need to do more coding, but it also gives more control.

To avoid loosing the data in your controls, don’t unload the form, simply hide it and show it again later on. It will have retained all values and selections.

Reply | Quote

I can confirm that the idea and solution submitted by gibbindr & Andrew Connelly works splendidly…….Thank you both for your assistance.

Jan, may I thank you too for all your help and too for all your useful pointers which I will go forward from here with.

Thanking you all for your prompt responses.

Regards
Rodopi

Reply | Quote

I can confirm that the idea and solution submitted by gibbindr & Andrew Connelly works splendidly…….Thank you both for your assistance.

Jan, may I thank you too for all your help and too for all your useful pointers which I will go forward from here with.

Thanking you all for your prompt responses.

Regards
Rodopi

Reply | Quote

I hardly ever use the controlsource property and link that to a cell. If anything breaks, you encounter a problem.

I prefer to use VBA to fill the controls with their data, works fast. It does mean you need to do more coding, but it also gives more control.

To avoid loosing the data in your controls, don’t unload the form, simply hide it and show it again later on. It will have retained all values and selections.

Reply | Quote

It is at runtime I wish to carry out this change. However; as you pointed out my attempt at this won’t stick after userform closure.

Why do I want to do this?

The object happens to be a ComboBox who’s data is derived from cells on the worksheet and data is dynamically loaded at runtime via the RowSource definition specified when form was designed.
This data unfortunately is not of a fixed number and will, through time shrink and grow and I wanted to dynamically manage this via VBA’s elegant RowSource definition method.

I could of course, loop and load each item into my ComboBox until a null value is detected to achieve my objectives.
My only concern then would be how efficient that would be in terms of performance & time.
I guess you must be thinking…Well why don’t you try it and find out?…………I have no suitable answer to that question so I shall do just that.

As I am a newcomer to this language, it would have been nice to know how it could have been achieved.

You do raise a good point in terms of security about allowing design objects to be updated during runtime and I have taken that onboard….Thank you.

Reply | Quote

I could go into this and figure out how to do it (I’d have to put something together myself), but let me take the time to step back and discuss:

Why would you want to change things to a form’s design at design time rather than at runtime?

I could only see very few reasons to do that. Making changes at runtime does make sense and since they are relatively simple to do, why try and do it in the form’s designer object?

Note, that if your project is protected, making changes at design time by code is not possible.

Reply | Quote

May I start by thanking Hans & Jan for their prompt replies to my problem query.

Jan, I have checked my references within my project and I have ‘Microsoft Visual Basic 6.0 Extensibility’ referenced.
As I am currently teaching myself VB/VBA at home my knowledge of windows programming languages are some what wanting to say the least.
Could you advise me on how I would go about programatically using the designer object of my userform to facilitate the change to this property.

If you feel it is too complex a subject to cover this way, then could you point me to an internet site or book where I may acquire the knowledge I seek.

Thanking you once again.
Rodopi.

Reply | Quote

Runtime changes to properties of userform controls do not “stick”, they are undone as soon as the form is unloaded from memory.

If you need to make design-time changes to a userform programmatically, you need to set a reference to the VBE extensibility library and use the Designer Object of the userform.

Reply | Quote