HYPTUSS - Reset button sizes and positions on main sheet

[Itsallaguess]

Following another recent thread regarding what can be a fairly regular issue with HYPTUSS buttons re-sizing and losing their original positions, I thought it would be worth investigating a simpler and longer term solution to the problem that can easily be incorporated into the tool and then solved with a simple process if the issue ever occurs.

The instructions below works with the main 'High Yield Portfolio' sheet buttons on the current 11.81 Excel version, although I suspect that it'll also work with with any of the previous Excel versions that has 16 buttons on the main sheet -

1. Open your HYPTUSS file and select the main 'High Yield Portfolio' sheet

2. Enable the 'Developer' tab if it's not already available (see https://www.excel-easy.com/examples/developer-tab.html for simple instructions on how to do this)

3. On the 'Developer' tab, click on the 'Macros' button towards the left of the 'Developer' ribbon

4. In the 'Macro' window that opens up, make sure to select 'This Workbook' on the drop-down menu marked 'Macros in' -



5. At the top of the 'Macro' window, in the 'Macro name' area, clear the field and then type 'Reset_Buttons' to give the VBA macro a memorable name for the future -



6. After typing the above macro name, click on the 'Create' button that should be active towards the right of the 'Macro' window -



7. After clicking the above 'Create' button, and after entering the protective VBA password of 'pleaseletmein', the VBA Editor window should open with the new 'Reset_Buttons' macro now available to modify, so click on the 'SELECT ALL' option at the top of the following 'CODE' section, and then right-click the selected code and COPY it to your bookmark -

Code: Select all


With Sheets("High Yield Portfolio").Shapes("CommandButton1")
    .Left = 245.25
    .Top = 6.75
    .Height = 30.75
    .Width = 69
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton2")
    .Left = 245.25
    .Top = 47.25
    .Height = 30.75
    .Width = 80.25
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton3")
    .Left = 427.5
    .Top = 9
    .Height = 30.75
    .Width = 48.75
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton4")
    .Left = 427.5
    .Top = 45.75
    .Height = 30.75
    .Width = 56.25
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton5")
    .Left = 507.75
    .Top = 8.25
    .Height = 30.75
    .Width = 54.75
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton6")
    .Left = 946.5
    .Top = 2.25
    .Height = 19.5
    .Width = 80.25
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton7")
    .Left = 759.75
    .Top = 7.5
    .Height = 33.75
    .Width = 76.5
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton8")
    .Left = 761.25
    .Top = 48.75
    .Height = 18.75
    .Width = 75
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton9")
    .Left = 684
    .Top = 6.75
    .Height = 30.75
    .Width = 39.75
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton10")
    .Left = 347.25
    .Top = 7.5
    .Height = 30.75
    .Width = 53.25
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton11")
    .Left = 945.75
    .Top = 30
    .Height = 32.25
    .Width = 81
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton12")
    .Left = 347.25
    .Top = 48
    .Height = 30
    .Width = 53.25
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton13")
    .Left = 864.75
    .Top = 5.25
    .Height = 18.75
    .Width = 75
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton14")
    .Left = 866.25
    .Top = 30.75
    .Height = 32.25
    .Width = 74.25
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton15")
    .Left = 508.5
    .Top = 45.75
    .Height = 30.75
    .Width = 74.25
End With


With Sheets("High Yield Portfolio").Shapes("CommandButton16")
    .Left = 684.75
    .Top = 48.75
    .Height = 25.5
    .Width = 54.75
End With




8. When the above code has been copied to your bookmark, PASTE it into the VBA Editor window between the two existing macro lines -



9. Once the above code has been pasted into the above new 'Reset_ Buttons' macro, you can close the VBA Editor using the red cross towards the top-right of the window, and you should then be returned to your main HYPTUSS tool.

10. To run the 'Reset_Buttons' macro, simply select the 'Macros' button again on the 'Developer' tab, and you should now see the 'Reset_Buttons' macro listed. Left-click to select the 'Reset_Buttons' macro, and then click the 'Run' button towards the right of the 'Macro' window -



11. All buttons on the main 'High Yield Portfolio' sheet should now be reset to their original size and positions...


PLEASE NOTE - as with all of these slightly off-piste processes, I would always recommend that anyone looking to implement them on their own HYPTUSS files should initially work through the instructions ON A COPY OF THEIR WORKING HYPTUSS FILE, to help avoid any issues in case something might go wrong.

Once the above 'Reset_Macros' process has been completed, the underlying macro is then available at any point to quickly 'Run' from the 'Developer' tab from STEP 10 onwards, so for anyone who finds they have regular problems with this button issue on their main HYPTUSS portfolio sheet, it should help to quickly reset their buttons whenever it's required.

Cheers,

Itsallaguess