Button To Unlock & Unhide All Columns

Apr 14, 2008

I have a sheet that upon opening a macro runs to hide a selection of columns based on cell containing certain words (see here).

I now also require the workbook to be protected so that the hidden columns (which Im sure are locked as default when hidden) are locked as are columns N & S. Now, I know I can do this by protecting the sheet and unprotecting the columns I require (ie N & S - with the hidden cells remaining locked anyway).

The issue is, every time the workbook is opened this protection must be in place.

However, I require a button, which could simply reside in A1, that when pressed, will unhide and unlocked all cells. - This is needed as its vital I can easily copy rows to another sheet (and delete from existing sheet) as and when required.

if possible, re-pressing the button would hide and proetct the worksheet however this is not too significant as long as when I close and open the workbook everything is hidden and protected as specified above.

There is no need to password protect anything.

View 9 Replies


ADVERTISEMENT

Button To Hide / Unhide Columns

Jul 25, 2012

I'm looking to make a simple button that would hide a given range of columns.

This is the simplest I could find:

VB:
Sub button1()
Columns("AD:AE").EntireColumn.Hidden = Not Columns("AD:AE").EntireColumn.Hidden
End Sub

Although this works nicely, there was another way to do it (looks more ergonomic and doesn't take up spreadsheet space). Here's a screenshot of what I mean: ColumnHide.gif

View 2 Replies View Related

Macro For Hide / Unhide Columns With Same Button

Nov 28, 2012

Need a macro for each button.

The sheet will be protected with a password (in the future, users will have varied access privileges).

Column A is designated as the "Button" Column.

There are 5 buttons here. Each representing the area on the sheet that needs to be viewed. Once the button is pressed, it takes you to that section of the sheet. At this time, I have designated each column area as:

a-z
aa-az
ba-bz
ca-cz
da-dz

I have tried this formula with opening tabs, but this won't work.

View 5 Replies View Related

Record Macro To Hide And Unhide Columns (one Button)

Jan 10, 2012

I'm trying to record a macro which will hide and unhide columns K:P of data, but I only want one button. I know how to do this to produce one button for hiding and another for unhiding...but I want one combined button.

How to use vba, how I do this via the macro recorder?

View 9 Replies View Related

Hide/Unhide Multiple Sets Of Columns With Toggle Button

Sep 23, 2009

I am trying to create a very large spreadsheet and i have got everything organized, but for easy viewing i want to have a toggle button that hides and un-hides certain sets of the columns (ie. Press down and it hides columns "B-F", "H-J", and "M-O".... then on depress it shows all the information again)

I have got some of the coding down, but am only able to get it to hide one set at a time, not all the sets. Can anyone help me out and let me know how i tell it to run multiple commands from the one press? here is the code i have already: ...

View 6 Replies View Related

Unlock A Cell With A Button

Mar 19, 2009

I want to let the user unlock certain cells but only after they have clicked a button. To notify that the cell is unlocked I also want it to change colour.
Is this possible?

I have attached a small example. Password is mg

unlock.xlsx

View 6 Replies View Related

The Unlock Button Gives An Error

May 15, 2008

I have an lock button to lock a worksheet, and a unlock button to unlock the same worksheet.
The unlock button though gives me an error when I don t set the password correct or not at all.
My code is like that

Sub UnlockSheet()
'
' Unlock Sheet Makro
'
'
ActiveSheet.Unprotect
End Sub

I am sure that is very simple for you guys here but for me a problem I can not figure out yet why.

View 9 Replies View Related

Unlock Columns If Column Is Not Blank In Any Row

Jan 30, 2009

Unlock columns if Column is not blank in any row

Using VBA
On Sheet("Draft Final") , search every row and

If Col A is not blank, then unlock Columns G: M and unlock Columns O:U

View 4 Replies View Related

Hide & Unhide Button On Top Of Sheet ?

Feb 9, 2009

I want to hide & unhide columns using only (plus minus) button which is appearing on top of current sheet. I have couple of files with that options built in, but I dont know how to do it. I tried to see macros but appears that there is no macros at all in that file??

View 3 Replies View Related

Hide/Unhide Entire Row With Button IF

Feb 9, 2010

I'm trying to get a Button to hide and unhide rows depending on Column A only which is a pasted link to other sheets. The rows should hide when Cell A is empty or 0.

or 2 buttons one for hiding and the other for unhiding rows.

View 6 Replies View Related

Hide And Unhide Column With One Button

Mar 11, 2014

Here is my code ( Recorded by macro )

Sub Tally_Chck()
'
' Tally_Chck Macro
'
'
Columns("D:E").Select
Range("E1").Activate
Selection.EntireColumn.Hidden = True
Range("B3").Select
End Sub

I want Hide And Undie Column With One Button...
-Once Click Hide
-Again Click Unhide

View 4 Replies View Related

Hide / Unhide Column With A Button

Oct 23, 2008

Using Excel 2007, I figured out how to insert a Toggle Button and program it to hide or unhide my columns F and G (which show cost data for products we sell) depending on the state of the button.

What I'd like to add to the button is text and functionality as follows:

- Columns F and G are shown, Toggle button IS NOT depressed and the button says "Hide Cost"

- Columns F and G are hidden, Toggle Button IS depressed and the button says "Show Cost"

I figured out how to change the text on the button, but can't figure out how to change it based on the button state. I know there has to be a way to do this.

View 9 Replies View Related

Hide And UnHide Combobox With Option Button?

Sep 24, 2012

I have a TextBox and a ComboBox on a worksheet, is it possible to hide them with option buttons?. So only one box shows at a time.

Option Button1 show TextBox
Option Button2 show ComboBox

View 2 Replies View Related

Toggle Button To Hide/unhide Rows

Jun 12, 2007

I have a command button that hides rows based on zero value in Col B then a 2nd btn to unhide those rows. The challenge is that as I want this code for several workbooks that will have data of varying row lengths & not all the zero values will be in the exact same place for each workbook or each month. Having done a little research on the Board (& googled) I have not found an answer to my challenge. So I am sure someone will point me in the right direction -

Private Sub CommandButton1_Click()
BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2
Range("B3:B" & EndRow).EntireRow.Hidden = True

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Range("A2").Activate................................

View 2 Replies View Related

Hide/unhide Button Based On Data Validation

Oct 8, 2009

I am creating a disclaimer in one of my sheets.

Cell I15 contains 'Are you a resident for tax purposes?' Cell N15 has the data validation list of Yes or No.

Then I have a button below which covers rows18, 19 and 20.

Is there a way I can have this button hidden until Cell N15 = Yes?

View 10 Replies View Related

Excel Toggle Button For Hide / Unhide Rows With Zero

Nov 14, 2013

I am trying to make an excel toggle button and am stumped. I am trying to create a toggle button that hides the entire row if it finds a 0 in a preset range that I am calling "Alpha". I have tried this code but it's not working.

Code:
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
For Each cell In Range("Alpha")

[Code].....

View 3 Replies View Related

Writing A Cleaner Loop & Toggle Hide/unhide With Button

Oct 12, 2007

What the code does is, when the command button is clicked, if any of the validation list in a particular range is equal to"Select_Product.." or "Select Feature", then hide those rows.

Also, is there a way to toggle hide and unhide rows using one command button. I would like the code to not only hide the rows but when clicked again, it'll unhide it. Is this possible?

Private Sub CommandButton1_Click()

Dim c As Range
Dim d As Range
Dim myRange As Range
Dim featureRange As Range

View 9 Replies View Related

How To Unhide Columns

Aug 13, 2012

I want to unhide columns K:DY if cell B2 = ALL, if cell B2 is something other than ALL, hide columns K:DY.

Is there any VBA code for that.

View 6 Replies View Related

Cannot Unhide Columns In A Worksheet

Feb 27, 2009

The workbook contains 5 worksheets, 4 of which will unhidden just fine; the remaining worksheet ("AnnualBudget" refuses to unhide columns AD to IV. Of course, this is the sheet that contains data in those hidden cells and I cannot get to the data. The sheet is unprotected and all freeze panes removed.

I have copied this sheet to a new sheet in the same workbook:

If I copy only formulas and number formats, the new sheet has all columns unhidden. If I copy formats, I have the same issue with the new worksheet. It’s like these columns are forever locked from view.

FYI I am using Excel 2007 with a 2003 compatability worksheet

A copy of the workbook is attached. Any idea what’s happening?

View 6 Replies View Related

Hide/Unhide The Columns

Jun 16, 2009

I've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.

View 3 Replies View Related

Hide/Unhide Columns

Dec 19, 2008

I have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).

View 2 Replies View Related

VBA To Unhide And Hide Columns?

Oct 23, 2002

I want to create two buttons.. one name HIDE and the other UNHIDE.

What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)

