toggle button with Tri - State, in two states hide colums and one not...How?

Nikko · Feb 3, 2019

Hello everyone,

after long back and forth I have copied the code into a toggle button. Works well, except for the columns. The toggle button is set to three states, Tri - State.
As you have noticed, my VBA knowledge is not the best ... in a nutshell

My question from the whole ... How can I hide in the given code the columns I: M in two states and in a state not?

En

Private Sub ToggleButton1_Change()
ActiveWorkbook.ActiveSheet.Unprotect ("1234")
Sheets("Menu").Columns("I:M").EntireColumn.Hidden = Not ToggleButton1
ActiveSheet.Range("E13, E14, E15, E16, E17, E18, E19, E20, E21, E22, E23, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23").Cells.Locked = True
ActiveSheet.Range("J4, J5, J6, J7, J8, J9, J10, J11, J12, J13, J14, J15, J16, J17, J18, J19, J20, J21, J22, J23, K4, K5, K6, K7, K8, K9, K10, K11, K12, K13, K14, K15, K16, K17, K18, K19, K20, K21, K22, K23").Cells.Locked = True
If IsNull(ToggleButton1.Value) Then
Sheets("Menu").Columns("I:M").EntireColumn.Hidden = Not ToggleButton1

ActiveSheet.Shapes("Textfeld 18").Visible = False
ActiveSheet.Shapes("Grafik 25").Visible = False
ActiveSheet.Range("E13, E14, E15, E16, E17, E18, E19, E20, E21, E22, E23, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23").Cells.Locked = False
ActiveSheet.Range("J4, J5, J6, J7, J8, J9, J10, J11, J12, J13, J14, J15, J16, J17, J18, J19, J20, J21, J22, J23, K4, K5, K6, K7, K8, K9, K10, K11, K12, K13, K14, K15, K16, K17, K18, K19, K20, K21, K22, K23").Cells.Locked = True
ToggleButton1.Caption = "Middle Picture"
ElseIf ToggleButton1.Value = False Then
ActiveSheet.Shapes("Textfeld 18").Visible = True
ActiveSheet.Shapes("Grafik 25").Visible = True
ToggleButton1.Caption = "Small Picture"
ElseIf ToggleButton1.Value = True Then
'Sheets("Menu").Columns("I:M").EntireColumn.Hidden = Not ToggleButton1
ActiveSheet.Shapes("Textfeld 18").Visible = False
ActiveSheet.Shapes("Grafik 25").Visible = False
ActiveSheet.Range("E13, E14, E15, E16, E17, E18, E19, E20, E21, E22, E23, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23").Cells.Locked = False
ActiveSheet.Range("J4, J5, J6, J7, J8, J9, J10, J11, J12, J13, J14, J15, J16, J17, J18, J19, J20, J21, J22, J23, K4, K5, K6, K7, K8, K9, K10, K11, K12, K13, K14, K15, K16, K17, K18, K19, K20, K21, K22, K23").Cells.Locked = False
ToggleButton1.Caption = "Big Picture"
End If
ActiveWorkbook.ActiveSheet.Protect ("1234")
End Sub

closed the code:

Thx in Advance

Nikko

mikerickson · Feb 3, 2019

Your toggle button has three states (True, False and Null). A column can be hidden or visible.
What do you want to happen when the button is Null, when it is True and when it is False

Code:

If IsNull(ToggleButton1.Value) Then
    rem do the Null option
ElseIf ToggleButton.Value Then
    Rem do the True option
Else
    Rem do the False option
End If

Nikko · Feb 3, 2019

Hi mikerichson,

Many thanks for your quick response. As you've seen, I'm a novice in vba stuff. Could you help me in how do I insert this into my code?

Nikko

My Aswer Is This · Feb 3, 2019

How can a Toggle Button be set to NULL ??

mikerickson said:
Your toggle button has three states (True, False and Null). A column can be hidden or visible.
What do you want to happen when the button is Null, when it is True and when it is False

Code:

