Скачать или смотреть How to Set a Fixed Size for Comment Boxes in Excel VBA

Learn how to easily set a fixed size for comment boxes in Excel using VBA, ensuring your comments are organized and visually appealing.
---
This video is based on the question https://stackoverflow.com/q/75572359/ asked by the user 'Steve Ackerman' ( https://stackoverflow.com/u/15501023/ ) and on the answer https://stackoverflow.com/a/75572825/ provided by the user 'T.M.' ( https://stackoverflow.com/u/6460297/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA Comment Format

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

If you're diving into the world of Excel and VBA, you may encounter challenges when trying to manipulate comment boxes. Whether you are creating presentations or just need to clarify notes for your spreadsheet, having your comments formatted properly can be crucial. In this guide, we explore a common issue faced by many new developers: how to set a fixed size for all comment boxes in an Excel worksheet using VBA.

This is particularly useful if you want to maintain a consistent visual format for comments throughout your spreadsheet.

Understanding the Problem

The question that arises is how to automate the resizing of comment boxes across an entire sheet. While resizing a single comment box is straightforward, doing so for a sheet with multiple comments can be a bit more complex.

In our specific scenario, the user has attempted to adjust the size of comment boxes but encountered issues with the code provided. Therefore, we will present a breakdown of how to achieve this through simple, clear steps.

The Solution Explained

To automatically set a fixed size for comment boxes in Excel using VBA, we will use a two-part approach. The first part is the main subroutine that loops through all comments, while the second part handles the resizing of individual comments.

Step 1: Loop Through Comments

The first step is to create a procedure that iterates through every comment within the desired worksheet. Here's how to do this:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of Step 1:

Turn off Screen Updating: This optimizes performance by preventing the screen from updating while the macro runs.

Set the Worksheet: Specify which worksheet you're working with (replace Tabelle4 with your actual worksheet).

Loop through Comments: Use a For Each loop to iterate through each cell that contains comments (found using SpecialCells(xlCellTypeComments)).

Call the Resizing Function: For each comment, call the adaptCommentSize function that will be defined next.

Step 2: Resize Individual Comments

The next step involves defining the adaptCommentSize subroutine that resizes each individual comment based on specified width and height.

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of Step 2:

Comment Shape Reference: The program accesses the shape of the comment contained in the range.

Text Frame Options: AutoSize is set to True allowing the comment to resize automatically based on content. Margins are also reset to zero to ensure the comment box is as snug as possible around the text.

Width and Height Check: Conditions are checked to enforce maximum dimensions for the comment box, here set to 250 pixels but can be adjusted according to your needs.

Conclusion

By executing the above two subroutines, you will successfully resize all comment boxes in your specified worksheet to a consistent size. This approach greatly enhances the usability and appearance of comments in your spreadsheets.

With practice, you will become adept at manipulating Excel through VBA, allowing for streamlined experiences in spreadsheets to suit various needs.

Feel free to reach out if you have further questions or need clarification regarding the code. Happy coding!