Macro Enabled Column Hiding Based On Data Validated Dropdown

Mar 26, 2014

I'm trying to write macro for hiding columns based on a drop down answer. The drop down would be in cell B7 on Sheet 1. The colums affected would be in each worksheet in the workbook. The data validation would have a drop down selection choice of 1 through 16. The columns affected range would be H through W. For example, in the drop down if 1 was selected, only column H would show. If two, H and I would show...etc. This macro should affect all worksheets in the workbook EXCEPT sheet 1.

View 2 Replies


ADVERTISEMENT

Hiding Columns Based On Other Column Data

Feb 27, 2008

I'm trying to hide columns based on information in another column. If that column contains "A" or "B", hide columns "U" through "W". If it contains "A", "B", "C", or "D", hide columns "V" through "W". Etc, etc., so on and so forth...

I've snipped the code I found earlier and modified it to (what I thought) was correct for my application, but it doesn't seem to do anything. (btw, should I get an error if it doesn't run correctly?)

Here is the code as modified:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "P:P" Then

If LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Then

Columns("U:W").EntireColumn.Hidden = True

ElseIf LCase(Target.Value) = "A" Or LCase(Target.Value) = "B" Or LCase(Target.Value) = "C" Or LCase(Target.Value) = "D" Then

View 9 Replies View Related

Hiding Sheets Based On Value In Dropdown Cell

Mar 21, 2007

I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.

I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.

if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.

View 14 Replies View Related

Validated Dropdown Lists In Merged Cells?

May 14, 2012

Do not use validated drop down lists in merged cells.

It will only be a matter of time before it breaks Excel.

(Both 2007 & 2010)

View 1 Replies View Related

Excel 2010 :: Hiding Rows Based On Dropdown

Mar 6, 2013

I am currently working on a workbook for work. It is a basic input output sheet. I have data from work that i copy and paste into sheet 1 and I have it arranged into sheet 2.

I am using windows 7 with Excel 2010, though needs to be compatible with 2007 using XP.

What I am trying to implement. I would have a drop down box in cell AD-4 with the current list

Select
Week 1
Week 2
Week 3
Week 4

What I want to implement is a code that if I select "week 1" from drop down then it will hide the entire row if data in column B = week 2, week 3, week 4, week 6

also

If week 2 is selected then hide entire row if column b = week 3 , week 4, week 5

so basically hide the weeks that are after the current selected week.

In addition to this I would also like to hide the entire row, regardless of drop down selection if data from J, K and O all = 0 or (blank).

(the first row is the current Row labels, I have a lot of current hidden columns).

A
B
J
K
O

Supervisor Name
Agent Name
Needs Complete
Completed w/o Turn in
Needs Turn In

[Code] ........

View 7 Replies View Related

Hiding A Column Based On A Field Value

Apr 4, 2008

I have a worksheet called "Calendar" with values D4:X4

if the value "Name" appears in any of these cells then the corresponding column where the entry appears will be hidden. otherwise the column will be visible.

View 9 Replies View Related

Hiding Rows Based On Text Value In Column

Feb 2, 2010

I would like to have a VBA macro that would look over my entire spreadsheet and hide the individual row if any cell in Column A contains the text "xyzzy". The "xyzzy" can be anywhere in the cell....beginning, middle or end.

View 9 Replies View Related

Run Macro On Selection Of Validated Cell

Mar 27, 2007

I have a macro that I am running "onentry" of a worksheet. The problem is that I have some cells that are validated to allow a list. If I manually type a word from the list in the validated cell the macro runs. However, if I make a selection from the list the macro does not run. I guess vba does not see my selection as an entry.

View 9 Replies View Related

Hiding Columns Based On Cell Value (macro)

Dec 1, 2009

I have an elaborate spreadsheet that I have made for my company. I will give a little background how I have it set up:
E6 contains a drop down menu of all products that we sell, and line items are populated based on the selection here. In columns N and O, I have a "Business Partner Costs" table that calculates a specific discount % based on the business partner.

Some of our products are sold to BPs with no discount. For these specific products, I am trying to get the BP Costs table to disappear, or hide.

Basically, I want something like: "=if(or(e6=ae2,e6=ae7,e6=ae12,e6=ae13,etc),hide columns N&O,show columns N&O)" in macro format.

View 9 Replies View Related

Vlookup Changed Validated Cell Value In Macro

Jul 2, 2009

I have a worksheet containing a cell (say A1) that has a validated list of times (data/validation/list) where the cells in the list contain times in 8 minute increments. The user can select cell A1 and manually select any of the validated times from the list. A vlookup formula on this worksheet uses the content of cell A1 to look for this time in another section of the worksheet. When the time is selected manually, the vlookup formula works properly.

As part of a macro, the macro will change the time in cell A1. I do this in the macro by selecting and copying the desired time from the validation list and pasting it into A1. Cell A1 does change to the new value, however the vlookup formula that uses A1 shows a result of #N/A. If I manually change the time, it again works. I am looking for a way to have the macro change this cell to another time and for the vlookup formula to work as it should.

View 2 Replies View Related

Toggle Data Validated Option On / Off Within With Right-click?

Aug 2, 2014

This area of my worksheet, Sheets("ACTIVITY").Range("AM2:AM172"), is currently populated by text values from a dynamic, validated list, 'DEL_2'.

Is there a simple way to effectively 'toggle' (on/off) data validation - maybe with right-click - in this area, so that the user can type what they want if their chosen option can't be found in DEL_2?

View 3 Replies View Related

Hiding Cells From Dropdown List

May 27, 2014

I have seen a few threads here about this, but not for how I need it to work.

I will have a drop down list with 8 options, (Days of the week Wed to Wed, hence the 8 days lol) There will now be 8 columns, one for each day.

I want to be able to select the day from the list, and then have it hide all the columns except for the day selected. When a different day is picked, un hide that one and hide the rest.

I will attach a book in the reply...

View 14 Replies View Related

Use Value Multiple Times Before Hiding It In Dropdown List?

May 24, 2013

I used this method to hide previously selected values in a drop down menu. I thought by including that value multiple times in my "employee" list, I could use it multiple times before hiding it. Is there any way to skirt this? I need to use each value three times before it disappears.

View 2 Replies View Related

Auto Update Or Resync Values In Data Validated Lists

Feb 8, 2010

I was wondering if there was some way to auto update or resync values in data validated lists which are dependant on information in other cells.

I attach my spreadsheet. My list in Cell F32 changes depending on what has been selected in cell F31. However, my problem is that, until you select cell F32, it still keeps the previous value on display, if you know what I mean. So! What I would like to happen, is when I select a different option in cell F31, then I would like cell F32 to update and show the first available option from the new list?

View 10 Replies View Related

Spreadsheet That Allows Users To Select Items From A Validated Data List

Mar 25, 2009

I am making a spreadsheet that allows users to select items from a validated data list. Each item in the list has a numeric value in an adjacent column. When the user is entering data they will select from a drop down of the data list in column "A" and I would like to have the numeric value that corresponds with their selection to then autofill in column "B". I have tried VLOOKUP and IF formulas but cannot seem to get anything to work.

View 9 Replies View Related

Macro Enabled Workbook IOS App?

Dec 5, 2013

Is it possible to have a macro-enabled workbook to work on ios software(iphone or ipad app)? Currently I cant find any spreadsheet app that will let you have a macro enabled workbook. The code from the posts below would be great if they worked on my phone or ipad.

Scan barcode to excel with date & time stamp in & out.

View 4 Replies View Related

Hiding Previously Selected Options In Dropdown List?

Jul 25, 2014

I wanted to hide/remove the already selected options in a dependent drop down list so that the next time i am left with only the remaining options.

View 7 Replies View Related

Save As Macro Enabled Document Only

Mar 6, 2014

I have been having a problem with a document which I have created as a xlt format which then is saved locally but because some users arent as computer literate I have found them saving the document in non macro enabled format which means all macros are removed causing me a huge issue. Is there a macro which will only allow the document to be saved as macro enabled?

View 2 Replies View Related

Protecting Macro-Enabled Workbook

Apr 29, 2012

is it possible to protect a macro-enabled workbook with password.

View 5 Replies View Related

Macro-enabled Template Without Warning

Feb 13, 2010

I have created a template including a macro. It works as expected besides the issue that macros need to be enabled manually. This is totally annoying. I need to have it work directly.

Or what would be another way to have a macro available all the time I run Excel?

View 9 Replies View Related

Wrong Entry Into Data Validated Cell Triggers Debug On Change Event

Dec 14, 2011

I have a Change Event macro that works properly. I also have several cells that contain a drop down list. If a user tries to enter an incorrect entry...the debug is triggered on my even change macro.

Here's the piece of the Change macro that has issues. Specifically, the .undo line highlights.

With Application
.ScreenUpdating = False
.EnableEvents = False

Set SelectedCell = ActiveCell
myTitle = Cells(Range("Titles").Row, Target.Column).Value
myRow = Target.Row
.Undo: myBefore = Target.Value
.Undo: myAfter = Target.Value
SelectedCell.Select

View 3 Replies View Related

How To Split Macro Enabled Text Into Different Cell

Apr 12, 2014

I usually copy ready macro from google. And I currently working on a check printing excel for my company to print check. I have googled and extract this macro from an author and place into my excel. But i run into a problem which is, the returned text will exceed the boundaries,

is there a way to spilt the text, lets say from after "dollars " to another appointed cell?

[Code].....

View 5 Replies View Related

Excel 2010 :: Pivot Table Dropdown Filter Hiding Items?

Dec 22, 2011

In the coming months the company i work for will be transferring from Excel 2003 to Excel 2010 (i know, a little late......) and now i am testing some things at home.

We deliver lots of Excel reports to our clients where we are using Pivot tables. In excel 2003 we where able to hide items from the dropdowns using properties - hide items but when i now open an excel 2003 file in excel 2010 and want to filter the pivot table to (let's say) another month i see all my hidden items.

Is there an option in excel 2010 to hide items like i could do in 2003? (either regular option or VBA)

View 4 Replies View Related

Macro Based On Dropdown List

Jun 24, 2009

I am trying to improve a process for someone in my workgroup to save a lot of time when reporting performance.

I have a spreadsheet with a drop down list of names. When I select a name from the drop down list I would like to copy a range of values on another sheet and paste special value a few columns over on the same sheet. I would like to have this only happen for the range of vaules on the row that correspondes with the name that I selected from the drop down list on the original sheet.

View 9 Replies View Related

Excel Macro Enabled Dashboard To Interactive Web Page

Jun 3, 2014

I have created a dashboard in excel and want to convert it into an interactive web page.

View 3 Replies View Related

Save Sheet As Macro Enabled Workbook Using Codes

Jan 28, 2014

Working with a macro enabled workbook that has many sheets what would be a good code that would save Sheet1 into a new MACRO enabled workbook so that the 'Buttons' (which are linked to macros) on this specific sheet will be the only macros that will be copied to the new workbook as opposed to the rest of the macros that are found in different sheets?

Here is what I g=have:

[Code] ......

View 11 Replies View Related

Prompt User To Open Sheet As Macro Enabled - VBA

Jun 19, 2013

I have got a protected sheet with macros, how can I ensure that users can only open the sheet as Macro enabled only otherwise the sheet would not open?

I understand that some users may have different Macro security settings?

View 1 Replies View Related

Dynamic Dropdown List Based On Column Filter?

Jun 9, 2014

I have a spread sheet that has three sheets, PROJECTS - TIMES - LISTS Time sheet.xlsx PROJECTS contains a list of 'In Progress' and 'completed' projects for work, with relevant other data, I.e. PROJECT No. / STATUS etc This will grow to 1,000's of rows TIMES will contain the amount of time a person spends on a PROJECT on a daily basis. This will grow to 10,000's of rows LISTS contain basic validation list for the first 2 sheets.

Part A
I would like to have a validation drop down list for the PROJECT NO column in the TIMES sheet that only lists the PROJECT NO from the PROJECTS sheet where STATUS <> COMPLETED. I.e. I only want to display the PROJECT NO's for current projects.

Part B
Even though I only want the PROJECT NO used, can the drop down list also contain other column's data for reference information only before one is selected.

View 2 Replies View Related

Macro That Will Copy Data Based On The Column Headings

Apr 8, 2009

i need a macro that will copy data based on the column headings, i have two worksheets, the first worksheet is the raw data and second worksheet will be the results sheet, the issue is the headings i have in raw data is different to the headings i have in the results sheet and the heading will not always be in same column in the results sheet, so for example in Raw data i have heading Quantity and in destination sheet i have heading Units, so the macro should copy the data for quantity in sheet raw data and paste to sheet "Results' under heading "Units". The header row on both sheets is row 3. I do have more headers but if I know how to do one then i can apply the same method for the others.

Sheer raw data

Quantity 820,655.00 177,015.00 6,652,041.00 1,517,267.00

Sheet results

Units 820,655.00 177,015.00 6,652,041.00 1,517,267.00

View 9 Replies View Related

Macro To Copy Based On Date In Dropdown?

Mar 7, 2014

I want to create a macro assigned to a button that copies values from one cell to another.

I want it to be smart in that way that it should copy the values to predefined cells cells based on the month that has been selected in a drop down field. So if i select for January, it should copy the fixed selection to other target cells then when September was selected.

View 1 Replies View Related







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