in some cases, a method of using shapes is more effectively in Excel.
The shapes can be used to build custom plans, maps and layouts.
Shapes are perfect for the users to add or change the location, size, color and the other properties.
By using sticky buttons, we can interact with the selected shapes and collect additional data. Each shape has its unique name so can be used a part of huge tables and calculations.
For instance, you can design your office allocation to find the most efficient layout or you can use dynamic design of the shelves of your bookstore
to use this method;
create shape or a group of shapes,
Lock them to prevent size an location changes,
Continuously watch that any shape is selected
If any, relocate the buttons near the shape and set them visible
run the vba code when its clicked
Get the information with additional user forms and dialogs
1. Insert a rectangular shape, an icon and textbox for the button
2. Resize the and select all
3. open right click menü and hit the “format shape”.
4. change the visual properties as you like. Don’t forget to add shadow.
5. Under properties, Click the shapes are not move or resize when the cells are moving.
6. group all the elements and repeat step 5
7. create a table to store data when the button is clicked
8. Hit alt+F11 and insert a vba module
9. right click the button and assign btnexport_click macro to the button
10. Run “start_timer” sub
The button appears any shape you click, even its newly inserted.
When clicked the data if the shapes will be written to the table/
.code explaations:
Since there is no event when the shapes selected: we need a timer
"Start timer" runs the defined subroutiine in every 2 seconds
"Stop timer" stops the timer
When the timer has started, watchshape runs in every two seconds
Watchshape, checks selected shape and returns the shape object
Download :
Comments