Posts by richardcranney

Re: Search folder and display files

Yeah, thats the one.

When they click the link which appears, it opens this in a new window. or thats what im hoping for.

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"?

Thanks

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:
http://www.ozgrid.com/forum/showthread.php?t=54151&highlight=display+folder+contents
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:

Sub TestListFilesInFolder()
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "File Name:"
    Range("B3").Formula = "File Size:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("E3").Formula = "Date Last Accessed:"
    Range("F3").Formula = "Date Last Modified:"
    Range("G3").Formula = "Attributes:"
    Range("H3").Formula = "Short File Name:"
    Range("A3:H3").Font.Bold = True
    ListFilesInFolder "J:\IT Development\EMTECH.GRP\PCBANKIN\Dukes' Hazards'\Chatshop\mailings", True
     ' list all files included subfolders
End Sub
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
     ' lists information about the files in SourceFolder
     ' example: ListFilesInFolder "C:\FolderName\", True
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
         ' display file properties
        Cells(r, 1).Formula = FileItem.Path & FileItem.Name
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Attributes
        Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
         ' use file methods (not proper in this example)
         '        FileItem.Copy "C:\FolderName\Filename.txt", True
         '        FileItem.Move "C:\FolderName\Filename.txt"
         '        FileItem.Delete True
        r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

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:

Sub MakeIt()
     
On Error Resume Next
    Set myBar = CommandBars("CustomPopup")
    If myBar Is Nothing Then
        Set myBar = CommandBars _
        .Add(Name:="CustomPopup", Position:=msoBarPopup, Temporary:=False)
        With myBar
            .Controls.Add(Type:=msoControlButton).Caption = "IDLE - CFT"
                .OnAction = "idle_cft"
                .BeginGroup = False
            .Controls.Add(Type:=msoControlButton).Caption = "IDLE - Non CFT"
            .Controls.Add(Type:=msoControlButton).Caption = "OPI"
            .Controls.Add(Type:=msoControlButton).Caption = "Four"
            .Controls.Add(Type:=msoControlButton).Caption = "Five"
        End With
    End If
     
End Sub

And i get the message:

Compile Error: Method or data member not found.

Re: Close/Open Userforms on Mouse Click

Woah, thats cool, and it works bloody well too. Your a genious Andy!

Any ideas on getting the buttons to run Macros?

Thanks

Re: Close/Open Userforms on Mouse Click

Oooo, that sounds interesting. I know how to create extra parts into the right hand menu.
Do you have any more information on this? Thanks

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.

Hello,

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.

Rich

Re: Running Cumulative

That my friend, is perfect!

Re: Think this is conditional formatting

Peeerrrfect! works a treat, thanks wigi. Any ideas on my other post? the Cumulative thingy majig?

Hello,

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?

Thanks

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

Hello, wondered if anyone could help.

Im wanting to run a running cumulative which only sits next to the last total on a spreadsheet.

Ive added an example to see if my rubbish explanation can be explained further.

Thanks

Re: Open a Userform next to mouse cursor

Quote from norie

Richard

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

Hi Roy, Norrie,

Ive concuted this, The only problem is it seems to open the userform a bit offset to the mouse pointer and it changes where it opens depending on where abouts in the button you click.

Any ideas?

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.

Thanks