If IsNull(ToggleButton1.Value) Then rem do the Null option ElseIf ToggleButton.Value Then Rem do the True option Else Rem do the False option End If

mikerickson · Feb 3, 2019

In a userform, this works

Code:

ToggleButton1.Value = Null

as does clicking on a TRUE ToggleButton.

My Aswer Is This · Feb 3, 2019

I thought a Toggle button if clicked would toggle from True to False

Ok. But it only becomes Null when The Workbook is closed or Toggle Button is never activated or is set to Null using Vba. Correct?

mikerickson said:
In a userform, this works

Code:

ToggleButton1.Value = Null

as does clicking on a TRUE ToggleButton.

Nikko · Feb 3, 2019

Hi everyone,

As written in advance, I have no knowledge in VBA, actually can excel only well.

Can anyone PLEASE include the code line - Sheets ("Menu"). Columns ("I: M"). EntireColumn.Hidden = Not ToggleButton1 - So in my code embed it in two states the columns are hidden and in a state not ?

What you are trying to say to me, I can not understand (even if I want it) because my knowledge is not enough. Besides, my English is not the best either. Have all preconditions to annoy one

))

My Code:

Private Sub ToggleButton1_Change()
ActiveWorkbook.ActiveSheet.Unprotect ("1234")
Sheets("Menu").Columns("I:M").EntireColumn.Hidden = Not ToggleButton1
ActiveSheet.Range("E13:E23, F13:F23").Cells.Locked = True
ActiveSheet.Range("J4:J23, K4:K23").Cells.Locked = True

If IsNull(ToggleButton1.Value) Then
ActiveSheet.Shapes("Grafik 25").Visible = False
ActiveSheet.Range("E13:E23, F13:F23").Cells.Locked = False
ActiveSheet.Range("J4:J23, K4:K23").Cells.Locked = True
ToggleButton1.Caption = "Middle Picture"

ElseIf ToggleButton1.Value = False Then
ActiveSheet.Shapes("Textfeld 18").Visible = True
ActiveSheet.Shapes("Grafik 25").Visible = True
ToggleButton1.Caption = "Small Picture"

ElseIf ToggleButton1.Value = True Then
ActiveSheet.Shapes("Textfeld 18").Visible = False
ActiveSheet.Shapes("Grafik 25").Visible = False
ActiveSheet.Range("E13:E23, F13:F23").Cells.Locked = False
ActiveSheet.Range("J4:J23, K4:K23").Cells.Locked = False
ToggleButton1.Caption = "Big Picture"

End If
ActiveWorkbook.ActiveSheet.Protect ("1234")
End Sub

mikerickson · Feb 3, 2019

I have a TripleState=True ToggleButton on a userform.

As I click on it, it cycles through False, null, True, False, null, True, ...

Oddly enough when transitioning from False to null, neither the Change event or the Click event fires, I had to use the MouseUp event to capture that.

Excel 2011 for Mac

mikerickson · Feb 3, 2019

@Nikko,
If you are using the ToggleButton to hide or unhide columns I would set the .TripleState property to False. You only need the button have two states, True or False.

My Aswer Is This · Feb 3, 2019

You can also Toggle a Column from Hidden to Visible by using a command Button and code like this:

Code:

Private Sub CommandButton1_Click()
'Modified  2/3/2019  12:33:30 PM  EST
Columns(3).Hidden = Not Columns(3).Hidden
End Sub

toggle button with Tri - State, in two states hide colums and one not...How?

Nikko

Board Regular

Excel Facts

mikerickson

MrExcel MVP

Nikko

Board Regular

My Aswer Is This

Well-known Member

mikerickson

MrExcel MVP

My Aswer Is This

Well-known Member

Nikko

Board Regular

mikerickson

MrExcel MVP

mikerickson

MrExcel MVP

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

toggle button with Tri - State, in two states hide colums and one not...How?

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Well-known Member

MrExcel MVP

Well-known Member

Board Regular

MrExcel MVP

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock