Advanced Filter Question

On Mon, 16 Nov 2009 07:42:00 -0800 (PST), Herbert Seidenberg
wrote:

Excel 2007
Advanced Filter
Turned Macro into an Event Macro.
http://www.mediafire.com/file/jngxzj...11_14_09b.xlsm


That one is cool. Thanks!

I now use a "button" to "pop" my DVD case images into my sheet.

It starts by deleting the previous image, so there is no pile of shots
there.

It also relies on the FlicNum value, and works quite well. Though the
lookup occurs before the macro runs, so the macro does not reference it.
It references a file name I pass to it.

Can I place my routines inside yours, and have the DVD image 'pop' in
whenever I change FlicNum?

Here is my current code.

The only other question I have is Can you place "table4" in another
sheet, and modify your code to point at and fill that table in the other
sheet?

I was able to do it with the button macro, but the always on macro
doesn't want to let me put the other sheet name in place of "Data" in the
Table4 reference area.

My code pops in a picture. Yours pops in a list. Mine shows up as a
macro, your only under "code". I do not understand the difference in how
things are declared here. Anyway, if you could encapsulate my routine
inside yours and place "Table4" on another sheet, I will be able to adapt
it into my sheet names.

I can supply those structures as well, if needed, but I did pretty well
adapting the button macro version.

Here is my code:

Sub Pop()
On Error Resume Next
ActiveSheet.Shapes("Popped").Delete
InsertPicture Range("H7").Value, _
Range("H7:I22"), "Popped"
End Sub
Sub InsertPicture(PictureFileName As String, TargetCells As Range,
picName As String)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
'Name the picture so you can delete it later....
p.Name = picName
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

I have a sheet called "Master_Pane", which is where I want table 4 to
end up.

The "Data" table is called "Acted_In" and the fields (headers) are
called "ID" for the flic number and "Actor_id" is the number which is
referenced to on the "Actors" sheet as "Actor_id" and "Actor".

That is how I keep the actor list only once by using numbers in the
"Acted_In" reference and doing a lookup on the numbers in the Actors
sheet. Otherwise the database (spreadsheet) size nearly doubles.

Also "FlicNum" is on another sheet (Master_Pane). I get a fault when I
move it there since you tie it all to one sheet.

On Mon, 16 Nov 2009 20:57:43 -0800, Pieyed Piper
g wrote:

On Mon, 16 Nov 2009 07:42:00 -0800 (PST), Herbert Seidenberg
wrote:

Excel 2007
Advanced Filter
Turned Macro into an Event Macro.
http://www.mediafire.com/file/jngxzj...11_14_09b.xlsm


That one is cool. Thanks!

I now use a "button" to "pop" my DVD case images into my sheet.

It starts by deleting the previous image, so there is no pile of shots
there.

It also relies on the FlicNum value, and works quite well. Though the
lookup occurs before the macro runs, so the macro does not reference it.
It references a file name I pass to it.

Can I place my routines inside yours, and have the DVD image 'pop' in
whenever I change FlicNum?

Here is my current code.

The only other question I have is Can you place "table4" in another
sheet, and modify your code to point at and fill that table in the other
sheet?

I was able to do it with the button macro, but the always on macro
doesn't want to let me put the other sheet name in place of "Data" in the
Table4 reference area.

My code pops in a picture. Yours pops in a list. Mine shows up as a
macro, your only under "code". I do not understand the difference in how
things are declared here. Anyway, if you could encapsulate my routine
inside yours and place "Table4" on another sheet, I will be able to adapt
it into my sheet names.

I can supply those structures as well, if needed, but I did pretty well
adapting the button macro version.

Here is my code:

Sub Pop()
On Error Resume Next
ActiveSheet.Shapes("Popped").Delete
InsertPicture Range("H7").Value, _
Range("H7:I22"), "Popped"
End Sub
Sub InsertPicture(PictureFileName As String, TargetCells As Range,
picName As String)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
'Name the picture so you can delete it later....
p.Name = picName
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

I have a sheet called "Master_Pane", which is where I want table 4 to
end up.

The "Data" table is called "Acted_In" and the fields (headers) are
called "ID" for the flic number and "Actor_id" is the number which is
referenced to on the "Actors" sheet as "Actor_id" and "Actor".

That is how I keep the actor list only once by using numbers in the
"Acted_In" reference and doing a lookup on the numbers in the Actors
sheet. Otherwise the database (spreadsheet) size nearly doubles.

All routine operations
Zzzz....Zzzz...

On Tue, 17 Nov 2009 11:04:11 -0800 (PST), Herbert Seidenberg
wrote:

All routine operations
Zzzz....Zzzz...

So, why will the button macro let me place Table4 on another sheet, but
the always running macro will not? Also, I need to examine FlicNum from
it's location on another sheet.

Though perhaps routine for you, I am having a hard time declaring more
than one sheet in "With sheets()". I suspect that is not the correct way
to do it, but I do not know, so it is not routine for me.

On Tue, 17 Nov 2009 17:44:33 -0800, Pieyed Piper
g wrote:

On Tue, 17 Nov 2009 11:04:11 -0800 (PST), Herbert Seidenberg
wrote:

All routine operations
Zzzz....Zzzz...

So, why will the button macro let me place Table4 on another sheet, but
the always running macro will not? Also, I need to examine FlicNum from
it's location on another sheet.

Though perhaps routine for you, I am having a hard time declaring more
than one sheet in "With sheets()". I suspect that is not the correct way
to do it, but I do not know, so it is not routine for me.


Is "Zzzz.....Zzzz..." A count of your IQ point total, Herbert?

WAKE UP!

Meow