Posts by mattalin

mattalin · Jun 9th 2016

Referencing an old thread: http://www.ozgrid.com/forum/showthread.php?t=147605&page=1 but hoping to improve on the code.

I used the code and it works great as long as only one excel source file is linked to the Presentation. Was hoping code could be modified to do the following (assuming there are more than one linked files):

1. Scan the PPT file and list all external excel source files within the document.
2. Allow the user to pick one of the existing source files.
3. Apply the below code to change the selected old source file with the the newly selected source file.

Code

Sub M1()
Dim sld As Slide
Dim sh As Shape
Dim ExcelFileNew
Dim exl As Object
Set exl = CreateObject("Excel.Application")
'Open a dialog box to promt for the new source file.
ExcelFileNew = exl.Application.GetOpenFilename(, , "Select Excel File")
Call stripPath(ExcelFileNew, filenameNew)
For Each sld In ActivePresentation.Slides
For Each sh In sld.Shapes
If sh.Type = msoLinkedOLEObject Then
With sh.LinkFormat
LinkOld = .SourceFullName
Call stripReference(LinkOld, fullpathOld)
Call stripPath(fullpathOld, filenameOld)
LinkNew = Replace(LinkOld, filenameOld, filenameNew)
.SourceFullName = LinkNew
'Call showStrings(ExcelFileNew, filenameNew, LinkOld, fullpathOld, filenameOld, LinkNew)
End With
End If
Next sh
Next sld
End Sub
'
Sub stripPath(fullPath, filename)
'This will take c:\folder\workbook.xlsx* and provide workbook.xlsx*
Dim filenamePosition As Long
filenamePosition = InStrRev(fullPath, "\")
filename = Mid(fullPath, filenamePosition + 1, Len(fullPath) - filenamePosition)
End Sub
'
Sub stripReference(fullReference, filename)
'This will take *workbook.xls!Graphs![workbook.xls]Graphs Chart 1 and provide *workbook.xls
Dim referencePosition As Long
referencePosition = InStr(1, fullReference, "!")
filename = Left(fullReference, referencePosition - 1)
End Sub
'
Sub showStrings(ExcelFileNew, filenameNew, LinkOld, fullpathOld, filenameOld, LinkNew)
'This is just a debugging function to display the variables
MsgBox ("ExcelFileNew: " & ExcelFileNew & vbNewLine _
& "filenameNew: " & filenameNew & vbNewLine _
& "LinkOld: " & LinkOld & vbNewLine _
& "fullpathOld: " & fullpathOld & vbNewLine _
& "filenameOld: " & filenameOld & vbNewLine _
& "LinkNew: " & LinkNew)
End Sub

Display More

mattalin · Apr 26th 2006

Re: SUMIF Function based on Row selected

I pretty much figured out how to make a UDF out of the change event:

Code

Function SUMIFD(rngStatic As Range, Criteria As Range, rngInput As Range)
Application.Volatile True
If Application.Intersect(ActiveCell.EntireRow, rngInput) Is Nothing Then
SUMIFD = "Out of Range"
Else
SUMIFD = Application.WorksheetFunction.SumIf(rngStatic, Criteria, Application.Intersect(ActiveCell.EntireRow, rngInput))
End If
End Function

The only thing missing is the "Out of Range" piece doesn't seem to work correctly. I wanted to make it say "out of range" or return zero when the user selects a cell outside the intersection.

mattalin · Apr 25th 2006

Re: SUMIF Function based on Row selected

Thanks, this works great for the "Worksheet_SelectionChange" event, however would it be easy to convert this code into a UDF to make it more flexible when implementing into a large spreadsheet with multiple ranges - i.e. without having to manipulate the code for each occurance?

mattalin · Apr 25th 2006

Re: SUMIF Function based on Row selected

Here is what I have so far.

Code

Function SUMIFD(rngStatic As Range, Criteria As Range)
Set AreaRange = ActiveCell.CurrentRegion 'Would like this to be based on Yellow-shaded
'cells (since they will start out all zero values)
Set rngDynamic = Application.Intersect(AreaRange, ActiveCell.EntireRow)
SUMIFD = Application.WorksheetFunction.Sum(rngDynamic)
End Function

I realize I am missing the criteria matching the static range (among other things). I would like the code to do the following:

1) Allow the UDF to be placed in a particular cell, in this example F3.

2) The UDF would sum the cells - that match the first criteria (FY2006) in E3, based on the range B7:R7 (rngStatic) - based on the intersection between the yellow-shaded region and active row.

For example, if the user were to select cell B10 then the value in F3 would be ~$81. If they were to enter $10 in cell F10, then F3 would equal ~$91. If the user were to then select cell J14, then the value of F3 would show $187 and the value of F4 would show $278.

mattalin · Apr 22nd 2006

I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.

background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.