For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)

View 9 Replies View Related

Hide / Unhide Columns By The Use Of + And -

Mar 5, 2013

I once saw an excel sheet where I could hide or unhide a section by some + and - signes above the column-letters.... I have searched for this but I only get the ordinary hide/unhide solutions.

View 4 Replies View Related

Macro To Unhide Columns - Call A Macro Then Revert Columns To Previous State

Jul 17, 2014

I have on sheet1 a number (72 at the moment) of Form CheckBoxes.

In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)

Then, go through and Check All checkboxes

Call MyMacro

Once MyMacro is complete (Filtering & Printing)

Revert the checkboxes to their original state.

The purpose of the checkboxes:

When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden

Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.

View 11 Replies View Related

Hide Or Unhide Columns With One Macro

Nov 21, 2008

I have a single button I want to use to call a macro to:
1.Hide columns C:AZ if they arent already hidden
2.Unhide columns C:AZ if they are already hidden.

View 2 Replies View Related

Set Up Macro That Will Hide And Unhide Columns?

Nov 27, 2012

I want to set up a macro that will hide and unhide columns.

View 9 Replies View Related

VBA Cycle Hide / Unhide Columns?

Aug 5, 2013

The code below if I run once Hide the Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF") And if I run it again Unhide the Range("H:FV,GG:IV") And Show all columns

