1

I have a form control button which i want to use to group-ungroup columns.That is if it is clicked the first time it groups/hides those columns and next time it it clicked it unhides those columns.

I want to count the no of click on that button so that ,if the variable containing the no of clicks count is odd i will hide the columns else if it is even i will unhide the column.

this is my code 

Private Sub CommandButton1_Click()
Static cnt As Long
cnt = 0
Dim remain As Integer
cnt = cnt + 1


remain = cnt Mod 2

If remain = 1 Then
 ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
 End If

 If remain = 2 Then
 ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
 End If


End Sub

So how can i count the no of clicks on that button in a variable in vba. Sorry for the bad english?

shiven
  • 421
  • 2
  • 8
  • 19
  • 1
    I can give you the answer but please show us what have you tried? Questions asking for code must demonstrate a minimal understanding of the problem being solved.Include attempted solutions, why they didn't work, and the expected results. See also: [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) – Siddharth Rout Nov 28 '13 at 17:58
  • i have edited the post and given my code. – shiven Nov 28 '13 at 18:03

1 Answers1

3

Ok you do not need to use a count and keep adding to it. You can use a Boolean Variable instead. Here is an example. This works an an ON/OFF switch. 

Option Explicit

Dim boolOn As Boolean

Sub CommandButton1_Click()
    If boolOn = False Then
        boolOn = True

        MsgBox "OFF"
        '
        '~~> Do what you want to do
        '
    Else
        boolOn = False
        '
        '~~> Do what you want to do
        '
        MsgBox "ON"
    End If
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • `Sub CommandButton1_Click() If boolOn = False Then boolOn = True ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1 Else boolOn = False ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2 End If End Sub` This is the code i modified but this is not giving the desired effect i want – shiven Nov 28 '13 at 18:11
  • Please explain "Desired effect" – Siddharth Rout Nov 28 '13 at 18:12
  • sorry I rectified my mistake.Thanks a lot for the solution – shiven Nov 28 '13 at 18:22