Free Microsoft Excel 2013 Quick Reference

Allow Hide & Unhide Column/Rows On Protected Sheet

I'm familiar with the code to allow grouping/ungrouping in a protected spreadsheet; thanks to the this old thread, http://www.ozgrid.com/Excel/outlining-protected.htm.

How would I code to allow for hiding/unhiding cloumns and rows while still protecting the spreadsheet?

Also, how could i allow for this file to be shared? Currently i'm getting an debug error when another person enters the file.


Post your answer or comment

comments powered by Disqus
Hello Everyone,

I am trying to figure out a macro (or formula if one exists) to hide/unhide columns in my excel worksheet based on the cell value in the first row of the column. Based on how my sheet is set up, this is my desired result:

If cell C1:=0, then Hide Column C. If not, unhide column C
If cell D1:=0, then Hide Column D. If not, unhide column D
If cell E1:=0, then Hide Column E. If not, unhide column E
If cell F1:=0, then Hide Column F. If not, unhide column F
If cell G1:=0, then Hide Column G. If not, unhide column G
If cell H1:=0, then Hide Column H. If not, unhide column H
If cell I1:=0, then Hide Column I. If not, unhide column I

See attached sample workbook for the set up. I must say that I am relatively new to using excel macros and programming in excel. Any detail you all can provide would be very helpful. Thanks in advance. Regards,

Taghos

Hello;

I'm trying to protect/hide the formulas on a w/s by protecting the w/s so that I may not accidentally delete or overwrite them.
It works fine with the exception that the Hide & Unhide column no longer available either via Format::Column::Hide and Unhide or by a macro.

The macro to hide / unhide columns produces:
Run-time error '1004':
"Unable to set the Hidden property of the Range class"
and in the macro code:
.....Range("J1:K1").Select
...>Selection.EntireColumn.Hidden = True
is highlighted

Is there a workaround this difficulty; namely protecting the w/s formulas and still be able to Hide / Unhide columns ??

Thank you kindly.

This is killing me. I made this simple little macro to hide/unhide a row on a different sheet based on a check box. The row hides fine. The problem I have is unhide. I uncheck the box, but the row stays hidden. What am I missing to make this little gem come to life? This is for tracking popcorn sales for scouting and I'm trying to make it as easy to use as possible.
Thanks
Curtis

code:

Sub Patrol1_Scout1()
If True Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = True
End If

If False Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = False
End If
End Sub

Hi everyone
This is only the second pice of code i have written so appologise for any schoolboy errors. I am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement. Can anyone help?


	VB:
	
 UpdateCashflow() 
     '
     ' Update Cashflow
     '
     
    Application.ScreenUpdating = False 
    ActiveSheet.Unprotect Password:="LD" 
    ActiveSheet.Select 
     
    Dim c As Integer 
     
    For c = 5 To 64 
         
        If Cells(54, c) = "" Then 
            ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True 
        Else 
            ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False 
        End If 
         
    Next c 
     
    ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True 
    Application.EnableEvents = True 
     '
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, I am using Dave Hawley's code to allow grouping/ungrouping on a protected sheet:

	VB:
	
 Worksheet_Activate() 
    With Me 
        .Protect Password:="eb", UserInterFaceOnly:=True 
        .EnableOutlining = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to add the option of inserting rows to the protected sheet using this code. I am using an Excel 2007 file. PLEASE HELP!!
Thank you,
Reayn.

Hi,

I have groups of data in A5:AW200 with various blank rows in between, and would like to be able to automatically hide and unhide (toggle) the blank rows, but only for those rows where cells in column A are blank.

I'd also like to hide those same groups of blank rows on Sheets 2-5, which are laid out exactly the same as Sheet 1, but since they contain cell references to and are mirrors of Sheet 1, certain rows on those sheets may appear blank, but in fact are not. (I should probably have separate buttons on each sheet, but not sure).

I know Sheet 1 can be used to test for conditions (blanks rows) and translate the results to Sheets 2-5, but I'm not sure how to do it.

I would appreciate any help with this.

Hi Guys,

I am using Excel 2000. I am attempting to write a macro that hides rows on a protected worksheet.

This is how I am hiding a row.

