Can you do another video on hiding multiple columns, I have a spreadsheet that I would like to hide about 20 columns with one click of a button, they are not consecutive columns, so a video like this would really help.
Private Sub ToggleButton1_Click() Dim xAddress As String xAddress = "B:E" If ToggleButton1.Value Then Application.ActiveSheet.Columns(xAddress).Hidden = True ToggleButton1.Caption = "Show Column" Else Application.ActiveSheet.Columns(xAddress).Hidden = False ToggleButton1.Caption = "Hide Column" End If End Sub Just replace the column you want to hide
hello... can we attach the button with a code in a module ? I mean to write the code in a module and the button to read the code from there instead of the worksheet which might have already a large code
Hi , great video. Do you know how to hide multiple ranges of rows? For example, ranges "R:T, AC:AE, BG:BI". I have managed to use your formula to hide a single range of rows by just adding "R:T" for example but I can't seem to get multiple ranges to hide on one click. Thanks in advance.
Hi, please accept my apologies for the late reply. I need to check the comments on my videos more often. The answer to your question is added on the code below. I have also added programmer's comments to make it easier to understand. Hope this helps. talk soon '=============Code Starts================== Private Sub showhideC_Click()Dim MyC As String Dim MyD As String MyC = "C" MyD = "D" 'Hide rows - Create the variable for the first row My1 = 1 'Hide rows - Create the variable for the second row (by following the same process you will add more rows if you wish) My2 = 2 If showhideC.Value Then Application.ActiveSheet.Columns(MyC).Hidden = True Application.ActiveSheet.Columns(MyD).Hidden = True 'Show Hide Rows (Show: Row 1 and Row 2) Application.ActiveSheet.Rows(My1).Hidden = True Application.ActiveSheet.Rows(My2).Hidden = True Else Application.ActiveSheet.Columns(MyC).Hidden = False Application.ActiveSheet.Columns(MyD).Hidden = False 'Show Hide Rows (Hide: Row 1 and Row 2)Application.ActiveSheet.Rows(My1).Hidden = False Application.ActiveSheet.Rows(My2).Hidden = False End If End Sub '=============Code End==================
Hello! What if you want to add another button to do the same thing on other columns? Btw your tutorial helps me a lot, but im stack on add a second button :S Thanks in advance
You add the new button and you give a different name, than you add the VBA code I have on my sample file which can be found on the description and create more variables e.g. MyA = A. MyB = B, etc than you create an if statement "IF showhideC.Value Then" Just follow the example code I provided you with. If you still have an issue, please email me your excel doc on: team@a-webdesign.com and I will fix it and send it back to you. Talk soon
Hi, please accept my apologies for the late reply. I need to check the comments on my videos more often. The answer to your question is added on the code below. I have also added programmer's comments to make it easier to understand. Hope this helps. talk soon '=============Code Starts================== Private Sub showhideC_Click()Dim MyC As String Dim MyD As String MyC = "C" MyD = "D" 'Hide rows - Create the variable for the first row My1 = 1 'Hide rows - Create the variable for the second row (by following the same process you will add more rows if you wish) My2 = 2 If showhideC.Value Then Application.ActiveSheet.Columns(MyC).Hidden = True Application.ActiveSheet.Columns(MyD).Hidden = True 'Show Hide Rows (Show: Row 1 and Row 2) Application.ActiveSheet.Rows(My1).Hidden = True Application.ActiveSheet.Rows(My2).Hidden = True Else Application.ActiveSheet.Columns(MyC).Hidden = False Application.ActiveSheet.Columns(MyD).Hidden = False 'Show Hide Rows (Hide: Row 1 and Row 2)Application.ActiveSheet.Rows(My1).Hidden = False Application.ActiveSheet.Rows(My2).Hidden = False End If End Sub '=============Code End==================
Step 1. Create a new Toggle button and name it as: showhideCD Step 2. Go to the VBA window (Developer --> Visual Basic tab). Step 3. You will need to create a Variable (MyC), and a Variable (MyD) where "C = Column C" and "D = Column D" to do this you will need to do the following: Dim MyC As String Dim MyD As String "Then you will need to assign the column letter": MyC = "C" MyD = "D" Step 4. Finally, you will need to create your if statement (Please note that "showhideCD" is the name of your Toggle button): If showhideCD.Value Then Application.ActiveSheet.Columns(MyC).Hidden = True Application.ActiveSheet.Columns(MyD).Hidden = True Else Application.ActiveSheet.Columns(MyC).Hidden = False Application.ActiveSheet.Columns(MyD).Hidden = False End If So your code should look similar to the code below: Dim MyC As String Dim MyD As String MyC = "C" MyD = "D" If showhideCD.Value Then Application.ActiveSheet.Columns(MyC).Hidden = True Application.ActiveSheet.Columns(MyD).Hidden = True Else Application.ActiveSheet.Columns(MyC).Hidden = False Application.ActiveSheet.Columns(MyD).Hidden = False End If to hide more column follow the above structure. Create more variables and add them onto your if statement. Hope this answers your question. Please let me know if you need any more help. Please like and subscribe if this instructions helped you. Thanks Talk soon
Hi, please accept my apologies for the late reply. I need to check the comments on my videos more often. The answer to your question is added on the code below. I have also added programmer's comments to make it easier to understand. Hope this helps. talk soon '=============Code Starts================== Private Sub showhideC_Click()Dim MyC As String Dim MyD As String MyC = "C" MyD = "D" 'Hide rows - Create the variable for the first row My1 = 1 'Hide rows - Create the variable for the second row (by following the same process you will add more rows if you wish) My2 = 2 If showhideC.Value Then Application.ActiveSheet.Columns(MyC).Hidden = True Application.ActiveSheet.Columns(MyD).Hidden = True 'Show Hide Rows (Show: Row 1 and Row 2) Application.ActiveSheet.Rows(My1).Hidden = True Application.ActiveSheet.Rows(My2).Hidden = True Else Application.ActiveSheet.Columns(MyC).Hidden = False Application.ActiveSheet.Columns(MyD).Hidden = False 'Show Hide Rows (Hide: Row 1 and Row 2)Application.ActiveSheet.Rows(My1).Hidden = False Application.ActiveSheet.Rows(My2).Hidden = False End If End Sub '=============Code End==================
Thank you this worked for me (Microsoft 365)
Can you do another video on hiding multiple columns, I have a spreadsheet that I would like to hide about 20 columns with one click of a button, they are not consecutive columns, so a video like this would really help.
Private Sub ToggleButton1_Click()
Dim xAddress As String
xAddress = "B:E"
If ToggleButton1.Value Then
Application.ActiveSheet.Columns(xAddress).Hidden = True
ToggleButton1.Caption = "Show Column"
Else
Application.ActiveSheet.Columns(xAddress).Hidden = False
ToggleButton1.Caption = "Hide Column"
End If
End Sub
Just replace the column you want to hide
so amazing.......
I try this trick ...on header footer hide & show ...... via toggle button.....
🚩🚩🚩🚩🚩🚩🚩🚩🚩🚩🚩
Great 👍
Thank you... It really helped me a lot...
Glad to hear that
Thank you.
You're welcome!
Thanks Mr. but i was wondering if it works with graphics too?
Yes but requires more code. When I get a chance I will create a new tutorial for this
hello... can we attach the button with a code in a module ? I mean to write the code in a module and the button to read the code from there instead of the worksheet which might have already a large code
yes, I will create a new tutorial soon.
Hi , great video. Do you know how to hide multiple ranges of rows? For example, ranges "R:T, AC:AE, BG:BI".
I have managed to use your formula to hide a single range of rows by just adding "R:T" for example but I can't seem to get multiple ranges to hide on one click.
Thanks in advance.
Hi, please accept my apologies for the late reply.
I need to check the comments on my videos more often.
The answer to your question is added on the code below. I have also added programmer's comments to make it easier to understand.
Hope this helps.
talk soon
'=============Code Starts==================
Private Sub showhideC_Click()Dim MyC As String
Dim MyD As String
MyC = "C"
MyD = "D"
'Hide rows - Create the variable for the first row
My1 = 1
'Hide rows - Create the variable for the second row (by following the same process you will add more rows if you wish)
My2 = 2
If showhideC.Value Then
Application.ActiveSheet.Columns(MyC).Hidden = True
Application.ActiveSheet.Columns(MyD).Hidden = True
'Show Hide Rows (Show: Row 1 and Row 2)
Application.ActiveSheet.Rows(My1).Hidden = True
Application.ActiveSheet.Rows(My2).Hidden = True
Else
Application.ActiveSheet.Columns(MyC).Hidden = False
Application.ActiveSheet.Columns(MyD).Hidden = False
'Show Hide Rows (Hide: Row 1 and Row 2)Application.ActiveSheet.Rows(My1).Hidden = False
Application.ActiveSheet.Rows(My2).Hidden = False
End If
End Sub
'=============Code End==================
@@ComputingAcademy1:57
why i can not find developer in my ex sheet
You will need to go to - File ->> Options ->> Customize Ribbon ->> Check the 'Developer' option
Thank you Mr. I am from Indonesia. I like your channel.
Design mode in my excel is disable. Toogle button also is disable. How to enable it??
Ensure a button or any other object you are going to use and edit is selected
An example project hiding multiple columns can be downloaded from here: a-webdesign.com/ToogleButton.xlsm
I am getting an error message on "If ToggleButton1.Value Then
". Do you know why?
I got error code 13, trying to hide a few cells not the entire thing.
Did you fix it? if not, please send me the file on: team@a-webdesign.com and I will try to fix it for you.
Hello! What if you want to add another button to do the same thing on other columns? Btw your tutorial helps me a lot, but im stack on add a second button :S Thanks in advance
You add the new button and you give a different name, than you add the VBA code I have on my sample file which can be found on the description and create more variables e.g. MyA = A. MyB = B, etc than you create an if statement "IF showhideC.Value Then" Just follow the example code I provided you with. If you still have an issue, please email me your excel doc on: team@a-webdesign.com and I will fix it and send it back to you.
Talk soon
how to hide only rows in a column, for ex. from b2 to b5
Hi, please accept my apologies for the late reply.
I need to check the comments on my videos more often.
The answer to your question is added on the code below. I have also added programmer's comments to make it easier to understand.
Hope this helps.
talk soon
'=============Code Starts==================
Private Sub showhideC_Click()Dim MyC As String
Dim MyD As String
MyC = "C"
MyD = "D"
'Hide rows - Create the variable for the first row
My1 = 1
'Hide rows - Create the variable for the second row (by following the same process you will add more rows if you wish)
My2 = 2
If showhideC.Value Then
Application.ActiveSheet.Columns(MyC).Hidden = True
Application.ActiveSheet.Columns(MyD).Hidden = True
'Show Hide Rows (Show: Row 1 and Row 2)
Application.ActiveSheet.Rows(My1).Hidden = True
Application.ActiveSheet.Rows(My2).Hidden = True
Else
Application.ActiveSheet.Columns(MyC).Hidden = False
Application.ActiveSheet.Columns(MyD).Hidden = False
'Show Hide Rows (Hide: Row 1 and Row 2)Application.ActiveSheet.Rows(My1).Hidden = False
Application.ActiveSheet.Rows(My2).Hidden = False
End If
End Sub
'=============Code End==================
How to hide a multiple columns?
Step 1. Create a new Toggle button and name it as:
showhideCD
Step 2. Go to the VBA window (Developer --> Visual Basic tab).
Step 3. You will need to create a Variable (MyC), and a Variable (MyD) where "C = Column C" and "D = Column D" to do this you will need to do the following:
Dim MyC As String
Dim MyD As String
"Then you will need to assign the column letter":
MyC = "C"
MyD = "D"
Step 4. Finally, you will need to create your if statement (Please note that "showhideCD" is the name of your Toggle button):
If showhideCD.Value Then
Application.ActiveSheet.Columns(MyC).Hidden = True
Application.ActiveSheet.Columns(MyD).Hidden = True
Else
Application.ActiveSheet.Columns(MyC).Hidden = False
Application.ActiveSheet.Columns(MyD).Hidden = False
End If
So your code should look similar to the code below:
Dim MyC As String
Dim MyD As String
MyC = "C"
MyD = "D"
If showhideCD.Value Then
Application.ActiveSheet.Columns(MyC).Hidden = True
Application.ActiveSheet.Columns(MyD).Hidden = True
Else
Application.ActiveSheet.Columns(MyC).Hidden = False
Application.ActiveSheet.Columns(MyD).Hidden = False
End If
to hide more column follow the above structure. Create more variables and add them onto your if statement.
Hope this answers your question.
Please let me know if you need any more help.
Please like and subscribe if this instructions helped you.
Thanks
Talk soon
I have an example project which you can download here: a-webdesign.com/ToogleButton.xlsm
@@ComputingAcademy Awesome, so simple yet so useful, thanks a lot!
@@ComputingAcademy Thank you - this worked for me!
Hello, Is there a similar way to hide rows as well?
Hi, please accept my apologies for the late reply.
I need to check the comments on my videos more often.
The answer to your question is added on the code below. I have also added programmer's comments to make it easier to understand.
Hope this helps.
talk soon
'=============Code Starts==================
Private Sub showhideC_Click()Dim MyC As String
Dim MyD As String
MyC = "C"
MyD = "D"
'Hide rows - Create the variable for the first row
My1 = 1
'Hide rows - Create the variable for the second row (by following the same process you will add more rows if you wish)
My2 = 2
If showhideC.Value Then
Application.ActiveSheet.Columns(MyC).Hidden = True
Application.ActiveSheet.Columns(MyD).Hidden = True
'Show Hide Rows (Show: Row 1 and Row 2)
Application.ActiveSheet.Rows(My1).Hidden = True
Application.ActiveSheet.Rows(My2).Hidden = True
Else
Application.ActiveSheet.Columns(MyC).Hidden = False
Application.ActiveSheet.Columns(MyD).Hidden = False
'Show Hide Rows (Hide: Row 1 and Row 2)Application.ActiveSheet.Rows(My1).Hidden = False
Application.ActiveSheet.Rows(My2).Hidden = False
End If
End Sub
'=============Code End==================