Posts by Bob Phillips

Re: Handle User-defined Errors Raised In An Inaccessible Project

Unless the project returns an error code, I don't see that you can trap it.

Re: Create Dynamic Validation Lists From Array

It works fine for me if the name DV[sListname] is a defined range name.

Re: Sum Across Worksheets Named In 2 Cells

My solution works perfectly okay, but it does asume that the sheets are called Sheet1, Sheet2 etc., and then the 1:3 part refers to the first and final sheet number.

Do note however that the post managed to get a space injected into the function before Sheet.

Re: Msgbox To Prompt User To Select Value If Found Twice

Look at FindNext in help, there is a good example there.

Re: Editing Controls In 2007

You don't, you need the custom UI editor from MS to link it to the workbook. Get it at http://openxmldeveloper.org/ar…05/26/CustomUIeditor.aspx

And read about it at http://msdn.microsoft.com/en-u…/cc508991(office.11).aspx

Re: Run "doit" To Accept My Sheets

Why do you select one then another, you can only have one active.

Re: Lookup 2 Way Between High/low Range

=INDEX($M$2:$M$12,MATCH(1,($H$2:$H$12=A2)*(B2>=$I$2:$I$12)*(B2<=$J$2:$J$12)*(C2>=$K$2:$K$12)*(C2<=$L$2:$L$12),0))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

Re: Hide Rows Based On Range Values

Sub HideRows()
Dim LastRow As Long


    With Worksheets("table")
    
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Columns("E:E").Insert Shift:=xlToRight
        .Range("E2").Resize(LastRow - 1).FormulaR1C1 = _
            "=OR(ISNUMBER(MATCH(RC[-2],names!C[-3],0)),ISNUMBER(MATCH(table!RC[-1],names!C[-2],0)))"
        .Range("E1").Value = "Temp"
        .Columns("E:E").AutoFilter Field:=1, Criteria1:="TRUE"
    End With
End Sub

Re: Function Arguments Of User Defined Functions

This is not provided within Excel, but Laurent Longre's has written a DLL utility, named Funcustomize, which provides these.

It is free, and you can find it at

http://xcell05.free.fr/

ADDED BY ADMIN

For custom functions, thes pages may also be of use to you

Adding a Description and Category to your User Defined Functions in Excel
AND
Add Excel UDF/Custom Function to a Category & Add a Description

Re: Pass Workbook Name After Save

Yes I believe so, my code was specifically created to give an After Save event.

As I said, you can insert code after the places where the workbook is saved to do whatever you want.

Re: Referencing Objects In Spreadsheet

Put this in a class module called clsActiveXEvents

Option Explicit


Public WithEvents mImages As MSForms.Image


Private Sub mImages_Click()
    MsgBox "This image name is " & mImages.Name
End Sub

and in the sheet module add

Dim mcolEvents As Collection


Private Sub Worksheet_Activate()
Dim cImages As clsActiveXEvents
Dim shp As Shape
    
    Set mcolEvents = New Collection


    For Each shp In Me.Shapes
        If shp.Type = msoOLEControlObject Then
            If TypeOf shp.OLEFormat.Object.Object Is MSForms.Image Then
                Set cImages = New clsActiveXEvents
                Set cImages.mImages = shp.OLEFormat.Object.Object
                mcolEvents.Add cImages
            End If
        End If
    Next


End Sub

Re: Pass Workbook Name After Save

Creating an after-save macro is trivial, and I think that is basically what I gave you.

You probably need to change the ThisWorkbook references to wb, but I show placeholders where you can add further code.

Re: Run Macro Sequence On Set Cell Changed By Drop-Down

IMO it does, because the OP might get the wrong impression as to what he can and what he cannot do with Sheet modules and ThisWorkbook. It may not be a direct respones to his question, but it does extend the information.

Re: Run Macro Sequence On Set Cell Changed By Drop-Down

That doesn't make it private, just makes it a class.

Re: Run Macro Sequence On Set Cell Changed By Drop-Down

Quote from Dave Hawley

Insert>Module while in the VBE

The Sheet & ThisWorkbook Modules are Private.

I don't think any modules are Private by default, in fact they are not private at all in the context of how stated I believe.

The procedures in a Sheet and ThisWorkbook module may be private by default, but they can be changed to Public very easily, and other public procedures can be inserted within these modules. Referring to these procedures may need to be qualified with the module name, but it is very easily done.

Private modules only refers to modules that are private to the Project, not per se, and that can apply to any module, including general modules.

Re: Formula Show Blank Instead Of Zero

=IF('BP Machar'!C10="","",'BP Machar'!C10)

Re: Working With Multiple Userforms

Private Sub CommandButton1_Click()
Dim counter1 As Long
Dim myFrame As MSForms.Control
Dim myframeresult As Variant


    For counter1 = 1 To 30
         
        Set myFrame = Me.Controls("Frame" & counter1)
        myframeresult = "myframeres" & counter1
         
        Dim objControl As Control
         
        For Each objControl In myFrame.Controls
        
            If objControl.Value Then
                 
                myframeresult = objControl.Caption
                Exit For
            End If
        Next
    Next counter1
     
End Sub

Re: Select Range Change Color Of Empty Cells

Dim rng As Range


    On Error Resume Next
    Set rng = Range("B7:G12").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rng Is Nothing Then
    
        rng.Interior.ColorIndex = 4
    End If

ADDED BY ADMIN
Select the range B7:G12 and go to Conditional Formatting and use =ISBLANK(B7)

Re: Control Order Of Class Object Events

Why use events, why not just trap the new worksheet event and call the two procedures in the order that you want?

Re: Count Between Date Range &amp; Text Criteria

Seems a bit pointless offering any more PCI, it looks like the OP is another who has got himself banned.