Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Controls > Control types
Deze pagina in het Nederlands

Using controls on worksheets

Overview of the available controls

The table below shows which controls there are and describes each one shortly.

Pic. Control name Control use and remarks
Label Label Add a label next to other controls.
Groepsvak Frame  Use this control to group other controls. OptionButtons placed within a frame work together.
Opdrachtknop Button (CommandButton) Start a macro
Selectievakje CheckBox Set an option, Select multiple options from a list of options.
Keuzerondje OptionButton Select one option from a (short) list.
Keuzelijst ListBox Select an option from a list. Multiple options are visible at the same time.
Keuzelijst met invoervak ComboBox Select an option from a list, only the selected option is visible.
Schuifbalk ScrollBar Quickly change numeric values.
Kringveld Spinner Change values step-by-step easily.
Tekstvak TextBox Enter a text.
Wisselknop ToggleButton Toggle status. This control is not recommended, I advise to use either a checkbox or a set of two OptionButtons.

 


Comments

All comments about this page:


Comment by: adam (7/1/2011 1:37:07 AM)

Hello,
I have a sheet that uses a scrollbar linked to a graph, which I use to show students how the data changes between set points.

I get tired of holding the scrollbar down to 'play' the 'animation'. Is there a play control button? Could a scrollbar be tweeked so that you only have to click the button once and it just goes through to the end?

That would be very nice. Any ideas?

thx

 


Comment by: Jan Karel Pieterse (7/4/2011 1:39:12 AM)

The easiest way would be to use a little bit of VBA code that increments the cell which holds the scrollbar's result. Suppose it is cell A1 on sheet1:

Sub RunChart()
    dim lCt as Long
    Dim lWait as Long
    For lCt=1 to 1000
        Worksheets("Sheet1").Range("A1").Value=lCt
        For lWait=1 to 10000 'Change this number to increase/decrease speed
        Next
    Next
End Sub

 


Comment by: adam samuelson (7/10/2011 2:12:55 AM)

Hi Jan,
Thankyou for your prompt reply, the code you provided worked very well on the data and is exactly the type of thing I wanted.

The problem I have now is the graph doesn't 'refresh' with each data change, it only changes when the macro finishes running.

Interesting to note that when I slide the scrollbar (slowly) the graph does change, so I suspect it is a refresh rate problem.

Any ideas as to how I could solve this new problem??

Cheers

 


Comment by: Jan Karel Pieterse (7/11/2011 12:24:05 AM)

Hi Adam,

Just before the waiting loop, include this statement:

DoEvents

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].