how to reference the same row

[sassriverrat]

If I have this piece of code to reference each row in a continuing loop, how would I write to reference just the same row for which the button was hit (i.e. if a button was over cell a1, then it would run the code for a the row 1, but if the same code was run for a button in a4, it would run the code for row 4). Thanks

LastRow = Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row
For rw = 4 To Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row
next rw

it's for tweaking this piece of code to hit just one row instead of every row

Sub Downloady()Dim URL As String
Dim tstamp As String
Dim Folder0 As String
Dim Folder1 As String
Dim Folder2 As String
Dim folder3 As String
Dim Namer As String
Dim Date0 As String
Dim Date1 As String
Dim Date2 As String
Dim Date3 As String
Dim Divider As String
Dim LocalFilePath As String
Dim TempFolderOLD As String
Dim OldFinalName As String
Dim TempFileNEW As String
Dim DownloadStatus As Long
Dim LastRow As Long
Dim Finalname As String
Dim btn As Shape
Dim MyFSO As FileSystemObject
Set MyFSO = New Scripting.FileSystemObject


Dim rw As Long


    ' find last row of data in column B on 'Background'
    LastRow = Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row


    ' loop through rows on 'Background'
    'For rw = 4 To LastRow
    For rw = 4 To Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row
            
            With Sheets("Background")
            Namer = .Range("B" & rw)    'Pub name
            URL = .Range("I" & rw)      'URL to download
            Date0 = .Range("C" & rw)    'Week #
            Date1 = .Range("E" & rw)    'Year #
            Divider = .Range("D" & rw)  '\
            Date2 = .Range("G2")        'base week
            Date3 = .Range("I3")        'base year
        End With
        
        With Sheets("Setup")
            Folder0 = .Range("B5")    'temp folder (desktop)
            Folder1 = .Range("B7")    'permanent folder (desktop)
            Folder2 = .Range("C7")    'permanent folder
            folder3 = .Range("C5")    'temp Folder
        End With
        
        TempFolderOLD = Environ("Userprofile") & "\" & Folder0 & "\" & folder3
        tstamp = Format(Now, "mm-dd-yyyy")
        TempFileNEW = TempFolderOLD & "\" & Namer & ".pdf"
        LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\" & Folder2
        OldFinalName = LocalFilePath & Finalname
        
        
        'If these criteria are met, let's begin the download tree
        If Date0 <> Date2 And Date1 <> Date3 Then
    
            'Let's assign everything to the temp folder
            'Begin by clearing any possible undeleted/corrupted files from my "temp" folder
            If MyFSO.FolderExists(TempFolderOLD) Then MyFSO.DeleteFolder (TempFolderOLD)
            'Make a new temp folder
            If Not MyFSO.FolderExists(TempFolderOLD) Then MkDir (TempFolderOLD)
            'Attempt download to the temp folder
            DownloadStatus = URLDownloadToFile(0, URL, TempFileNEW, 0, 0)
            'Check for proper download
            If DownloadStatus = 0 Then
                'Delete the old files
                If MyFSO.FileExists(OldFinalName) Then
                    MyFSO.DeleteFile (OldFinalName)
                    MyFSO.CreateFolder (LocalFilePath)
                End If
                'Save temp files to replace old files
                'TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF
                If MyFSO.FileExists(OldFinalName) Then MyFSO.DeleteFile (OldFinalName)
                MyFSO.CopyFile Source:=TempFileNEW, Destination:=LocalFilePath & "\"
                'Now delete temp files
                If MyFSO.FolderExists(TempFolderOLD) Then MyFSO.DeleteFolder (TempFolderOLD)
                'Now update excel sheet to show download passed
                MsgBox "File Downloaded. Check in this path: " & LocalFilePath
                
                With Sheets("Background")
                    .Range("F" & rw) = tstamp
                    .Range("G" & rw) = "SAT"
                    .Range("C" & rw) = Format(Now, "ww", vbWednesday)
                    .Range("E" & rw) = Format(Now, "yy")
                    .Range("D" & rw) = "/"
                    'date formating
                    .Range("C" & rw).HorizontalAlignment = xlRight
                    .Range("D" & rw).HorizontalAlignment = xlGeneral
                    .Range("E" & rw).HorizontalAlignment = xlLeft
                End With
                
                'If download failed, update excel to show- old files should NOT have been deleted yet but the temp file should be deleted
            Else:
                MsgBox "Download File Process Failed"
                Sheets("Background").Range("G" & rw) = "FAIL"
                If MyFSO.FileExists(TempFolderOLD) Then
                MyFSO.DeleteFile (TempFolderOLD)
                End If
            End If
            'If the original criteria were met and the download was not necessary, say so
        Else


            MsgBox "The most up to date pub has been downloaded"
        End If
        
    Next rw



End Sub

 

[Misca]

If you know the name of the button you'll get the row from something like

ActiveSheet.Shapes("Button1").TopLeftCell.Row

If there's more than one button you'll need to adjust the macro to something like

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

 

[sassriverrat]

So every row has two buttons, one in column A and one in column B and in theory, I don't know the name of the buttons because there's hundreds of them...

So I use the second piece of code there? And how might I call the button in column B?

 

[sassriverrat]

So change of plans....if I do know the name of the buttons (new code), and they were named as "Btn_(Cell)" so a Button in A1 would be called "Btn_A1"

Then how might I reference each of them dynamically for my above code?

Thanks

 

[Misca]

The Application.Caller in the second one returns the name of the button clicked. If the column makes any difference you can check it as well:

Dim R As Long
Dim C As Integer


With ActiveSheet.Shapes(Application.Caller).TopLeftCell
    R = .Row
    C = .Column
End With

In the code above C returns the column number of the top left corner of the clicked shape.

 

[sassriverrat]

hmmm... ok so still set my rw = to this code?