VBA Excel - Hide Worksheet On Date Cell Value
Jan 9, 2014
I have a workbook that contains 14 worksheets. I have been looking for vba code that hides 3 of these sheets according to a date value. The date value is situated on sheet 1 cell B7. The 3 sheets I wish to hide are sheets 2, sheets 3 and sheets 4. These 3 sheets are required to be made unhidden until 3 days before date in sheet 1 cell B7. From the date value I want the workbook to hide the 3 sheets and can only be unhidden if a correct password is used to unhide these 3 sheets.
View 4 Replies
ADVERTISEMENT
May 23, 2014
Hide Columns based on Date value in cell (Worksheet Change event)Looking for a worksheet_change event macro that will automatically hide columns based on a value in cell S3. S3 is a data validation pick list of months based on the 1st day of each (e.g. 1/1/2014, 2/1/2014, 3/1/2014, etc.). I have date columns in row 6 from columns T through AQ. These values are 1/1/2014 (T6), 2/1/2104 (U6), 3/1/2014 (V6)....12/1/2015 (AQ6). I would like to hide columns that have a date in row 6 (T6:AQ6) that is LESS THAN (<) the date in S3 after the cell is value is changed.
View 2 Replies
View Related
Jul 16, 2014
knew if there was a way to hide the available tabs in an excel worksheet.
I know you may be thinking just right click and hide, however what I am looking for is a little different.
Basically I have a Macro Enabled work sheet which works on a "one click" system. The work sheet is full of buttons with assigned macros and data validation drop down boxes which link to many different tabs. The entire sheet is for information only so I will be locking the sheets to avoid data manipulation. The problem with right clicking and hiding the tabs is that it interfears with the macros.
All I really want to do is make these tabs not visible. I have set the sheet to open in full screen mode and they obviously are still there, but this is basically what I am looking to do. Not so much hide them in terms of sending them into the background, more so make them "drop off" the bottom of the screen to they are still active but not visible.
View 6 Replies
View Related
Mar 27, 2013
I have a workbook with worksheet like 2a, 2b, 2c, 2d, 2e, 3a, 3b...etc Is it anyway i can make a tab which will hide a group of worksheet (like 2a,2b....2e) together and unhide all the worksheet with another click on it? becasue i got too many worksheet to show in the bottom..
View 2 Replies
View Related
Nov 5, 2012
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
View 3 Replies
View Related
Oct 30, 2011
Operating System: Windows XP, Excel version: 2003
Aim: To create a Macro to hide all rows where the date in column D is before today. Column D has about 600 rows.
Current solution:
Code:
Sub Hide_Old2()
'Worksheet name
With Worksheets("Schedule")
'set start of date range
Set rngStart = .Range("D2")
'find end of date range
[Code] .....
The problem with this solution is its speed, or lack thereof. It causes the screen to hang and flicker while it cylces through. Is there some way to create a range based on the date and hide the range? or another solution?
Note: Autofilter is not an option, as the spreadsheet with the dates needs to be kept simple for other stakholders and the macro is being run from another sheet.
View 3 Replies
View Related
Jun 19, 2009
If Sheet1!A1 = FALSE, I want to hide Sheet2. If the value of Sheet1!A1 changes to TRUE, I want Sheet2 to be visible.
View 2 Replies
View Related
Jul 18, 2010
I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.
I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?
View 9 Replies
View Related
Oct 28, 2009
The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.
Problem:
What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.
If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.
I have found the following
Sub
If Target.Value = 1 Then
Rows(Target.Row).RowHeight=0
Else
End If
End Sub
Which I have changed to:
Sub
If F4.Value = Green Then
Rows("5,6,7,8,9,10.Row).RowHeight=0
Else
End If
End Sub
So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?
View 9 Replies
View Related
Jan 22, 2010
I have got a macro (from Mr Excel's MVP) that hide certain cells according to assigned cell value
What I need is:
to hide certain ranges in different worksheets according to the assigned cell value in the first worksheet
if sheets1.cell A1= "hi" then
range (" goooo") in sheet 1.hide
range ("deeeee")in sheet 2 .hide
range ("faaaaa") in sheet 3.hide
the macro I had is :
====================================
====================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "hi" Then
Worksheets("Sheet1").Rows(2).Hidden = True
Else
Worksheets("Sheet1").Rows(2).Hidden = False
End If
End If
End Sub
View 9 Replies
View Related
Apr 8, 2008
i have a workbook with two sheets. i have a command button on sheet 1 that inserts another worksheet from another workbook based on a cell value in sheet 1. i would like this specific command button on sheet 1 to be hidden or disabled until a value is entered into a certain cell on sheet 1 that matches a value in a named range on sheet 2.
View 7 Replies
View Related
Sep 9, 2013
I am looking to to hide rows of info on a sheet when it shows 3 days have passed.
I am also having a problem making the formula stay in the sheet columns without it showing 'blank' cell results..
View 9 Replies
View Related
Apr 30, 2013
I'm trying to create a workbook which has dates in column B, starting with row 10. I'd like to have a code which will hide the rows if the date is less than today upon workbook open. This would need to apply to worksheet1 only.
View 1 Replies
View Related
Dec 27, 2012
Code:
A
B
C
D
2
34
If I had columns as above with values in B1 and C1 but none in A1 and D1
How do I loop through so that columns A and D are hidden?
My spreadsheet has columns A to DI and different columns have values each day so I want to write code to hide just the columns with no values.
View 6 Replies
View Related
Feb 14, 2012
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies
View Related
Jul 9, 2012
I have set up a calendar control on a userform and got it to pop up when selecting one of 3 cells on a worksheet. The various bits of code making this work are below.
This code is attached to the Userform.
VB:
Private Sub Calendar1_Click()
With ActiveCell
.Value = Calendar1.Value
[Code]....
What I am struggling with is that I'd like to have the calendar that pops up in cell C18 (which is Date of Birth) default to starting on 1st January 1987 not todays date. Also, if there is a value in either of the 3 cells that use the calendar, then I'd like the calendar to display that date. If the cell is empty then todays date is fine for cells G3 and G26.
View 7 Replies
View Related
Feb 27, 2012
(Excel 2010): Hide row if cell C in this row is empty.
I've just started using macros and I'm sure there is one for this problem.
View 5 Replies
View Related
Apr 19, 2014
I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.
I am using Microsoft Excel 2010.
View 3 Replies
View Related
Oct 6, 2013
In excel 2007, I have an embedded image on a sheet which I want to make it hidden by default. When someone hovers over a particular cell say cell B3, then image should be displayed below this cell (sort of hyperlinking) and when the cursor or activell cell is not in B3 it should be hidden again. What steps/technique/VBA code will make it happen ?
View 3 Replies
View Related
Nov 21, 2009
I am trying to create a macro that will copy a sheet, move it to the end and rename it it with the next date of the value of the date in a particular cell.
I can do the first part no problem, however I am stuck with naming the sheet with the date which is the next day from the date value in a cell.
i.e. If the date value in A1 of the copied and moved sheet is 11 Nov 2009 I need the next sheet to be called 12 Nov 2009 in that format (dd mmm yyyy)
This is the code that I have come up with so far, but it gets stuck at the bottom line
Sub Macro3()
Dim WS As Worksheet, WB As Workbook
Set WB = ActiveWorkbook
Set WS = WB.ActiveSheet
WS.Copy After:=Sheets(WB.Sheets.Count)
WS.Name = Format(WS.Range("A1" + 1).Value, "dd mmm yyyy")
End Sub
View 7 Replies
View Related
Dec 28, 2006
I have a Excel Dates & Times column where the time is not always used. In these cases the time is 12:00AM. Is there a way to Custom Formats the cell so that the time is only visible if it is not 12:00AM?
View 6 Replies
View Related
Jan 30, 2014
I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
View 2 Replies
View Related
Apr 12, 2009
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
View 9 Replies
View Related
Feb 1, 2013
I have a worksheet that is protected with data entry cells unlocked.
1. Unprotect worksheet
2. Lock all cells in the worksheet and then protect.
3. After a date in a cell in the worksheet.
I'm a newbie to vba and at least know vba is the only way. And this need to be automated.
View 1 Replies
View Related
Feb 6, 2008
I have two workbooks, one a database that accumulates data over time, and the other a monthly report that needs to be populated based on information from the database. When moving data between the two, the following works fine (snipped a lot for ease of reading):
Workbooks.Open (database) ', Password:= /
Workbooks(reportbook). Sheets("Sheet1"). Range("A1").Value = month(Cells(282, 5))
As you can see, it simply puts the month from a cell in the database into the report workbook. However, if I change Range("A1") to Cells(1,1) I get a runtime error:
Workbooks.Open (database) ', Password:= /
Workbooks(reportbook).Sheets("Sheet1").Cells(1, 1).Value = month(Cells(282, 5))
I can generally work around this issue by changing the active workbook, but doing so is a big pain. Is there any way to use the second bit of code without popping an error message?
View 3 Replies
View Related
Feb 10, 2014
I have 2 worksheets, 1 with a table sorted like this (in a row):
component | start date | end date | assigned to
Second worksheet with a table like a calendar with dates and people (dates in the columns and people in the rows), every component is assigned to each person by dates.
............ | 01/01 | 02/01 | 03/01
---------|----- --|-------|-------
person 1 | comp1 | comp1 | comp2
-------- |--------|-------|-------
person 2 | comp1 | comp1 | comp3
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
You can see a template here: [URL] ........
View 9 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
Jan 26, 2014
I have a userform with numerous text boxes. One textbox is used to input a date with the following code
[Code] .....
When I have completed my userform I want to export this date as a date into range(a2) so I can then use the filter function to filter by month or year. THe problem I am having at the mo is that in the userform it is formatted as dd mm yy, but when I export it from userform it changes to mm,dd,yy. So if I entered 12th January 2014 it changes it to 1st December 2014.
A workaround is to export as text but then I use the filter by month year function so would prefer to avoid this.
View 1 Replies
View Related
Dec 19, 2012
I work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.
View 2 Replies
View Related
Jan 10, 2013
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Ive also attached the worksheet
View 1 Replies
View Related
