We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x
Private

MS Office VBA: circles shapes count

Luis Diaz
Luis Diaz asked
on
High Priority
94 Views
Last Modified: 2020-04-26
Hello experts,

I am facing the following requiring problem.
How to add count (same process as proposed by greenshot):

20200423_225028.png
I am aware that I can insert this manually but I don't understand how can be possible that MS Office applications, which are paid application, don't propose this feature and Greenshot which is a free application propose this feature which is very useful.

Due to this I am looking for a VBA approach with the following sequence:
-I execute the procedure and it will insert sequential circle number based on number of click. Once I finish, I just heat escape. Example: I click on different locations 7 times and 7 circle shapes with be inserted and each of it with the number (1) (1)
(3) (4) (5) (6) (7)

-Cross MS office app procedure will be the best here however if this is not possible I will manage this with PowerPoint:
-If click sequential number is complex we can adopt the following approach,
Msgbox how many circles do you want?
and insert them. I will move then manually.

I attached dummy file with the format of circle required.

Thank you for your help.
InsertCount.pptx
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
In what application do you want this ? ppt ? Excel ?
Gowflow
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
You need something like this in PowerPoint:
Sub Insert_Circles()
Dim ActiveSlide As Slide
Dim OneCircle As Shape
Dim Num As Integer
Dim C As Integer

Num = InputBox("How many circles you want?", "Create circles")
Set ActiveSlide = ActiveWindow.View.Slide
C = 1

While (C <= Num)
    Set OneCircle = ActiveSlide.Shapes.AddShape(msoShapeOval, 20 + ((C Mod 10) + 1) * 20, 20 + (C \ 10) * 20, 20, 20)
    OneCircle.Fill.Solid
    OneCircle.Fill.ForeColor.RGB = vbRed
    'OneCircle.Fill.BackColor.RGB = vbRed
    OneCircle.TextFrame.TextRange.Text = C
    OneCircle.TextFrame.TextRange.Font.Color = vbBlack
    C = C + 1
Wend
End Sub
Luis DiazIT consultant

Author

Commented:
@Glowflow:

Top priority: PowerPoint & Word
Nice to have: Excel
Luis DiazIT consultant

Author

Commented:
@Máté Farkas: very nice procedure proposed. to adapt the same mechanism to Word and Excel, how can I proceed?
gowflowPartner
CERTIFIED EXPERT

Commented:
Hi Luis
Here is an Excel solution fully adapted to work on any sheet in this workbook. Her is the setting .

Once the application is launched, it automatically create a menu in the Add-in Menu you will find there My Circles if you click on it will give you 2 sub menu
Cl;ear Circles: When depressed, it will clear all the circles in the active sheet.
Draw Circles: When depressed the first time it will draw a sequential numbering red circle each time you click anywhere on the sheet. Once you finish with drawing circles and want to do other things with the sheet and not have circles being drawn you simply click again on Draw Circles and it will de-activate drawing circles.

Please feel free to try it and let me know your comments.

For sure it need tweaking to adapt to powerpoint and word.
Gowflow
Red-Circles-V01.xlsm
Luis DiazIT consultant

Author

Commented:
Hi GowFlow,

Thank you for this proposal. I made a testing video. I am having some issues with the proposal:

1-Unable to stop the flow sequence with double click as recommended in your comment:
Once you finish with drawing circles and want to do other things with the sheet and not have circles being drawn you simply click again on Draw Circles and it will de-activate drawing circles.

2-Wrong sequential number in the second relaunch testing.

Please find attached testing video.

Regards,
Luis.
20200424114354_20200424_113834-video.zip
gowflowPartner
CERTIFIED EXPERT

Commented:
NOT doublcick

Click on DRAW Circle menu when u want to stop. This is how designed right now

We can change that
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Luis

Your giving me some challenges !!!!

This version will (beside the menu) get deactivated like not draw circles as soon as you hit ESCAPE button and if you want to resume drawing circles it will ask you if you want to continue with the same sequence or start a new sequence.

Enjoy
Gowflow
Red-Circles-V02.xlsm
Luis DiazIT consultant

Author

Commented:
Hi Gowflow,

I am having some issues with VO2.xlsm:

1-Unable to stop the sequence with Esc keyboard. The only way to stop is to relaunching again the first procedure: CirclesOnOff
2-When I relaunch the sequence a second time and I select the option Yes, I don't have the right sequence number.

 If this is complex to implement we can do the easiest approach which is as proposed here: #a43072713

Instead of clicking sequence: ask for number of cirles in inputbox.

Regards,
Luis.
gowflowPartner
CERTIFIED EXPERT

Commented:
What version of Excel do you use ?
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Nothing is difficult but you got to be patient. You don't give me a chance to troubleshoot. Here it is working perfectly fine. I never upload a solution unless fully tested. Must be probably we have different excel versions please answer my question then will take it from there.
What excel version
Gowflow
Luis DiazIT consultant

Author

Commented:
Excel 2019 and Excel 2016.
Luis DiazIT consultant

Author

Commented:
Take your time, no rush.
gowflowPartner
CERTIFIED EXPERT

Commented:
WOW !! speedy gonzales
Am at 2010

Let me see maybe incompatibility with escape sequence one mom
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Your absolutely right the version I posted had a bug coz I found the Escape last minute and did a mess. Shame on ME !!!

PLease try this one. Use Escape and menu and toggle
GOwflow
Red-Circles-V03.xlsm
Luis DiazIT consultant

Author

Commented:
Thank you for this. I am out of Home. I will test it as soon as I can!
gowflowPartner
CERTIFIED EXPERT

Commented:
ok be my guest. Please feel free to let me know anything that is not 100% to your like !!
Gowflow
Luis DiazIT consultant

Author

Commented:
Hi Gowflow,

I tested the sequence and now the whole process works on activecell Excellent news!
I am having a little issue and I think you are not going to be happy with this. However I am going to take the risk to let you know.
My use case is to put shapes or images in Excel Workbooks. I think that the sequence is applied on activecell and not on shapes or images.
I attached a dummy file. I did the following test: launch the procedure and try to click on shape to insert the a circle without success.

If you are going to tackle this, please ba aware that I can have images or shapes and I expect to be able to insert circles when I click on shapes, images or cells.

Thank you for your help.

Regards,
Luis.
Red-Circles-V04.xlsm
gowflowPartner
CERTIFIED EXPERT

Commented:
I love challenges !!

Try this and tell me if it pleases you.


PS
Warning:: Please at this stage try the workbook as is and do not attempt to try move the code onto an other workbook as if not done properly you may get very erratic behavior.

Also an other warning, now designed to get the shape to react to macro as already in the workbook upon opening. Not tested yet (but sure can be done) to react to macro if created now when workbook is opened.
Gowflow
Red-Circles-V05.xlsm
gowflowPartner
CERTIFIED EXPERT

Commented:
I guess this version should do it.

Fixes:
1)  Can generate Circled numbers on existing shapes and newly created.
2) Numbering is fixed after 10 was only showing the first digit.

Please let me know.
Gowflow
Red-Circles-V06.xlsm
Luis DiazIT consultant

Author

Commented:
Tested V06 it covers most of the case but I am having 2 issues:

1.Unable to stop the sequence I try multiple times with ESC when I say multiple times it is real :-)
2.Shapes allows to insert one circle on shapes, if I want to insert more than one it doesn't work and it increase the sequence number of circle already inserted on shape. Example circle which contains number 41

20200425_015202.png
I tested with Excel 2019.

If this can not be reviewed I will prefer to have the simple approach: InputBox: "Report the number of circle to insert? and insert accordingly.

Thank you for your help.
Red-Circles-V06.xlsm
gowflowPartner
CERTIFIED EXPERT

Commented:
To stop the sequence if Escape does not work in your version please from the addin menu select Draw Circles then it will stop creating circles. When you request Draw Circles again it will resume and so on each time you request this menu it toggle between Stop and resume.

For the shape that you clicked twice there are 2 circles that are one on top of the other just right click on the circle you see and drag it away you will see the other circle underneath.

Let me know.

EDITED
I read it seems in some cases there are problems with the Escape they suggested Shit and Escape this version uses the Shift and while holding shift press Escape it will start or top.

Gowflow
Red-Circles-V07.xlsm
Luis DiazIT consultant

Author

Commented:
Thank you for this.

I tested V07 and I am having the same two issues. Unable to escape and unable to insert more than one circle on shapes. I think that the best approach here is the simplest. Ask for how many circles to be inserted and place them after the insertion.

If we can have this on Word, Excel and PowerPoint it would be great:
Proposal for PowerPoint at:
#a43072713

I think that there is no sense to continue to find a solution for something that we can manage with a clever and simple approach.
gowflowPartner
CERTIFIED EXPERT

Commented:
Have you tried to stop circles by the menu ?
Draw Circles ??

This version will ask you how many circles you want and surprised you only get 1 circle per drawing if you click first time on a drawing you get 1 you click again do u get 2 or nothing ??

As far as word is concerned sorry I cannot help not into word
Gowflow
Red-Circles-V08.xlsm
Luis DiazIT consultant

Author

