Excel Macro Help

[andreew]

Hi,

I am pretty new to all this so please forgive my ignorance
I have created an excel sheet which holds all the movie's I own and a macro which allows me to add a new movie, find a movie, delete a movie, etc.

The problem is when I search for a movie using my 'FIND' button and entering my search, the worksheet scrolls through the movie's and finds the movie's which meet my criteria alphabetically (first word). Instead I would like for the macro to open a new box or even sheet which contains all the movie's which contain the word/phrase I have entered in my search field.

So I was wondering if anyone has any ideas about how I should change my code or how I should go about doing it. More information can be provided as needed.

thanks,

 

[PHV]

how I should change my code
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

You could use advanced filter.
Filter criteria on the Output page, Use events to detect a change in the criteria range and operate the filter. Use named ranges and .currentregion to cope with new titles added to your list and allow you to add/delete criteria. Like this:

 Range("Alldata").currentregion.AdvancedFilter _
  Action:=xlFilterCopy, _
  Criteria:=myCriteria.CurrentRegion
  CopyToRange:=Range("myResults"), _
  Unique:=False

Gavin

 

[andreew]

Hi Gavona,

Thanks for your reply that helped a lot. Just another quick question, in my movie list I have about about 3000 movie's in range A9 to about A3000 if that makes sense, I would like to link an image to each movie so that the user can right click and choose the option to 'View Cover' and the image corresponding to that movie would be displayed. Do you have any idea about how I should go about doing this?

Andrew

 

[UnsolvedCoding]

There are several ways to connect an image to what you are describing.

What is your skill level in coding?

If you are coding VBA as a full time job you get talked to in a different way then if you do this at home for fun.

I don't say that to be vague, there are several ways to code this that come to mind and giving you an anwer that is for someone who codes at home occasionally vs someone who does this full time will make a difference in communication.

 

[UnsolvedCoding]

By the way - I did see you said you were new to coding, but I am looking for how new you are. In otherwords have you taken classes on VBA or are you learning it from a book or are you learning it as you go?

 

[Gavona]

Have a look (without code) at the hyperlink function.

=HYPERLINK(mypath&A9&".jpg",A9)
Where myPath is a named range containing the path to the folder containing all your covers and assuming the covers have the name of the album.

A simple click would open the file.



Gavin

 

[andreew]

Hi guys thanks for your answers, yes I was a little vague in my description of my skill level sorry. I have done a small amount of VBA coding in my spare time at home before but never taken lessons and mainly learned from what I have read from other people. So I am a beginner but happy to learn :L. I have a couple of books on VBA but wasn't sure where to read haha. Gavona I have not had a chance to have a look at the hyperlink function yet but I will and than get back to you.
Any ideas you guys can give me about going about doing it is great and I'm just looking for someone to point me in the right direction.

Thanks.

 

[Gavona]

Welcome to the forum! You can learn loads by reading posts and FAQs here. You posted a clear description of what you are trying to achieve, which is great. However when posting here in the VBA forum you would normally be expected to post the code that you have tried - hence PHV's post. You will find you learn better getting specific feedback that way too.

Interestingly the core elements of the solution that I have posted can all be done without VBA. So part of the challenge is to be aware of the standard capabilities of Excel.

My last answer could also have been:
=HYPERLINK(mypath&A9&".jpg","Click here for picture of the cover")

If you want this to work on your extracted list then you may have to be creative. (An interesting challenge for you). On the other hand the business case for having a separate list is not clear to me - so maybe consider modifying the advanced filter to filter in place instead.


Gavin

 

[andreew]

Hi guys thanks for all your answers. I am a little hesitant to use a formula like the hyperlink on my worksheet because i want it to look nice :L so do you think it is possible to have all the images located in one folder and write a macro to link the file name of the image and the name of the movie in each row and open the images via the right click option - i have already written the function to add a new right click option and have that ready just to write the code to open the images.

Thanks
andrew

 

[Gavona]

Yes, it is possible. What code have you got so far? You want to add a hyperlink to the relevant right click option.


I don't see the problem with the hyperlink not looking nice. It can display whatever text you like in whatever font (including Wingdings etc)and formatted as you like.
Or as I was thinking you hide column A (containing the movie name and use instead the hyperlink displaying the movie name but actually clickable to open the cover).

Gavin