You are here:

Excel/Check Boxes to Hide Columns

Advertisement


Question
QUESTION: Hi Jan,

>>>What I would like is if the check box beside name IS checked, the related column on the “Gangs Work” sheet would be UnHidden and IF the check box is NOT checked the related column on the “Gangs Work” sheet would be Hidden.<<<


Im sorry I was a bit premature when I said it was all working perfectly.  Upon further testing, it seems that it works as expected upto and including checkbox no 29, if I check ANY box after the 29th box,  it hides the 29th relative column.

Is there a limit of 29?

Any ideas?

Kind regards,
Keith

ANSWER: Hi Keith,

The code has three prerequisites:

1. The checkboxes top-left corner must be inside the cell to the immediate right of the cell with the name
2. The cell with the name must have a hyperlink to the cell in the column that is to be hidden.
3. Each checkbox must have a unique name (check by right-clicking a checkbox to select it, look in the name box to the left of the formula bar). If you have Excel 2007 or up, click the binoculars on the home tab (far right) and select "Selection pane".

---------- FOLLOW-UP ----------

QUESTION: Good Afternoon Jan,

Again, thank you for your assistance with this.

The link http://1drv.ms/1j95kbo holds the actual sheets that I am trying to build.

I have checked the three prerequisites and I believe all is ok.

This is strange, each hyperlink goes to & fro the 2 sheets as expected.

I have put a "breakpoint" (I think thats right)in the code then gone and unchecked the check box AZ (row 49) on Crew Names sheet, looking back at the code I notice the variables are:

1CT = 47      AZ is the 47th box

bCheck = False

oCell = AZ

but column AH on the Crew Share sheet is about to be hidden.

Look forward to hearing from you.

Keith







http://1drv.ms/1j95kbo

Answer
Hi Keith,

Sorry for the belated reply!!

Somehow, some of the cells contain more than one hyperlink (I didn't even know you could do that!). The second hyperlink appears to point to the right location.

Solution would be to remove all hyperlinks and recreate them.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.