VBA to open windows folder based on date, with a button on each row

[cdalgorta]

Hi,
Not sure if it is possible to add macro buttons with VBA, but here goes my question:
This is my sample table:

What I'm trying to do is have a VBA that will create a button on each cell on column K, that when clicked, it will open a windows folder based on the date on column C

For example:
K3(date 07/30/2021) would have a button that would open this folder:
Call Shell("explorer.exe" & " " & "G:\Shared drives\AP-AR\AR\Bank Deposits\2021\07-2021\Remittances\07-30 Remittances", vbNormalFocus)

and each cell would need to have its own button(or anything clickable) that would open its respective folder.

Is this possible?

Thank you very much in advance!

P.S. Ignore empty J column. I also need help with that one, but I think I should make a different thread per question.

 

[cdalgorta]

Hi,
Not sure if it is possible to add macro buttons with VBA, but here goes my question:
This is my sample table:
View attachment 70317

What I'm trying to do is have a VBA that will create a button on each cell on column K, that when clicked, it will open a windows folder based on the date on column C

For example:
K3(date 07/30/2021) would have a button that would open this folder:
Call Shell("explorer.exe" & " " & "G:\Shared drives\AP-AR\AR\Bank Deposits\2021\07-2021\Remittances\07-30 Remittances", vbNormalFocus)

and each cell would need to have its own button(or anything clickable) that would open its respective folder.

Is this possible?

Thank you very much in advance!

P.S. Ignore empty J column. I also need help with that one, but I think I should make a different thread per question.

I cannot find the edit button, so I'll post this as a reply.
I did not get an answer to this, so I reposted it on the link below. I think I read somewhere that I'm supposed to do this if I repost my question in a different place. Thank you

Reposted here

 

[NateSC]

Here are 2 subs that work together to do this. The first one builds the buttons and the second one is the routine the buttons call when pressed. I used the button "Name" property to track which row it will use to find the date so be careful if you change the "Name". The caption is controlled through .Characters.Text so you can make it say what you would like. I would recommend changing the "Activesheet" calls to be more specific to your file since it can cause issues if the focus is on the wrong workbook or sheet. The exception might be the "ButtonClicked" sub because the relevant sheet should be the active one if you clicked the button.

Un-comment the shell call you want to use at the end of the "ButtonClicked" sub. I wrote it based on your post and then a way I have used it before.

Sub MakeBtn()

Dim rng As Range
Dim btn As Object

' Based on https://www.mrexcel.com/board/threads/changing-button-caption-using-vba-assigning-macro-to-button-using-vba.869256/

    'Loop to make your buttons
    For i = 2 To 7
        Set rng = ActiveSheet.Range("K" & i)
        Set btn = ActiveSheet.Buttons.Add(1, 1, 100, 100)
        
        With btn
            'Set the button location to match the rng that was set above
            .Top = rng.Top
            .Left = rng.Left
            .Width = rng.Width
            .Height = rng.RowHeight
            'Rename the button, change the caption, change the font size, set what it runs when clicked
            .Name = i 'This number will be used in the next routine to know which row is affected
            .Characters.Text = "Test " & i
            .Characters.Font.Size = 10
            .OnAction = "ButtonClicked"
        End With
    
    Next i

End Sub

Sub ButtonClicked()

Dim myBtn As Object
Dim myDate As String
Dim myYr As String
Dim myMoYr As String
Dim myMoDay As String
Dim myPath As String

    'Need to determine which button was clicked and get the associated date
        'You could combine the next 2 steps into one if you don't need to do anything
        ' to the button after it is pressed.  Setting it to a variable
        ' makes it easier to interact with for multiple operations.
        'Optional single line replacement:
        ' myDate = ActiveSheet.Range("C" & ActiveSheet.Shapes(Application.Caller).Name)
    Set myBtn = ActiveSheet.Shapes(Application.Caller)
    myDate = ActiveSheet.Range("C" & myBtn.Name)
    
    'Separate the pieces
    myYr = Format(myDate, "yyyy")
    myMoYr = Format(myDate, "mm-yyyy")
    myMoDay = Format(myDate, "mm-dd")
    
    myPath = "G:\Shared drives\AP-AR\AR\Bank Deposits\" & myYr & _
        "\" & myMoYr & "\Remittances\" & myMoDay & " Remittances"
    
    'The shell call you had listed
    'Call Shell("explorer.exe" & " " & myPath, vbNormalFocus)
    
    'A shell I used in another macro
    'Shell "explorer.exe """ & myPath & """", vbNormalFocus
    
End Sub

I hope this helps.
-N

 

[cdalgorta]

Here are 2 subs that work together to do this. The first one builds the buttons and the second one is the routine the buttons call when pressed. I used the button "Name" property to track which row it will use to find the date so be careful if you change the "Name". The caption is controlled through .Characters.Text so you can make it say what you would like. I would recommend changing the "Activesheet" calls to be more specific to your file since it can cause issues if the focus is on the wrong workbook or sheet. The exception might be the "ButtonClicked" sub because the relevant sheet should be the active one if you clicked the button.