Code:
Sub Hide_Unhide()Range("H:FV,GG:IV").Select
If Selection.EntireColumn.Hidden = True Then
Range("H:FV,GG:IV").EntireColumn.Hidden = False
Else
Range("H:FV,GG:IV").Select
If Selection.EntireColumn.Hidden = False Then
Range("H:FV,GG:IV").EntireColumn.Hidden = True
End If
End If
Range("A1").Select
End Sub

Above code is working with 2 cases now is it possible to add 3rd case Hide Or Unhide Range("H:GG,GI:HJ,HO:IV") in the same code, and shows the Range("A:G, GH, HK:HN")

Resume: my request
Step1-if I run macro First time it must Hide Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF")
Step2-If I run macro Second time it must Hide Range("H:GG,GI:HJ,HO:IV") and shows the Range("A:G, GH, HK:HN")
Step3-if I run macro third time it must show all columns

And repeat same cycle all time Step 1 to 2, Step2 to 3 and Step3 to 1

View 2 Replies View Related

Using VBA To Hide And Unhide Multiple Columns?

Dec 1, 2013

I have a worksheet with a fair amount of data. It is split into two parts: the main part is a table with data in columns D to AR while the other part is simply a list with checkboxes against each item. I have set things up so that when a checkbox is ticked against an item in the list, then a 2 appears in row 2 above the relevant column in the main table; if the checkbox is unticked then a 2 appears above the relevant column.

What I seek is a macro that will hide all columns that do not have the associated item ticked; in other words, I want columns to be hidden if there is a 1 in the relevant cell in Row 2 (and visible if there is a 2 there).

I have used the following code (obtained from this forum), but it doesn't work as expected. When I select the items, the cells in Row 2 react as expected, but the hiding and unhiding only occurs when I go to another Worksheet and then return to the Worksheet where the data is. Obviously I want the macro to work immediately I tick or untick a checkbox. What is wrong with the code I have used?

Private Sub Worksheet_Activate()
Call HideCols
End SubSub

[Code]....

View 6 Replies View Related

Code To Unhide All Columns Upon Deactivate Tab

Jan 27, 2014

I need to perform an action (unhide all columns) whenever I switch to a different worksheet.

Detail: I have a file with 2 tabs (worksheets). Tab 1 contains daily data, and when people are done with the current week they group and hide the columns for that week. This allows them to only view the new week and do an easy copy/paste into other applications. Tab 2 totals up the daily data from Tab 1 and shows monthly totals. The problem is that when they hide Tab 1 columns for past days, the formulas in Tab 2 don't "find" that hidden data. I would like to write a basic code that unhides all Tab 1 columns when I switch to Tab 2 so the formulas on Tab 2 reflect accurate totals. My thought is that it would be Worksheet code on Tab 1 using "Deactivate". I've tried to piece together different bits of code but can't get anything to work properly. I don't want them to have to run a macro or click a button, I'd like it to be automated when they switch tabs.

View 3 Replies View Related

Macro To Hide & Unhide Certain Columns

Sep 19, 2008

I'd like a macro that can hide/unhide certain columns. At the moment, the columns I want to hide/unhide are F, I, M, P, U and Y.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved