Start Free Trial

asked on

MS Office VBA: circles shapes count

Hello experts,

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

User generated image

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

In what application do you want this ? ppt ? Excel ?
Gowflow

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

Open in new window

ASKER

@Glowflow:

Top priority: PowerPoint & Word
Nice to have: Excel

ASKER

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

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

ASKER

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

NOT doublcick

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

We can change that
gowflow

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

ASKER

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.

What version of Excel do you use ?
Gowflow

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

ASKER

Excel 2019 and Excel 2016.

ASKER

Take your time, no rush.

WOW !! speedy gonzales
Am at 2010

Let me see maybe incompatibility with escape sequence one mom
Gowflow

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

ASKER

Thank you for this. I am out of Home. I will test it as soon as I can!

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

ASKER

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

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

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

ASKER

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

User generated image

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

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

ASKER

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.

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

ASKER

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.

did u try version v08?

ASKER

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.

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

ASKER

Ok, thank you for your message.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

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

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

ASKER

Hi GowFlow,

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

User generated image

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.

User generated image

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

ASKER

Ok now I understand it is related to the procedure CircleONOFF.

ASKER

Ok, our comments cross:

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

Thank you for your help!

ASKER

Hi GowFlow,

Following your comment: #a43073869

I created MS Word question.

Reference link: Question 29180113

Regards,
Luis.