Un-comment the shell call you want to use at the end of the "ButtonClicked" sub. I wrote it based on your post and then a way I have used it before.

Sub MakeBtn()

Dim rng As Range
Dim btn As Object

' Based on https://www.mrexcel.com/board/threads/changing-button-caption-using-vba-assigning-macro-to-button-using-vba.869256/

    'Loop to make your buttons
    For i = 2 To 7
        Set rng = ActiveSheet.Range("K" & i)
        Set btn = ActiveSheet.Buttons.Add(1, 1, 100, 100)
       
        With btn
            'Set the button location to match the rng that was set above
            .Top = rng.Top
            .Left = rng.Left
            .Width = rng.Width
            .Height = rng.RowHeight
            'Rename the button, change the caption, change the font size, set what it runs when clicked
            .Name = i 'This number will be used in the next routine to know which row is affected
            .Characters.Text = "Test " & i
            .Characters.Font.Size = 10
            .OnAction = "ButtonClicked"
        End With
   
    Next i

End Sub

Sub ButtonClicked()

Dim myBtn As Object
Dim myDate As String
Dim myYr As String
Dim myMoYr As String
Dim myMoDay As String
Dim myPath As String

    'Need to determine which button was clicked and get the associated date
        'You could combine the next 2 steps into one if you don't need to do anything
        ' to the button after it is pressed.  Setting it to a variable
        ' makes it easier to interact with for multiple operations.
        'Optional single line replacement:
        ' myDate = ActiveSheet.Range("C" & ActiveSheet.Shapes(Application.Caller).Name)
    Set myBtn = ActiveSheet.Shapes(Application.Caller)
    myDate = ActiveSheet.Range("C" & myBtn.Name)
   
    'Separate the pieces
    myYr = Format(myDate, "yyyy")
    myMoYr = Format(myDate, "mm-yyyy")
    myMoDay = Format(myDate, "mm-dd")
   
    myPath = "G:\Shared drives\AP-AR\AR\Bank Deposits\" & myYr & _
        "\" & myMoYr & "\Remittances\" & myMoDay & " Remittances"
   
    'The shell call you had listed
    'Call Shell("explorer.exe" & " " & myPath, vbNormalFocus)
   
    'A shell I used in another macro
    'Shell "explorer.exe """ & myPath & """", vbNormalFocus
   
End Sub

I hope this helps.
-N

Hi Nate,

Thank you so much!

Just a few questions:

For the first sub, how would I change it to make buttons beyond rows 2 to 7? Basically, to drag down as far as there is a value on the corresponding column A?

As for the 2nd sub.

Could you elaborate on this?:
"You could combine the next 2 steps into one if you don't need to do anything to the button after it is pressed. Setting it to a variable makes it easier to interact with for multiple operations."
I'm not sure I understand. You mean you could run 2 different macros with the same button depending on how you interact with it? Like a right click instead of a left click? hahah. Sorry, complete newbie here.

P.S. Again thank you so much!

 

[NateSC]

Change the top of the first Macro to:

Sub MakeBtn()

Dim rng As Range
Dim btn As Object
Dim myNumRows As Integer

' Based on https://www.mrexcel.com/board/threads/changing-button-caption-using-vba-assigning-macro-to-button-using-vba.869256/

    myNumRows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    'Loop to make your buttons
    For i = 2 To myNumRows

If you write many macros, you will use this (ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row) often. It is equivalent to selecting the bottom cell in the first column (row 1048576) and then hitting Ctrl-UpArrow on your keyboard. If you need it for another column, just change the 1 to the column you need. You can also use a variant of this to determine how many columns have data.

On the 2nd sub:
The extra operations would be if you want to change some of the button properties when clicked. I would usually leave it as the 2 line version because it really isn't a significant speed penalty and it makes it easier to debug. The single line of code is just more compact if you want to make it as few lines as possible or want to keep your variables to a minimum depending on your overall macro size.

P.S This was a fun one to figure out. I have plans to use it in another project.

-N

 

[cdalgorta]

Change the top of the first Macro to:

Sub MakeBtn()

Dim rng As Range
Dim btn As Object
Dim myNumRows As Integer

' Based on https://www.mrexcel.com/board/threads/changing-button-caption-using-vba-assigning-macro-to-button-using-vba.869256/

    myNumRows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    'Loop to make your buttons
    For i = 2 To myNumRows

If you write many macros, you will use this (ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row) often. It is equivalent to selecting the bottom cell in the first column (row 1048576) and then hitting Ctrl-UpArrow on your keyboard. If you need it for another column, just change the 1 to the column you need. You can also use a variant of this to determine how many columns have data.

On the 2nd sub:
The extra operations would be if you want to change some of the button properties when clicked. I would usually leave it as the 2 line version because it really isn't a significant speed penalty and it makes it easier to debug. The single line of code is just more compact if you want to make it as few lines as possible or want to keep your variables to a minimum depending on your overall macro size.

P.S This was a fun one to figure out. I have plans to use it in another project.

-N

Thank you so much once again Nate!