take input from userform and draw circles in userform

[praveen_ce]

hi ppl,

i am preparing a macro which takes input X=10

and draws 10 equidistant circles of same size in userform


thnks in advance

 

[Hui]

Praveen

When you say in the UserForm do you mean on the Excel screen or on a User Form in front of the Excel Screen?

When you say equidistant do you mean touching at an equal distance?

 

[praveen_ce]

Hi Hui,

I was talking abt userform prepared in VB sheet

equidistant like this " O - O "


Hui one more doubt

=MATCH(13,{27,35,39,13})

its giving me an error #N/A


thnks hui

 

[Hui]

You haven't specified the Match type

=MATCH(13,{27,35,39,13},)

a single , is enough to force it to default to 0

 

[Hui]

Praveen


I don't think the VBA editor has tools for drawing objects on Userforms at run Time.

 

[SirJB7]

Hi, praveen_ce!


I disagree with Hui, you can do that with the Shapes.AddShape object.

Maybe you want to give a look at this link: http://www.andypope.info/vba/userformdraw.htm


Additionally check this, it's done on worksheets not on user forms, but the technique might be applied too:

http://chandoo.org/forums/topic/anyone-attempted-to-create-this-using-excel


Regards!


EDITED

 

[Hui]

I'm sticking with my last answer!


Apart from simple straight lines (using the edges of Controls with width 1 or inserting pictures with objects on them, Excel 2007/10 has limited scope to add circles etc to userforms


Andy Popes links were for Excel 97-03 and don't work with 2007/10


ps: I do hope someone can prove me wrong

 

[SirJB7]

Hi, Hui!


In Andy Pope's previous link there are two sub-links: one to "Download the Self contained demonstration" and other to "Download the XLA demonstration" that lead to seldom zips containing the first one a 2003 .xls file and the second one a 2003 .xls file and an .xla file.


I tried both, as .xls 2003 format and as .xlsm 2010 format, and both worked smoothly, neither even an error nor a warning, just the message for allowing macros.


Looking inside Andy Pope's file, which is of 2004, the main core has been written by Stephen Bullen... in 1998! And still on 14 years and 4 versions after. Hats off for both.


Regards!

 

[praveen_ce]

thnks hui and SirJB7 ,

i thought i will excel itself now

i avoided circles by using letter "O"

i am doing macro like this


if no rows = 4

no of tubes per row =4

no of inlets =4


then it should show


O O O O



O O O O

/ / / /

O O O O



O O O O


my code is

,


Function lab_O(noofrows As Integer)

Dim a As Integer


For a = 1 To noofrows Step 1


If a Mod 2 <> 0 Then


ActiveCell.Offset(1, 1).Range("A1").Select

ActiveCell.FormulaR1C1 = "'"

ActiveCell.Offset(1, 1).Range("A1").Select

ActiveCell.FormulaR1C1 = "O"

ActiveCell.Offset(0, -2).Range("A1").Select


Else

ActiveCell.Offset(1, 1).Range("A1").Select

ActiveCell.FormulaR1C1 = "'/"

ActiveCell.Offset(1, -1).Range("A1").Select

ActiveCell.FormulaR1C1 = "O"


End If


Next a


End Function


Private Sub row()


Dim noofrows As Integer


noofrows = Sheet9.Range("b3").Value

tubesperrow = Sheet9.Range("b2").Value


For a = 1 To tubesperrow Step 1


ActiveCell.FormulaR1C1 = "O"


Call lab_O(noofrows)


ActiveCell.Offset(-noofrows * 2, 3).Select


Next a


End Sub

'