Excel Macro Tutorial : Toggle Button in Excel

February 9, 2012| Posted by : | Filled in Excel Macro Tutorial

Some times, we require a single control or Button which can perform different action on first click and some other Action on the second click. This can be achieved by adding Toggle Button in your Excel Workbook.

How to Add Toggle Button in Excel Workbook:

It’s very easy to add Toggle Button in your workbook. Follow the below steps to add toggle button:


Step 1. Go to Developer’s Tab
Step 2. Click on the Toggle Button Icon from the Control as shown below:

Toggle Button

Toggle Button



Step 3. Now Double Click on that Button


Toggle Button

Toggle Button




Step 4. Here you can write your Code.


Toggle Button VBE Code

Toggle Button VBE Code



Example:

I am going to explain you by giving two examples, how we can use this button in real time applications.


1. Hiding/un-hiding Row or Columns by Toggle Button: In this case, Clicking on the same button, each time different action will be performed, it means, once it will hide it and next time it will un-hide it.

Private Sub ToggleButton1_Click()
'For hiding and un-hiding Columns
  Columns("C:F").Hidden = Not Columns("C:F").Hidden
'For hiding and un-hiding Rows
  Rows("1:2").Hidden = Not Rows("1:2").Hidden
End Sub



2. Sorting in Ascending and Descending Order by Using Toggle Button:
i) Add the Code of sorting in Ascending and Descending in a Regular Module in VBE of your Workbook


To get the Code for Sorting in Ascending or Descending

ii) Paste the below code in the VBE of the Worksheet where you have Put your button:


Private Sub ToggleButton1_Click()

	If ToggleButton1.Value = True Then
		Call Sort_Ascending
	Else
		Call Sort_Descending
	End If

End Sub


To Know About Spin Button in Excel Click Here


 
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

Comments

comments

Related posts:

  1. Excel Macro Tutorial : VBA Spin Button In the previous Article How to write Excel Macro –...
  2. Excel Macro Tutorial : How to add different types of controls like Text Box, Radio button, button etc. in Excel Dear Readers, This is the 3rd Article of this Excel...
  3. Excel Macro Tutorial : VBA Control Property In last post Excel Macro Tutorial : Visual Basic Editor...
  4. Excel Macro Tutorial : How to write Excel Macro – Your First Excel Macro Dear Readers,   Till now we have learnt following things...
  5. Excel Macro Tutorial : Add Developer Tab in Excel – 2007 and 2010 In the Previous Article What is Excel Macro of Excel...

Subscribe To Get New Posts delivered directly to your Mailbox

Leave a Reply

  • About
  • Downloads
  • Excel Formula
  • Excel Tips
  • Excel Macro Tutorial
  • VBA Functions
  • Archives
  • Contact
  • Excel Formula
  • Excel Functions
  • Excel Macro
  • Excel Macro Basics
  • Excel Macro Tutorial
  • Excel Tips
  • HP QC
  • Interesting VBA Functions
  • New to Excel Macro ?
  • Personal
  • Popular Articles
  • 2014
  • 2013
  • 2012
  • 2011