Posts by DeepPurple

    Re: Forms Toolbar Versus Control Toolbox Toolbar

    Thanks Dave - I guess my 'Search' skills aren't great - I hadn't found this article before.
    One problem I've come across with these 'ActiveX' controls, especially on Dell computers, is a frequent 'Insufficient system resources' error message when viewing at magnifications other than 100%. Updating graphics drivers doesn't solve the problem.
    Using the 'Forms' controls seems to stop this happening (but of course has other limitations as your article describes).

    Does anyone have experience or knowledge of which of these toolbars provides the better 'controls' to paste onto worksheets (as opposed to UserForms)?
    For those wondering, both toolbars contain some apparently similar controls, e.g., combo box, radio button, spinner etc. but there are differences in their behaviour it would seem...

    Does anyone know at which version of Excel the app started to look for the Workbook_Open code?

    I'm pretty sure the Office 97 version of Excel (version 8.0e?) only looks at the auto_open code (and not the Workbook_open stuff)...
    Things are all well and good if users/customers are using the current versions of Excel etc. but many of our clients are still using Office 97:(

    It looks like the workaround might be to do a version check in auto_open and if less than a certain value, include a copy of the workbook_open code (I couldn't find a way of calling the code directly)

    Re: Show/Hide toolbars for current workbook only

    They are all 'Private Sub's in the 'ThisWorkbook' object(?) of the spreadsheet.
    When using 'Run' the execution just steps right through that line without apparently doing anything.
    I have appended the code I am using in case it helps...

    Re: Show/Hide toolbars for current workbook only

    Thanks Dave - you have saved my bacon again! Your suggested code works fine for the app I'm working on at the moment too - but with one minor alteration.
    'Run' doesn't seem to call my required 'subs' but using 'call' does.
    Any ideas why please?

    Quote from Dave Hawley

    I would use the Workbook Active and Deactivate to do this. E.g

    Re: Project Protection / Module Protection / Module Visibility...

    Thanks for your help royUK - Unfortunately I've already done that. I think this is an insurmountable problem. I just came across this on the web...
    "NOTE: In all versions of Excel, the VBProject must not be protected. If it is, these procedures will fail. There is no programmatic way to unlock a locked project. In Excel 2002, you must have "Trust Access To Visual Basic Project" enabled. To enable this setting, go to the Tools menu in Excel, choose Macros, Security, then the "Trusted Sources" tab, and put a check next to "Trust Access To Visual Basic Project". Otherwise, you will get errors." from

    Re: Project Protection / Module Protection / Module Visibility...

    I'm getting the same error - but AFAIK my code is not supposed to be changing any of the modules. My code is using a 'VBA extensibility' reference to interrogate all the forms in the workbook for certain types of controls.

    Dim lngIndex As Long
    Dim objComp As VBComponent
    Dim lngItem As Long
    Dim objCnt As Object
    For Each objComp In ThisWorkbook.VBProject.VBComponents
        If objComp.Type = vbext_ct_MSForm Then

    ...etc. (code incomplete to protect the innocent!)

    Does anyone have any ideas why there is a problem (and if there is a way around it)?
    Unprotecting the code isn't an option ;)

    Re: Looping through UserForms

    Wow! what a powerful piece of work this library is :thanx:
    The code you have suggested also seems to work fine with the Office97 version of the extensibility library by the way.
    Just saving my Excel file with this reference 'ticked' will mean that this x() procedure will run on anyone else's PC won't it?

    Re: Looping through UserForms

    Hi Andy,
    OK - here goes.
    Each form contains controls (text boxes & combo boxes) that have 'Tag' names - these match named ranges (usually single cells) in the workbook.
    These 'tag'ged controls represent every designated 'user input' cell that exists in the workbook.
    I am trying to generate a file that contains all these user input values, eg. to reconfigure a new workbook at a later date.
    Rather than interogating every cell in the workbook, it seemed to be a good idea to use the controls already existing on my forms 'myfrmN' (there is approx. one form per worksheet). So far the only way I have found of accessing these forms is to open each one explicitly, ie.

    As you can see the code is the same - just the method for accessing the forms is proving difficult via VBA code.

    Hope this helps you understand what I am doing a little better?

    I have a number of userforms named "myfrmN" where N = 1 to 10, ie. myfrm1, myfrm2 etc.
    I need to collect data from each of these forms in turn and save it. (That's the easy bit).

    I would like to know if there is a programmatic way of loading each of these forms in turn? ie. using the 'N' as the loop variable.

    Can anyone offer some guidance please?