Posts by richardcranney

    Re: Search folder and display files

    Thanks Ger,

    Had to amend it slightly to:

    ActiveSheet.Hyperlinks.Add ActiveSheet.Cells(r, 9), Address:=FileItem.Path

    but it works prefectly.

    Just a little niggle now, is there any way of making it have the link open in a new window, like the HTTP files use Target="_new"?


    Re: Search folder and display files

    Aha, just added the Microsoft Scripting runtime, you were right with the dll name.

    Your bloody good man, very good.

    Any ideas how to get it to make a link to the file, I tried

    Cells(r, 9).Hyperlinks.Add , Address:=FileItem.Path & FileItem.Name

    but guess what, it didnt work.

    Hello all,

    I have recently searched the forum and found the following post:
    which is something that I am wanting to use in my spreadsheet.

    When using this, i get a compile error on:

    FSO As Scripting.FileSystemObject

    The code I am using is the following:

    This has been placed in the Sheet1 and i use a button to run the Macro.

    Can anyone help? Thanks

    Re: Close/Open Userforms on Mouse Click

    hi Andy,

    sorry to be a pain, iv e searched the forums for the .onaction code and tried to use it, but i get an error, just wondered if you could help.

    The code is:

    And i get the message:

    Compile Error: Method or data member not found.

    Re: Close/Open Userforms on Mouse Click

    Because that uses menus at the top, ie a file, edit, view, favourites.

    My users have a row of buttons at the top of the excel sheet, such as IDLE - CFT, IDLE - Non CFT, Approaches.

    When the user clicks this, i was wanting a menu to appear, i think the easiest way is to open a userform underneath which I can do by using this method:
    C Pearsons way

    But if the user clicks off the userform, im trying to get it to close.


    I have a menu structure in place so when a user clicks a button, it opens a Userform as a menu. The only problem i have is that if the user doesnt want to use that menu, and clicks on the spreadsheet, it doesnt dissapear.

    Is there any VBA code that anyone knows that when a user clicks on the spreadsheet, or even just clicks their mouse anywhere (ie other menus at the top, spreadsheet, desktop) like a mouseout code on HTML so the menu will dissapear?

    Thanks in advance and thanks to everyone who has helped in previous posts.



    I have a series of numbers in cells B7:G17 and then a row of numbers in B25:G25.

    What i am looking to do is if any of the numbers in the row B25:G25 are the same as the numbers in B7:G17, the cell in B7:G17 glows red. I tried with conditional formatting, but couldnt get the desired effect, can anyone help?


    Re: Running Cumulative

    Explaining a bit better.

    All the cells in Cell C will have a value, whether this is 0 minutes or 1 minute to 23:59 hours.

    The formula there counts the 0 minutes as being a number, so doesnt follow. Is there anyway of making it find a number > 1 minute? Thanks

    Re: Running Cumulative

    Hi, sorry for the long delay, but i cant seem to get that working. All the cells in D have the formula to subtract cell A from cell B, so it has a 0, which means it always has a number in there.

    Any other ideas? Thanks

    Re: Open a Userform next to mouse cursor

    Quote from norie


    Perhaps you could take a look at the worksheet event BeforeRightClick?

    Thanks Norrie,

    The beforerightclick lets you do some code before the actual menu appears doesnt it?

    Im actually wanting to create a new menu, just cant favem out how to create one like the one when you rightclick.

    Re: Open a Userform next to mouse cursor

    I've also been looking at creating menu's. Using the ozgrid guide, I can now create menus such as the file, edit, view, favourites, tools and help.

    Is there a way of recreating the Right mouse button menu when left clicking on the object, but with my custom menu in there?

    Re: Open a Userform next to mouse cursor

    Quote from royUK

    have you checked Chip Pearson's Form Positioner as suggested earlier?

    Yes I did, but its bloody hard to work round and understand what does what. Just trying to get my head round it, not easy stuff!

    Re: Open a Userform next to mouse cursor

    Its just really to have a custom menu appear.

    when the user clicks on the button 'Accounts' at the top of the spreadsheet, either a menu, or userform as roy suggested appears next to the mousepointer.

    Im whizzing through some google searches at the minute and putting 4 or 5 pieces of code together to see if i can get the deisred effect, but any help would be appriciated.