Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Paisley, Scotland
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Paisley, Scotland
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Paisley, Scotland
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Virginia Beach, Virginia, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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 :<pre> Private Sub UserForm_Initialize()
    With Sheets("Data")
    .Range(.[R50], .[R50].End(xlDown)).Name = "DataList"
    End With
    Me.ComboBox1.RowSource = "DataList"
    End Sub</pre>

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

    Andrew C

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    New Lounger
    Join Date
    Mar 2002
    Location
    Paisley, Scotland
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •