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...

Private Sub Workbook_Activate()
    On Error Resume Next
    Call Display_App_Toolbars
    On Error GoTo 0
End Sub
 
Private Sub Workbook_Deactivate()
    On Error Resume Next
    Call Display_User_Toolbars
    On Error GoTo 0
End Sub
Private Sub Define_Toolbar_Collections()
Dim AppBars As Object
Dim tb As Object
    Set AppBars = Application.CommandBars
    For Each tb In AppBars
        If tb.Visible Then
            UserToolbars.Add tb
        End If
    Next tb
    With AppToolbars
        .Add AppBars("Standard")
    '    .Add AppBars("Auditing")
    '    .Add AppBars("Formatting")
    '    .Add AppBars("Control Toolbox")
        .Add AppBars("Unit_calc")
    End With
End Sub


Private Sub Display_App_Toolbars()
Dim AppBars As Object
Dim tb As Object
    On Error Resume Next
    For Each tb In UserToolbars
        tb.Visible = False
    Next tb
    For Each tb In AppToolbars
        tb.Visible = True
    Next tb
End Sub


Private Sub Display_User_Toolbars()
Dim AppBars As Object
Dim tb As Object
    On Error Resume Next
    For Each tb In AppToolbars
        tb.Visible = False
    Next tb
    For Each tb In UserToolbars
        tb.Visible = True
    Next tb
End Sub

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

Code

Private Sub Workbook_Activate()
    On Error Resume Next
    Run "hide_excel_headers"
    On Error GoTo 0
End Sub


Private Sub Workbook_Deactivate()
    On Error Resume Next
    Run "show_excel_headers"
    On Error GoTo 0
End Sub

Display More

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 http://www.cpearson.com/excel/vbe.htm

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

The 'VBA Extensibility 5.3' box is ticked and the code works just fine until I switch on the protection.

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
Thanks.

Re: Looping through UserForms

Sorry I ran out of time yesterday - but I wanted to thank you for your help. Great suggestion. Problem solved.

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

I must be missing something in my system - my VBE doesn't recognise 'VBComponents' ( produces a 'User-defined type not defined' error)

Is it from an add-in?

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.

For Each Ctrl In myForm1.Controls
        If Ctrl.Tag <> "" Then
             ReDim Preserve myArray3(1, i)
             Ctrl.Value = Range(Ctrl.Tag).Value
             myStr = Ctrl.Tag
             myVal = Range(Ctrl.Tag).Value
             myArray3(0, i) = myStr
             myArray3(1, i) = myVal
             i = i + 1
        End If
    Next Ctrl
        For Each Ctrl In myForm2.Controls
        If Ctrl.Tag <> "" Then
             ReDim Preserve myArray3(1, i)
             Ctrl.Value = Range(Ctrl.Tag).Value
             myStr = Ctrl.Tag
             myVal = Range(Ctrl.Tag).Value
             myArray3(0, i) = myStr
             myArray3(1, i) = myVal
             i = i + 1
        End If
    Next Ctrl

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?
Thanks.