VBA code to disable(grey out) condition formatting command in ribbon. Because I generated a template and do not want user to change the condition formatting.
I have tried below, but it did not work...
Application.CommandBars(1).Controls("Format").Controls("Conditional Formatting...").Enabled = False
For an excel application I need the disable most of the ribbon content i.e. disable insert, formulas tab etc.
The only thing that should function is a hand build menu add-in, which is created with custum UI editor.
I searched via google, this forum and many other places, but i didnt find a clear answer yet.
Essentially, I'm creating a spreadsheet which contains a list of staff in rows, and the months of the year as columns. It is to record certain company checks which must be done (e.g. driving licence check). When a check is complete, the date it was done is entered under the corresponding month. Certain checks (e.g. driving license) only need to be done every 6 months. What I'm trying to do is find a formula/vba to look at each row, determine where (which month) the date was entered in, and grey out the 5 months in front, meaning the next check is required in 6 months time.
Ideally, the frequency of this check is dynamic, and can reference a cell (e.g. might change to every 3 months etc).
I have the code that accompanies the attached spreadsheet model that locks the copy, paste, cut, double click and so on, with the right mouse button and shortcut keys, however, the toolbar excel these functions still remain active. How well I block access to the toolbar.
Desable_Ribbon.xlsm
Below are the codes to hide the objects within Excel, Disable Keyboard shortcuts and disable right click, all are placed in the ThisWorkbook and i placed them in a 'Private Sub Workbook_Open()' function.
VB:
'Change 'False' to 'True' to unhide
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
[Code]....
There maybe simpler ways to do the above i just don't know them, it's just these have worked for me and i have had zero bugs come back from them!
I have information in A1. I want A1 to look at B2:B5 and D2:D5 (Under conditional formatting) and if there is one or more "X" in those cells to color A1 Grey (Fill). I keep trying to put a formula under conditional formatting, but it is only working for the first cell (B2) and not the rest.
View 2 Replies View RelatedI'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
Conditional formatting image.png
Scenario: A & B Columns have an existing Conditional formatting:
Problem: If I place a text, number or date on any cell in Column C. The existing Conditional formatting in the A & B "ROWs" will be disabled.
how can we disable/enable a cell based on a condition which is dependent on other cell data
View 4 Replies View RelatedI have the following code which prints the number of time steps required and displays it as t0, t1, t2 etc how can I make all the numbers subscript? i.e until the number of time steps = the total time.
View 2 Replies View RelatedI would like to format a number of cells depending on the percentage of another cell.
I have a cell 'Seats" that has a number in it. In other columns, 'Stop1', 'Stop2', 'Stop3', are vaious numbers. I would like to format the Stop cells according to how close (or over) the number is that is in the 'Seats' cell. For example, if Seats is 30, Stop 1 is 15, it would be a medium red. Stop 2 is 30 (or over), it would be red. If Stop 3 is 5, then white. Basically a percentage scale of color depending on the number in the Seats field.
I have a workbook (column K) set with the following format conditions:-
Cond.1 - =(I3="declined")
Cond. 2 - =(G3>0)
Cond. 3 - =(K3
I have a workbook with 3 sheets.
The underlying VBA is password protected and I have the password.
My problem is there is one sheet that I can see in the VBA side of things but cant see on the excel side
Format/sheet/unhide - suggest there is no sheets hidden
If I right click on the sheets (vba side) View object can be seen
except for the one sheet which remains greyed out
Any ideas why this is?
If I try and goto(F5) a range in this sheet nothing happens
If I use a dropdown validation cell, which looks at this sheet it works, except there appears to be a line missing.
There is a table [A8 TO D60] and a column alongside, where the months of the year are listed. In a particular independent cell, the name of any month can be chosen and entered.
The table rows preceding the chosen month, have to be shaded. When a different month is chosen, the shading should now cover the new set of rows preceding this different month and so on.
I am using the following code that I got from this forum to color tabs. But I can only get it to do two colors. Is there a way to get three conditional colors of tabs?
Private Sub Worksheet_Change(ByVal Target As Range)
With Me
Select Case .Range("D13").Value
Case Is < 20
.Tab.Color = vbGreen
Case Is > 20, Is < 30
.Tab.Color = vbBlue
Case Is > 30, Is < 100
.Tab.Color = vbRed
End Select
End With
End Sub
I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great.
The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2.
I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding.
I now want to count how many of each colour there are in each row to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.
We are trying to print grey scale text. It will only print grey scale when we print in colour and not in black and white?
The images and line fills we are using are printing in grey, just not the text.
We are using excel 2003. Both word and publisher print grey scale?
I would like to change the color of a cell to either green, yellow, or red based on a few conditions. One of the conditions would be based on whether a check box was checked or not.
View 2 Replies View RelatedI'm referring to a post I tried to solve:http://www.excelforum.com/excel-gene...ml#post2227328
NO need to read. Try the attachement instead.
Steps done:
Select Column D
CF function =D1<A1
Select Column A
CF function =A1<D1
If I select cell D3 and look at the CF then I see:
CF: =D1<A1
where I would like to see
CF: =D3<A3
I remember this was the case in <=XL 2003? (now using 2010 beta)
I need to find a way to do conditional formatting and disabling of a cell based on a value from another cell. For instance if A1=X,Y, or Z, then B1 will get grayed out and cell entry will be disabled. This is so that when skimming over the sheet, you can tell what cells in row B you need to put info in still, and if it's grayed you'll know you don't have to put anything there (and it won't let you).
View 14 Replies View RelatedI have a column of numbers that are already formatted as text. Some of the data is duplicate which is fine. It's like a list of 6000 numbers. I want to conditionally format the column so that I can fill the cell with a color as long as it stays the same number, but if it changes to a new number make it a different color. I don't need a lot of colors. Two is fine. TI have Excel 2010.
View 4 Replies View RelatedI am now trying this using Conditional Formatting in Excell 2000; so limited to 3 conditions. However I cannot get it work with multiple conditions:-
Values;-
A34 = Heritage Rly Assn Business Development Committee
A35 = North London Rly Historical Society
C34 = 23/09/2009 (formatted as WEEKDAY DD MMM YYYY)
D34 = 23/09/2009 13:30 (formatted as HH:MM)
In Cell C34
Format condition 1:-
=A34<>A35 , Format Bottom Border
Format condition 2:-
=AND(WEEKDAY(C34)>1,WEEKDAY(C34)<7,HOUR(D34)<18,A34<>A35), Format Bottom Border and Yellow pattern
Format condition 3:-
=AND(WEEKDAY(C34)>1,WEEKDAY(C34)<7,HOUR(D34)<18), FormatYellow pattern
In cell c34 Format condition 1 activates NOT condition 2.
In another cell, C13, condition 3 correctly operates; in this row A13 = A14. Condition 1 works i.e. the cells A1 and A2 are the same only cell A2 has the bottom border and if Cell A2 and A3 are different there is a bottom border in both cells. There must be something wrong with my formula in condition 2 but I can't see what; condition 2 is just condition 1 amd 3 amalgamated.
The condition 2 should read:
If the date is not a Saturday or Sunday in cell C34 and the time is before 18:00 hours in Cell D34 and the name in the "a" cell i.e. A34 does not equal the name in the "a" cell below this one i.e. A35 put in a bottom border and colour the cell yellow.
Date Due
Date Ressolved
5/16/2013
5/17/2013
5/16/2013
5/9/2013
5/9/2013
How do I make this change, If the date resolved column has not data entry, but todays date is past the due date cloumn data entry change cell red and insert text to read "past date due"
Is there a macro that will format unused rows or with no value in a given range. I have a sheet that I am using that some of the date is either added or deleted and would like to have a macro to do grey out unused rows. I am using this with a form control.
View 3 Replies View RelatedI have a spreadsheet with rows of cells which are either blank or have text in them.
Is there an easy way to write a formula which will make the cells which have text in them bold and make the cell colour grey. But if the cell is blank keep the cell white.
I am running into a seemingly random event upon executing a rather long program in Excel VBA: a square area turns gray in the left of the screen in Excel, making invisible everything under it, and affecting every sheet and workbook open. The only way to get rid of it, so far, has been to restart Excel. I thought it pertained to a faulty Office installation (XP/2002), but then it also happened on another machine (with Excel 2003) where I installed the application.
View 4 Replies View RelatedIs it possible to check the conditional state for a specific condition for a specific cell.
For example a cell has 1 or more conditions.
A condition will toggle to a condition if true or false etc.
The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions)
So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.
But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address.
To msg the user where data entry is still required etc.
I am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.
1
1
2
3
4
[code].....
I am using Excel 2002 and have had a couple of files that seem to have become corrupted recently. When I try to open the files there is nothing there, no cells just a grey space where the cells should be. When I try to close Excel it asks me if I want to save changes when no changes have been made.
I had not made any changes to the sheets prior to this happening so am struggling to work out what is going on.
I try to add add ins tab to the ribbon. i checked in customize ribbon. it is not coming.
View 8 Replies View Related