Commented:
Hi Gowflow, I tried the stop through both options. This is very complicated to manage.  :-) I just want, MsgBox: Enter the number of circle to inser:. I enter 4 and 4 circles will be inserted distributed horizontally, no click to do anymore.
gowflowPartner
CERTIFIED EXPERT

Commented:
did u try version v08?
Luis DiazIT consultant

Author

Commented:
I tested V08. And it works for the first sequence. I enter 5 and I click five times on different locations (Ok with me).
However, (big issue) I relaunch it to have initiate a second sequence and I don't get the InputBox.

I continue saying that the best approach is not having the clicks.
gowflowPartner
CERTIFIED EXPERT

Commented:
ok ok I got u.
Just give me sometime coz I am finalizing it to make it as easy as possible and as clean as possible. Pls be patient.
Tks

Gowflow
Luis DiazIT consultant

Author

Commented:
Ok, thank you for your message.
Partner
CERTIFIED EXPERT
Commented:
ok here it is.

Very simple. You open the workbook and display the Add-Ins menu You will see 2 buttons
Clear Circles
Draw Circles

1) Click any where on the worksheet its a normal Excel worksheet nothing happens.
2) click on Draw Circles you are prompt to enter a number of circles put the number that you want press enter you will see them displayed. you can drag each one where you want to put it.
3) The worksheet is back normal
4) You want to draw circles again click on draw circles and put the number Etc..
5) YOu want to clean all circles simply press on Clear Circles.


FOr extra precision simply click where you want the series of circles to be layed out BEFORE you click on Draw Circles and it will display them there.
I hope this one will do it. Sorry for having taken so much time wanted to do it a bit fancy but guess the ESCAPE messed it up and the multiple circles on a shape also which are behaviour in newer version all working fine here. But anyway no problem.

Please let me know your comments.
Gowflow
Red-Circles-V09.xlsm

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Luis DiazIT consultant

Author

Commented:
Hello,
I don't see the button. Buttons brings complexity here. The need is clear :-) Just a simple procedure to insert shapes based on number reported in InputBox. A single procedure without having to click or Clear clicks.
gowflowPartner
CERTIFIED EXPERT

Commented:
oh my god !!! 40 years experience in the most complex software were never a challenge to me but the behavior ur experiencing i totally don’t understand !!!
what kind of excel u hv there that does not respond to escape where u dint see sub menus that are inserted in the ribbon Add-ins menu you should see there 1 button say clear circles and the other draw circles
you dont see them??

how are we supposed to activate a macro across any sheet in a workbook without a start??

i am really lost
gowflowPartner
CERTIFIED EXPERT

Commented:
just iut of curiosity. you are trying the workook i posted and giving your comments?? or integrated same in your workbook and noting the behavor??

just di me a favor just run the file i posted and give me a snapshot of the add-ins menu please
gowflow
Luis DiazIT consultant

Author

Commented:
Hi GowFlow,

Sorry for my last comment now I understand, I was getting lost with the previous versions and click issues.

20200426_120536.png
V09 covers all my needs!

Excellent version I am very happy with it. Now I will see if this can be applied for Word.

Just last question. Why I am not able to launch the procedure Draw Circle from Macro. I don't see it.
20200426_120919.png
gowflowPartner
CERTIFIED EXPERT
Commented:
This is a simple version
No menus
No Escape sequence
No Drawing on Shapes
Everytime Draw Circles is invoked it will start from 1
Clear Circles will elimitate all red circles on the sheet.

Try it and let me know
Gowflow
Red-Circles-Simple-V01.xlsm
gowflowPartner
CERTIFIED EXPERT

Commented:
Sorry for my last comment now I understand, I was getting lost with the previous versions and click issues.

At one point I thought I was loosing it !!! Ufff u saved my morale :):):)

GLad o9 did it as really I put everything there but previous versions with Escape is really good also anyway.


Re Word I was working on it in parallel but to be honest with you Word is a PAIN in the But !!! give me sometime and I will comeup with something as am the stubborn type !!!

PLease post here a link of the Word question and I will put all effort to get it.
Just keep the question very brief continuation of and put the link of this question and say would like to have it in Word !!!

Gowflow
Luis DiazIT consultant

Author

Commented:
Ok now I understand it is related to the procedure CircleONOFF.
Luis DiazIT consultant

Author

Commented:
Ok, our comments cross:

Red-Circles-Simple-V01.xlsm covers the full need! Exactly what I was looking!

Thank you for your help!
Luis DiazIT consultant

Author

Commented:
Hi GowFlow,

Following your comment: #a43073869

I created MS Word question.

Reference link: Question 29180113

Regards,
Luis.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.