Autofill a macro

[willaby]

I currently have a worksheet that, using a Form Button, generates a new worksheet that formats column widths, titles and also creates a file list of all files on my hard-drive along with date created and the full file path.

Each file has all of its information in one row.

From the original button that initiates the whole process I need the code to create a delete button for each file in the list using, I presume, the kill function.

The delete button would need to sit in column 'L' starting in cell '5' and autofill down. The kill function would pull the file path of the file to kill from the full file path displayed in column 'K', again starting in cell '5'.

I already have the code to genereate the file information (path, date created etc). I literally just need the code for the creating the multiple form buttons and assigning the macro to each button.

Thanks, in advance.

 

[Peter_SSs]

Having a separate 'Delete' button for each file seems like a lot of overhead to me. What about using, say, the BeforeDoubleClick event to double click in column L next to any file you want to delete.

Something like this. (Be very careful!!)

<font face=Courier New><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> FPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Resp <SPAN style="color:#00007F">As</SPAN> VbMsgBoxResult<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Column = 12 And Target.Row >= 5 <SPAN style="color:#00007F">Then</SPAN><br>        FPath = Target.Offset(, -1).Value<br>        <SPAN style="color:#00007F">If</SPAN> FPath = "" <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "No file to delete"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Resp = MsgBox("This will delete " & FPath & vbLf & vbLf & _<br>                "Are you ABSOLUTELY SURE?", vbYesNoCancel)<br>            <SPAN style="color:#00007F">If</SPAN> Resp = vbYes <SPAN style="color:#00007F">Then</SPAN><br>            <br>                <SPAN style="color:#007F00">'Code to delete the file here</SPAN><br>                <br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[willaby]

Hi Peter

Thanks for that. It looks like a good solution but I can't get the double_click function to work.

I incorporated your code into what I already have so you have a clearer idea of what I'm looking for.

I'm not all that experienced with VBA so this is probably a little clumsy looking. Thanks in advance... again.

Sub Macro1()

Dim WS As Worksheet
    Set WS = Sheets.Add

With ActiveSheet.Range("k5")
    .Value = Array("C:\Users\admin\Documents\Office Documents\Excel\Test Folder\TEST DELETE DOC.xls")
    .Interior.Color = RGB(244, 164, 96)
    .Borders.Color = RGB(0, 0, 0)
    .Font.Bold = True
    .Font.Size = 8
    .Cells.VerticalAlignment = xlVAlignCenter
    .Columns.Cells.HorizontalAlignment = xlHAlignLeft
    Range("k5").Select
    Selection.AutoFill Destination:=Range("k5:k10"), Type:=xlFillDefault
    Range("A1").Select
End With

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
' Macro1 Macro
'

    Dim FPath As String
    Dim Resp As VbMsgBoxResult
    Dim KillFile As String
    
'
    If Target.Column = 11 And Target.Row >= 5 Then
        FPath = Target.Offset(0, -1).Value
        If FPath = "" Then
            MsgBox "No file to delete"
        Else
            Resp = MsgBox("This will delete " & FPath & vbLf & vbLf & _
                "Are you sure you want to delete this file?", vbYesNoCancel)
            If Resp = vbYes Then
            
                'Code to delete the file here
                
               KillFile = Range("G2").Select
                 Selection.Range.AutoFill Destination:=Range("G2:G10"), Type:=xlFillDefault
                 Range("G2").Select
 
            If Len(Dir$(KillFile)) > 0 Then
                SetAttr KillFile, vbNormal
             Kill KillFile
             End If
                
               
            End If
        End If
    End If
    
                 Range("k5").Select
                 Selection.Range.AutoFill Destination:=Range("k5:k10"), Type:=xlFillDefault
                 Range("a1").Select
End Sub

 

[Peter_SSs]

Hi Peter

Thanks for that. It looks like a good solution but I can't get the double_click function to work.

I incorporated your code into what I already have so you have a clearer idea of what I'm looking for.

The double-click code needs to go in the particular worksheet module, not in a standard module. An easy way to find the correct place is to right click the sheet name tab (in Excel not the VB editor) and choose 'View Code'. That's where the double-click code should go, not combined with your other code.

There may be some other issues with your actual code to do the deletions, but I will leave that for you to try to work out for now. In any case I don't know exactly what is in your sheet so cannot be sure of making suggestions at this stage.

 

[willaby]

That makes sense but how would I programmatically place the Before_DoubleCLick code in the new sheet from the original process initiation button?

The whole creation of the new sheet and related functionality (delete file function) needs to be produced entirely from one click by the user on the original sheet.

Thanks Peter

 

[Peter_SSs]

One way would be to create a 'Template' worksheet with the code already in it then instead of

Dim WS As Worksheet
Set WS = Sheets.Add

use something like

Dim WS As Worksheet
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Set WS = ActiveSheet

 

[willaby]

That looks like a plan that will work.

Thanks. Can I trouble you for one last bit of assistance?

I am struggling to get delete code to work in the first solution you provided.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim FPath As String
    Dim Resp As VbMsgBoxResult
    
    Application.EnableEvents = True
    
    If Target.Column = 12 And Target.Row >= 5 Then
        FPath = Target.Offset(, -1).Value
        If FPath = "" Then
            MsgBox "No file to delete"
        Else
            Resp = MsgBox("This will delete " & FPath & vbLf & vbLf & _
                "Are you sure you want to delete this file?", vbYesNoCancel)
            If Resp = vbYes Then
            
                ' Delete code here
            
                
            End If
        End If
    End If
End Sub

So the file path of the file I wish to delete is in column "k" and the "Delete File" text is in colum "L" - which the user will double click when they wish to delete that file.

What is the code that I need to place above where you originally state "Your Delete code here" ?

Thanks Peter. You've been really helpful.

 

[Peter_SSs]

So the file path of the file I wish to delete is in column "k" and the "Delete File" text is in colum "L" - which the user will double click when they wish to delete that file.

Just to confirm exactly what you have, could we have a few samples of the col K & col L data?

 

[willaby]

Hi Peter

Column K contains a file path:

Z:\Source\Folder\Attachments\Forms\doc.xls

Colum L, by my understanding would just have the words "Delete File" in it that would then, when doubleclicked, delete / kill the file that is listed in column k. Also, if possible, an indicator in column M to say that file has been deleted would be good.

Thanks Peter

Will

 

[Peter_SSs]

Try this:

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> FPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Resp <SPAN style="color:#00007F">As</SPAN> VbMsgBoxResult<br>    <br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Column = 12 And Target.Row >= 5 <SPAN style="color:#00007F">Then</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        FPath = Target.Offset(, -1).Value<br>        <SPAN style="color:#00007F">If</SPAN> FPath = "" <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "No file to delete"<br>        <SPAN style="color:#00007F">ElseIf</SPAN> Target.Value = "Deleted" <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "File already deleted"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Resp = MsgBox("This will delete " & FPath & vbLf & vbLf & _<br>                "Are you sure you want to delete this file?", vbYesNoCancel)<br>            <SPAN style="color:#00007F">If</SPAN> Resp = vbYes <SPAN style="color:#00007F">Then</SPAN><br>                Kill FPath<br>                Target.Value = "Deleted"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>