Buttons created with VBA are not fitting/aligning with the cell they should be on

[cdalgorta]

Hi.
This was working perfectly until today, this morning it suddenly started to not work correctly. I'm not even sure if I changed anything, but I've gone back to an older backup I have and the macro looks the same, so maybe an Excel update ruined the macro? I've tried the macro on different computers(co-workers') and it is doing the same weird thing on theirs as well. Please let me know if you see what I can do to fix my macro if it was an update that messed it up?

All the macro pretty much does is "create buttons" on the J column that run another macro based on the row the button is on(but that 2nd macro works fine). For some reason, the Buttons are not "fitting in the cell" like they used to when they are created. Please see image 1 to see how it used to look vs image 2 how it's looking now.
Please help me fix the buttons so they fit in their cell and are aligned with their respective row. Thank you very much in advance!

Image 1: How it used to look. Fitting in the cell

Image 2: How it looks now. Buttons are not aligned with the rows.

Here is a sample workbook with the macro on it if you need it as well:

Gofile - Free file sharing and storage platform

Gofile is a free file sharing and storage platform. You can store and share your content of any type without any limit.

gofile.io

Sub Test_J_Buttons()

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

 ActiveSheet.Buttons.Delete


'''''=========Make Buttons to create new sheets on J column==========

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

' Based on Changing Button Caption using VBA & Assigning Macro to button using VBA

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

'Loop to make your buttons
For i = 2 To myNumRows
Set rng = ActiveSheet.Range("J" & 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 = "Sheet " & i
.Characters.Font.Size = 10
.OnAction = "New_Sheet" 'The macro the button would run
End With

Next i
    
End Sub

 

[cmowla]

Replace:

Set rng = ActiveSheet.Range("J" & i)
Set btn = ActiveSheet.Buttons.Add(1, 1, 100, 100)

With

With ActiveSheet.Range("J" & i)
    Set btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
End With

And delete these lines:

'Set the button location to match the rng that was set above
.Top = rng.Top
.Left = rng.Left
.Width = rng.Width
.Height = rng.RowHeight

And see if that works.

 

[cdalgorta]

Replace:

Set rng = ActiveSheet.Range("J" & i)
Set btn = ActiveSheet.Buttons.Add(1, 1, 100, 100)

With

With ActiveSheet.Range("J" & i)
    Set btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
End With

And delete these lines:

'Set the button location to match the rng that was set above
.Top = rng.Top
.Left = rng.Left
.Width = rng.Width
.Height = rng.RowHeight

And see if that works.

Did not work. Same thing

Sub Test_J_Buttons()

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

 ActiveSheet.Buttons.Delete


'''''=========Make Buttons to create new sheets on J column==========

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

' Based on Changing Button Caption using VBA & Assigning Macro to button using VBA

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

'Loop to make your buttons
For i = 2 To myNumRows
With ActiveSheet.Range("J" & i)
    Set btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
End With

With btn

'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 = "Sheet " & i
.Characters.Font.Size = 10
.OnAction = "New_Sheet" 'The macro the button would run
End With

Next i
End Sub

 

[cmowla]

Okay, maybe the buttons that were already there are still there, that you need to delete all buttons on the sheet first?

ActiveSheet.Buttons.Delete
'Loop to make your buttons
For i = 2 To myNumRows

 

[cdalgorta]

Okay, maybe the buttons that were already there are still there, that you need to delete all buttons on the sheet first?

ActiveSheet.Buttons.Delete
'Loop to make your buttons
For i = 2 To myNumRows

That's at the top of the macro. below of the sub name
Also, I copied/pasted A:I on a new workbook and it was the same.

 

[cmowla]

Okay, I don't know. You will have to wait for someone else to try and help. Sorry about that.

 

[cdalgorta]

Okay, I don't know. You will have to wait for someone else to try and help. Sorry about that.

No Worries! Thanks for trying.

 

[cdalgorta]

This was the issue the whole time. Putting it here in case someone has the same problem I did hahah. The easiest solution ever

 

[cmowla]

I would have never guessed! Good thing you found out it was that!