Sheets("Sheet1").Range("A10:A49").EntireRow.Hidden = True

Everything was working great until I protected the sheet. Now I get an error. Is it possible hide rows on a protected worksheet?

Thanks for your help!

Good Morning folks
can you hide and unhide columns and rows? I'm talking about to hide the actual columns. For example for columns, A B C D E and rest is just gray area. You cannot even see F G H and so on columns header. Same scenario with the rows.
I have received an excel sheet which after certain columns and rows dont see any columns and rows header itself.
any help will be appreciated.
thx

Hello Friends, I’m new to your forum, so want to greet you all and thank you in advance.
Now to my small problem. I want to use macro to help me unhide all hidden sheets, rows and columns.
I managed to write code to unhide all sheets, here’s the code:

	VB:
	
 Sheets_Unhide() 
     
    Dim sh As Worksheet 
    For Each sh In Worksheets 
        sh.Visible = True 
    Next 
End Sub 
[B][/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I could not write code to unhide rows and columns, after this action is executed.

Generally I want code to work in the following way, first unhide all sheets, then unhide all columns/rows on all sheets (despite the fact sheet is active or not).
Is it possible to for action like this to take place? If yes please help to resolve this issue.
Thank you all

can you allow the selection of an entire row when the sheet is protected. most cells in the row are locked and the user cannot access them at all. I merely wish to make the row that they are reading easier to identify.

I am trying to hide and unhide columns in the following sheet. I would like to be able to hide or unhide based off of the numbers in the purple row; however, I cannot get past the first step. I am using the following script but it is hiding columns "D" through "O" instead of just "H" through "L". I think it might be because I have the cells in row "5" merged but I cannot seem to work around it.

Thank you in advance for any help!

Sub Hide_Columns()
Columns("H:L").Select
Selection.EntireColumn.Hidden = True
Columns("Q:W").Select
Selection.EntireColumn.Hidden = False
End Sub

******** ******************** ************************************************************************>Microsoft Excel - Company Cash Flow Templete.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE2F2G2H2I2J2K2L2M2N2O2D3E3F3G3H3I3J3K3L3M3N3O3D4E4F4G4H4I4J4K4L4M4N4O4D5D6E6F6G6H6I6J6K6L6M6N6O6=
DEFGHIJKLMNO2*************************-***************************-***************************-***************************-***************************-***************************-***************************-*********************************9*************************-*******************************-******************************-***3ActualActualActualActualActualActualActualActualActualProjectionProjectionProjection4**************************1**************************2**************************3**************************4**************************5**************************6**************************7**************************8*******************************9*************************10*****************************11****************************12*5200661200622006320064200652006620067200682006920061020061120061220067****************1*****************2*****************3*****************4*****************5*****************6*****************7*****************8********************9***************10******************11*****************12*8*January**February**March**April**May**June**July**August**September**October**November**December*Cash Flow*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hello everyone,

I have a particular problem with a worksheet I am working on at the moment, basically if row 5 has a 0 displayed I want that column to hide, but if row 5 has text of any value displayed I want it to unhide, the range is E5 to BA5 across.

I have draft VBA code as follows:

Sub HideColumn()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For a = 0 To 50
    If Range("E5").Offset(0, a).Value = "Hide Column" Then Range("E5").Offset(0,
a).EntireColumn.Hidden = True
    Next a
    Application.ScreenUpdating = True
    
    
End Sub
This works to an extent in that it will hide the columns with the 0 displayed, but when I unhide them the word TRUE is input in other blank cells, and also if text is input for the hidden rows they do not unhide...

Hi everyone,

I am trying to allow people to insert rows on a sheet that I need to password protect. Here is the catch: I only want to be able to allow the users to insert rows in a certain part of the sheet, not anywhere in the sheet. The standard method of selecting the option won't work because of this. Is it possible to do this?

Thanks,

Jeff

I'm trying to create an inspection report that will allow me to create rows on Sheet 2 based on 2 values on Sheet 1.

SHEET 1

This sheet will be used to create sheet 2 rows.
B1 will represent how many parts I will be inspecting
B2 will represent how many inspection points per part

SHEET 2

A2 will be where B1 gets calculated
B2 will be where B2 gets calculated

EXAMPLE

I want each part inspected to be represented by a letter and each inspection point a number.
So if I am inspecting 3 parts with 4 inspection points I would generate the following rows on Sheet 2:

Letter is A2 Column
Number is B2 Column

A1
A2
A3
A4
B1
B2
B3
B4
C1
C2
C3
C4

I inserted the EXCEL file for a better understanding of what my set up is.

HELP WITH ROW INSERTION.xlsm (16.9 KB)

Thanks in advance,

John

Hello,

I am trying to lock cells on a spreadsheet so that they cannot be changed. Once I do this I find that I cannot insert rows/collumns or hide/unhide parts of the spreadsheet. Is there a way for me to lock certain cells but still be able to change/add rows/collumns to the portion of the spreadsheet that is not locked?

Thanks,

amesic

Hello Experts,
How do you hide & unhide columns based on a dropdown menu selection?
Across row 2, I have column headings named Budget, Actual, Variance,
Percentage. Using a dropdown Data Validation List, I'd like to hide the
selected columns.

The dropdown menu selection would yield the following result:
Variance - only Variance column will hide.
Percentage - only Percentage column will hide.
Variance and Percentage - both columns will hide.
Show All - both Variance and Percentage will unhide.

I have approximately 100 columns. So this code will alleviate a lot of
manual hide and unhide. I'd appreciate your input.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I am trying to link a row on one sheet into a column on another sheet?

I protected a worksheet. However I am not able to hide/unhide grouped rows when the worksheet protection is enabled. Is there a way around this?

regards,

Hello!

I am trying to conditionally hide/unhide coloumns. I have code tied to buttons on the individual sheets to hide and unhide ranges of columns. this code is....

Sub Hide_Datacombine_Click()
Application.ScreenUpdating = False
    Dim cell As Range
    
    For Each cell In Me.Range("G1:AG1,AI2:BI2").Cells
        If VarType(cell.Value) = vbDouble And cell.Value = 0 Then
            cell.EntireColumn.Hidden = True
        End If
    Next cell
Application.ScreenUpdating = True
End Sub
Sub Show_Datacombine_Click()
     Application.ScreenUpdating = False
     Range("A:IV").EntireColumn.Hidden = False
     Application.ScreenUpdating = True
End Sub
where the two subs are tied to two buttons on the given sheet. This code works exactly as I want it to.

Where my problem lies is that I have a master refresh which goes and grabs new data, and may potentially change the columns which need to be hidden or unhidden. I have tried to adapt my working code in order to automate the unhide then hide process. Here is what i came up with...(note this code is on another worksheet in the wb)

 Sub Refresh_Click()


Application.ScreenUpdating = False

'Refreshing Lims Data Query, Pivot table on Datacombine
Call Refresh_Results

'Selecting Sheets Datacombine, alerting to potential problems, hiding unused columns as result of pivot refresh
Sheets("Datacombine").Select

    For Each cell In Me.Range("B32").Cells
        If VarType(cell.Value) = vbDouble And cell.Value = 0 Then
            Response = MsgBox("There is no information to display for the given criteria. Try a different sample point
or different date ranges to get information on the product of interest.", 0, "No Data to Display")
        End If
    Next cell
For Each cell In Me.Range("B33").Cells
        If VarType(cell.Value) = vbDouble And cell.Value = 0 Then
            Response = MsgBox("Formulas only extend to row 2000.", 0, "Data Overflow, Edit Criteria to Reduce
Amount of Data")
        End If
        Next cell
        
Range("A:AD").EntireColumn.Hidden = False
   
For Each cell In Me.Range("D2:AD2").Cells
    If VarType(cell.Value) = vbDouble And cell.Value = 0 Then
           cell.EntireColumn.Hidden = True
       End If
   Next cell

'Selecting Sheets Machine Results hiding unused coloumns
Sheets("Machine Results").Select
    Range("B:IV").EntireColumn.Hidden = False
    Range("32:60").EntireRow.Hidden = False
  
       
      For Each cell In Me.Range("C2:IV2").Cells
        If VarType(cell.Value) = vbDouble And cell.Value = 0 Then
            cell.EntireColumn.Hidden = True
        End If
    Next cell
    For Each cell In Me.Range("B31:B58,C59").Cells
        If VarType(cell.Value) = vbDouble And cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        End If
    Next cell

'Selecting Sheets Quality Results hiding unused coloumns, displaying refresh complete message
Sheets("Quality Results").Select

Range("B:IV").EntireColumn.Hidden = False

    
    For Each cell In Me.Range("C2:IV2").Cells
        If VarType(cell.Value) = vbDouble And cell.Value = 0 Then
            cell.EntireColumn.Hidden = True
        End If
    Next cell
Response = MsgBox("Refresh Complete, See Results Sheets", 0, "Data Updated")

Application.ScreenUpdating = True

End Sub
I pasted the entire sheets code w/ comments so you can see what I am trying to do. The code doesn't return bugs/errors, but it also fails to unhide then hide the columns/rows on the referenced sheets.

I believe my problem is in calling the sheets to then unhide/rehide the columns.

Thanks in advance!

Hi,
Need help.
Using Excel 2007 would like to create a worksheet that will hide/unhide certain columns based on the text selected in a drop down list.

The purpose of the document is track company personnel individual trainings.

Employees holding different positions need different trainings. That’s why I’d like to create a spreadsheet that will help a user to choose what training is required for a person holding a certain position.

For example, I have 25 employees and 6 positions in a company. Positions are: A, B, C, D, E and F.

Each position has specific training requirement. Let’s say there are 7 trainings: tr1, tr2, tr3, tr4, tr5, tr6, tr7.

Employees holding position ‘A’ need to be trained on tr1, tr4 and tr5
Employees holding position ‘B’ need to be trained on tr1, tr2 and tr6
Employees holding position ‘C’ need to be trained on tr2, tr5 and tr7
And so on.

So, what I’d like to do is when position is selected in a drop down list, required trainings will appear and unnecessary ones hide.

Sample spreadsheet attached.

Following scenario:

Column A – Employee name
Column B – (drop down list) – position of employee
Columns C to W – are trainings. Each training 3 columns.

If position selected from drop down list, let's say position 'B' then unhide - tr1, tr2, tr6; hide - tr3, tr4, tr5. tr7 ... the same scenario for other positions.

The form is there but i do not know how to make it work.
Please help.

Thank you very much in advance,

Vagif

Hi everyone,
I need some vba to turn a column of one sheet into a row on another sheet.
For example I would like the data of Sheet1!A1:A5 to span Sheet2!A1:E1.
What's the easiest/most efficient way to do this?
Thanks in advance.

Hi
How to limit the number of columns on a sheet? I'd like to have a 5 columns on a sheet and afterwards coming the "gray area" (Like it is coming after 256 columns or 65536 rows)

Thanks in advance
Tarvi

Hi,

I need to allow users to be able to filter and use outlining (hide/unhide grouped data) in protected sheets. The code i did is as follows:

Const PW = "xxxx"

Private Sub Workbook_Open()

For Each sht In ActiveWorkbook.Sheets
sht.Protect _
Password:=PW, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
userinterfaceonly:=True, _
AllowFiltering:=True, _
EnableOutlining:=True

Next sht
End Sub
The code worked fine until I included "EnableOutlining:=True".

Any ideas on what I'm doing wrong?

regards,

On my worksheet i have markets going horizontally with each market taking up 2 columns. For instance, market 1 would take up columns E&F with each subsequent market after that taking up 2 more columns all the way to columns FG&FH (lot of markets).

So what i want to do is create a dropdown list in column A that has a list of markets, obviously simple. However what i'd like to do is have a macro that hides all columns that don't pertain to market in the dropdown.

So as an example, lets say in the dropdown the user picks "Market 4" and "Market 4" are columns M&N. What i'd like the macro to do is go through and hide the other columns that are not M&N so the user can enter the information.

Then when they are done entering information the macro will unhide all columns again.

--------------------

The reason for this type of fuction is to enter cost data and to not mistakenly enter it into a wrong market. Does this sound doable? I have been searching through the posts and trying to find a macro that would work and not having luck.

thanks for any help.


No luck finding an answer? You could always try Google.