I need to be able to add the next number to a text box which is a shape. When I run the macro the number 1 starts in the text, then the next time I run it I want the number 2, the next time number 3, number 4, 5, 6 etc.... I have the macro that can produce the first one but can't figure out how to loop it to add the next number.
Thanks
Please Login or Register to view this content.
Hi jtemp57,
Try the following (tested and working):
LewisPlease Login or Register to view this content.
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Or, maybe a count down ...
Please Login or Register to view this content.
Regards, TMS
This is on the right track; however, I don't want all of them to appear at once. If I assign it to a button, then every time I press the button I would want the next number to appear instead of all at once. I guess I would need something like i+1....???
You need to manage incrementing the counter somehow, but ...
Please Login or Register to view this content.
Regards, TMS
The following sequence of events should help you out (tested and working in Excel 2003). This should work for several shapes existing at the same time, without changing any code.
1. Create a Macro that will increment the number in the Shape. The name can be any legal Macro name.
2. Assign the Macro to Exisiting ShapesPlease Login or Register to view this content.
Right Click the Shape > Assign Macro > MyOvalIncrement >OK
3. Modify your creation Macro slightly, to assign the macro when you create the Shape. If the macro doesn't exist when you create the shape, but exists later, you may have to close the workbook, for the shape to recognize the Macro name. It is easier to have the macro exist when you create the Shape.
Modifications to your code are in red:
LewisPlease Login or Register to view this content.
Last edited by LJMetzger; 08-05-2014 at 09:46 AM.
Ok, this one counts to the number I need from my range, but it doesn't create a new shape for each number, it just replaces the number in the shape?
Please Login or Register to view this content.
That's right. It doesn't create a new shape each time. You get it to do that by moving theinside the loop. However, as it stands, each shape would be in the same place as all the others. If you want to create it elsewhere, you'd need to adjust the location parameters, perhaps based on the counter.Please Login or Register to view this content.
Regards, TMS
Yes!! That worked great!! Thanks so much
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks