Status Bar / Percentage bar?

[deerae]

Hey i am new to the forum nice to meet you all.

I stumbled upon a problem which i cant solve.

Please help me.

I want a formula that can make this.

VBA/Excel i dont care i hope you can get it to work thanks !

 

[AKTools]

Exacly this could only solved with VBA.
But you could do something similar with a formula-
You could use the REPT function for this

Set the font in cell A1 to "Wingdings" and type the following formula in Cells A2

=REPT("n",A1)

 

[AKTools]

And here is my VBA solution

1. Press ALT+F11 to open VBA Editor
2. Insert a new Module
3. Paste this code

4. [*]Function ProgressBar(ByVal MyValue As Double, ByVal MyShapeName As String)
       On Error Resume Next
       ActiveSheet.Shapes(MyShapeName).Width = MyValue
       On Error GoTo 0
   End Function
   [*]

5. Go to your worksheet and insert a Shape (recangle)
6. Select the recangle
7. Click in the Formular bar and refernce your value cell. Type like =$A$1 if your percentage value sits in cells A1
8. goto another cell (free choice) and type =ProgressBar(A1;"NAME OF YOUR RECANGLE")
9. And thats it!

If you now change the value in Cell A1 the width of your new Progressbar will change too!

 

[deerae]

And here is my VBA solution

1. Press ALT+F11 to open VBA Editor
2. Insert a new Module
3. Paste this code

4. [*]Function ProgressBar(ByVal MyValue As Double, ByVal MyShapeName As String)
       On Error Resume Next
       ActiveSheet.Shapes(MyShapeName).Width = MyValue
       On Error GoTo 0
   End Function
   [*]

5. Go to your worksheet and insert a Shape (recangle)
6. Select the recangle
7. Click in the Formular bar and refernce your value cell. Type like =$A$1 if your percentage value sits in cells A1
8. goto another cell (free choice) and type =ProgressBar(A1;"NAME OF YOUR RECANGLE")
9. And thats it!

If you now change the value in Cell A1 the width of your new Progressbar will change too!

i get stuck at step 7

could you explain it more clearly what you mean by formular bar?
i am using excel 2000 maybe thats the problem why i didnt understand what you actually ment?

 

[AKTools]

Formular Bar -> below the menu -> Where you enter formulas
have a look! this (green) is what I mean http://www.techrepublic.com/i/tr/downloads/scott_lowe/formula-bar.jpg

 

[deerae]

its a lot to ask but could you make an example workbook for me?

thanks ..

i tried it but it doesnt change the rectangle into a progressbar

thanks!

 

[AKTools]

sure no problem! You can download it from my website!
http://www.aktools.de/index.php/downloads/category/5-mrexcel-massage-board -> "How to use a Shape as a ProgressBar with VBA"