Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Conditional Grey-out And Disabling Of Cells


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Conditional Formatting: Cells To Color A1 Grey (Fill)
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 Replies!   View Related
Conditional Disabling Of ActiveX Controls
I want to do disable certain ActiveX toggles depending on the value of a specific cell; this cell can contain more than one value that should disable that control. the logic works as follows:

IF Cell="A" THEN Toggle 1=Disabled AND Toggle 2=Disabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="B" THEN Toggle 1=Ensabled AND Toggle 2=Enabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="C" THEN Toggle 1=Enabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Disabled AND Toggle 5-Disabled

IF Cell="D" OR Cell="E" THEN Toggle 1=Disabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Enabled AND Toggle 5-Enabled


View Replies!   View Related
Grey Out Worksheet
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.


View Replies!   View Related
Grey Scale Text
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?

View Replies!   View Related
Disable Grey Out Ribbon Items
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.

View Replies!   View Related
Form Control To Grey Out Unused Rows
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 Replies!   View Related
Blank Or Grey Area In The Left Of The Screen
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 Replies!   View Related
Disabling A COM Add-In
Is there a way using VBA that will allow me to disable a COM Add-in when I open a workbook without me (or the user) going through the steps to remove it first.

The reason for my asking is this: We have installed SAS Add-in 2.1 to Excel 2007. So this brings up SAS under the Add-Ins tab. But then when my program executes and it sees I use command bars, it adds my command bars under the SAS menu. I am trying to make the interface as clean as possible for the user, so they only see my command bars or menus.

Also, if we were to install the SAS Add-in 4.2 to Excel 2007, I have read in a SAS paper that SAS Add-in 4.2 creates its owe tab on the ribbon instead of inside the Add-in tab. So I would be able to use xml to make the SAS tab visible="false".

View Replies!   View Related
Disabling Alt+f11
Is there a way to disable alt+f11

I will only disable it if the file is opened as read only
and I will enable it on before_close.

View Replies!   View Related
Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View Replies!   View Related
Disabling 'mailto'
I am just using a simple sheet to keep track of email addresses. Every time I enter an email address it automatically creates it as a hyperlink/mailto function. So if I click on the cell, it will create a new email via outlook addressed to that person.

This is driving me nuts! I have a few thousand emails, mostly copied from other people's sheets, and 90% of them are set like this. Is there a fast way I can disable this function so that every cell is just text?

View Replies!   View Related
Disabling Functions
is there any way of disabling the following functions:

adjusting the size of the horizontal scroll bar;
being able to scroll to the right to search for tabs covered by the scroll bar

the reason being I want to have a few sheet tabs hidden but still be able to see the data on the sheet if a button taking you to the sheet is clicked

View Replies!   View Related
Disabling The F9 Calculation Key
Private Sub cmdCalculate_Click()
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Is there a way to disable the F9 Calc key and get a user to hit this command button?
Right now this code does not work. I do have Calculation set to Manual so it is disabled in the worksheet.

View Replies!   View Related
Disabling Macro When Condition Met
I have recorded a simple macro that refreshes a set of random data in my sheet everytime I click on the button I made.
When I see a zero in one of the cells I know to stop refreshing. The thing is, I click on the button quite rapidly and sometimes miss the zero. I need to click on this button quite quickly so slowing down isnt an option!
What I need is for excel to stop that button operation when it sees a zero in one of the cells.


View Replies!   View Related
Disabling Auto Calculate For Others?
I'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Work-related), so auto-calculate is casuing crashing, so what I've done with this sheet is disabled auto-calculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess).

My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?

View Replies!   View Related
Disabling Copy Function
Is there anyway in excel to make it so that you cannot copy the data off of the spreadsheet, but the user can still do functions, more specifically filters and such.

I've been asked to put it in PDF format, but my spreadsheet becomes useless without the use of filters. It's a check for the copyrights of my company.

View Replies!   View Related
Disabling Macros Not Working
I have a workbook which adds command bar named "Upload Processing" to Excel application if you open it.

The weirdest thing is, it hasn't got any macro stored anywhere or at least I can't see it anywhere (Alt+F11).

Note: Don't forget to use application.commandbars("Upload Processing").delete command from VBEditor to get rid of this newly added command bar when you are trialing.

View Replies!   View Related
Disabling Frames By Content
I have a UserForm with lots of controls divided by multiple frames. (163 controls over 19 frames). The same UserForm will be collecting multiple sets of data but the first set is a "master" set. Anything entered in the master set will auto fill in subsequent sets.

What I need is a way of preventing the user from changing any options that were input in the master set in subsequent sets. ie, If I entered info into Frame1 of the master set it would show up the next time I opened the UserForm (I have this part), but I would not be able to interact with it (this is what I need).

Since I'm already pre-populating subsequent sets with the master info, my first thought is to loop through each frame testing for input and if so setting Enabled = False, but I'm not sure how to do the loop/test. Is there a way to set up a "For Each Frame in UserForm" statement? Also, the controls in each Frame could be TextBox, OptionButton, and/or CheckBox. (sometimes more than one type in the same Frame)

View Replies!   View Related
Disabling Scrolling In 2007
I'm looking to disable the horizontal and vertical scrolling in 1 worksheet

Am i able to do this?

View Replies!   View Related
Disabling Of Workbook Maximization
How to disable by code Maximization of Workbook?

View Replies!   View Related
Disabling Control Causes Crash
I'm working with Excel 2000 on Windows XP machines. I have the following

Private Sub soInputBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
PushScreenUpdateState False
clearAllData
loadAllData
Worksheets("Main Screen").Activate
PopScreenUpdateState
End If
End Sub

Private Sub loadAllData()
soInputBox.Enabled = False
End Sub

The idea is this: the user can either type the value into the text box (soInputBox) and then click a button, or type the value into the box and then hit enter. They will both do the same thing; set in motion all the functions that load a bunch of data, etc.The code for the event handler for hitting enter is shown above. Inside of the loadAllData function, I disable the text box. What seems to be happening is that after the soInputBox_KeyDown function exits, Excel crashes. If I comment out the line to disable the text box, Excel doesn't crash. My question is this: is this a known problem, and is there a known workaround? I would like it so that the text box is disabled so that the value that was input cannot be changed accidentally during the rest of the programs execution, so if there's a way to change the control to be unmodifiable instead of disabling it that would be a reasonable alternative.

View Replies!   View Related
Disabling Arrow Keys For InputBox
I noticed the arrow keys will move the active cell while I have an inputbox active. This will be an issue for my users, I need them to key in data only.

Is there a command that will disable the arrow keys while the users enter the requested data?

View Replies!   View Related
Disabling Macros Will Close Workbook
First of I have a workbook, that runs several macros. All works fine. However I need to better secure the workbook. I realize that there is no guarantee on the security with excel but am hoping for something to solve the problem stated below.

When a user (with high security settings) opens the workbook in question, I would like the workbook to CLOSE out if the user chooses to disable macros when the asked by excel.

View Replies!   View Related
Disabling Private Sub Macro Temporarily
How do disable Private Sub Macro

I have a private sub macro for Sheet1 as shown below

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$2$" Then Sheets("Start").Select
Exit Sub
End Sub

And I have another macro (call ADDNumLine) that add additional data to the Sheet1. How do I temporally disable the Private Sub above when executing Macro AddNumLine?

View Replies!   View Related
Disabling A Macro If The Outcome Of It Already Exists
I have a macro implemented in my worksheet which plots graphs. When you press it, it plots a graph.

What i would like it to also do is:
(a) create a tab name which states Graph_1.45 {last bit is whatever the name of the data worksheet is}. - so for example i will eventually have 20 worksheets of data of the same form as the attachment. Each will have the option of the graph. So each will haveits own name, by pressing the graph button the name thatis given is Graph_"whatever the tab sheet of data's name is".

(b) Now the problem is if a user forgets to delete the worksheet it comes up with an error. Is there a way by which if the graph already exists the macro comes up with a message "Graph already exists" and gives an option to delete that graph and plot nbew one?

View Replies!   View Related
Disabling Pivot Table Features
I am using the following code to restrict what users are able to do with a Pivot Table I have designed:

Sub Workbook_Open()

Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
For Each pf In .PivotFields
With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
Next pf
End With

End Sub

However, I am getting a Run-time error with the 'For Each pf In .PivotFields' section after it has looped through all the Fields in my Pivot Table.

View Replies!   View Related
Disabling Delete Sheet From Menu
How do I diable the "Delete Sheet" so user can not delete any sheets

View Replies!   View Related
Disabling End Statement During OnChange Event
I am writing an XLA in XL2002 which will act on a number of legacy and new spreadsheets. During the execution of the XLA I set cell values.

i.e. m_rRange.Cells(j, i).Value = m_sInVal(j, matchIndex)

This then triggers the onChange event of the worksheet. Unfortunately, for one of the spreadsheets that this needs to work with, this causes an error. The error is trapped within the spreadsheet itself but the sub End is called which halts all VBA code execution.

My problem is that the XLA has not finished working and gets terminated early. I cannot capture this using an onError statement as it is never triggered (the spreadsheet code stops the execution and control never returns to the XLA).

I can disable the events using Application.EnableEvents but this means that none of the onChange events are called which is also not desirable.

So my question is: is there any way of disabling the termination of code using the End() statement, or of detecting when it happens to stop it?

View Replies!   View Related
Temporary Disabling Of Workbook Event
I have a workbook change event that when fired checks criteria to see that the sheet is complete. If not complete, the user must complete information on the sheet before continuing. The problem is that some of the "stuff" the user must do requires macros that fire and gain access to other sheets. Thus the "trap" I have set to restrict the user to just the one sheet has backfired on me restricting the user to just the one sheet. Is there a way that the workbook change event (or any other workbook event) be temporarily disabled until activated again?

View Replies!   View Related
Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

View Replies!   View Related
Disabling The Control Drag And Fill Function
the function I'm referring to is when you have a value in a cell and you hover your mouse cursor over the edge and get the black cross with arrows on all corners. Once you drag the cell into another location, it copies the the data into the other cell.

I need a way to stop this from happening, because this causes reference errors on other sheets when users do this by accident. However, I still need to allow the user to be able to type in that cell, use the autofill option, and the Ctrl+C option. Is there a way to program a macro that can be put in the sheets to do this for specified cells or ranges?

View Replies!   View Related
Deactivate Or "grey-out" Commandbutton
I was wondering if there is any way to "grey-out" a commandbutton? I made it using the forms toolbar.

View Replies!   View Related
Conditional Hiding Of Cells
i want to hide columns based on values of particular cell in that column.

View Replies!   View Related
Conditional Locking Of Cells
Lock cells based on conditions? For example, in my spreadsheet I need my cell J42 to stay locked at all times unless my cell C12 reads "Klongtoey".

View Replies!   View Related
Conditional Based On Other Cells
what would I do if I want cell B:1 to be formatted a certain way if the value in A:1 is a certain value?

View Replies!   View Related
Conditional Locking Of Cells ..
I have a condition whereby if cell C7 has a value entered, then cells D7 & E7 cannot have values entered. Like wise, if cells D7 & E7 have values, them cell C7 cannot. Cell F7 would be the product of the calculations using the data either in only C7 or only in D7 & E7. Please see the sample attached worksheet with my problem elaborated.

View Replies!   View Related
Conditional Difference Between Two Cells
I have to calculate the difference of two cells in an excel sheet if the first cell value is greater than the second one. If the first cell value is smaller than the second one then the difference should be shown as zero.

View Replies!   View Related
Conditional Unlocking Of Cells
I have had a look around and found some answers to this question but not quite as complex and I don't know enough to adapt them correctly.

Basically AX35:AX239 contain a formula which returns "TRUE" or "FALSE" dependant on certain values in the row, what I need is for the corresponding H, I & J cells to be unlocked on each row if the outcome is "TRUE" and no action if "FALSE".

View Replies!   View Related
Conditional Minimum Value Of Various Cells
I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C = "driver",
then corresponding salary to be included in the data to be analysed.)


View Replies!   View Related
Conditional Formatting Of Cells
I want to add some colour conditional formatting to the cells in column C, dependent on their variance to the figure in column B. I've got the basic gist of applying the rule to the one single cell, but I'm not sure how to apply it to all the cells in the column. I have tried "dragging" it down, but it then just gives me the variance to the original row.

View Replies!   View Related
Conditional Locking Of Cells- More Than One Sub?
I am trying to create a form that has multiple drop downs, which are dependent on answers from other drop-downs. I'm trying to make it super user friendly and have the cells lock, so that the user can tab through only the items that they need.

I have it almost down, but I'm not sure how to do it for multiple cells. The closest I come is one big long if,else statement which runs through the whole thing on every cell change.

I have an example of the spreadsheet attached.
If F1 says "Other", I want H1 to be unlocked
If F2 says "Y", I want F3 and F4 to be unlocked
IF F4 says "Other", I want H4 to be unlocked

View Replies!   View Related
Conditional Formatting In Multiple Cells
I am having a problem setting conditional formatting for multiple cells.
The first condition of the conditional formatting works for the cells but the second condition does not. There is probably an easy fix for this but I can't seem to find it.

View Replies!   View Related
Copy Down Cells After Conditional Format
I conditionally formated a spreadsheet to delete all repeats. Now I have my rows numbered 1, 2, 3, 4, 9 15, 20, 26, 36 (no specific order because the number of repeats were random). I inserted a new column (A) and want a running set of numbers (1,2,3,4) starting at A4. The only problem is that when I put a 1 in the A4 box and try to drag down in the bottom right corner of the cell with CTL+Click, it copies the number 1 all of the way down. What do I do?


View Replies!   View Related
Conditional Clear Contents Of Cells
I have a dropdown list in the range E17:E91 containing a list, and two of the values in the list are "DI" and "LTC". What I need to happen is if either of these are selected, the corresponding cell in column F needs to have it's contents cleared. Ex. if in E17 the value is ever either DI or LTC, cell F17 gets contents cleared. if column E contains ANYTHING ELSE, nothing happens. That's all, I've taken care of conditional formatting and data validation already. So even if data is pasted in the worksheet into column E it needs to recognize that and always act in real time.

View Replies!   View Related
Find All Cells Having Conditional Formatting
I need to find all cells within a range that have conditional formatting where that formating is:
Formula Is =(cell)="" where (cell) is the cell with the formatting.

As an unpreferred second choice...find all cells within a range that have conditional formatting.

View Replies!   View Related
Conditional Formatting (format All Cells)
I have two colums A and B with a lot of data and want to use Conditional Formatting:

Cell Value Is
equal to
=$A$2 $A$352

What formula do I have to use to format all cells (colored) in the colum B with any existing value in between A2 and A352.

THE VALUES ARE WORDS


View Replies!   View Related
Conditional Formatting : Highlight THE CELLS
I need to hightlight from A1:A18

In the above cells I have values from 1 to 4

So I need to fill the cell with 4 different colours.

If cell equals 1 = Green
If cell equals 2 = Yellow
If cell equals 3 = Red
If cell equals 4 = Blue

What I do is highlight cell from A1:A18, clik on 'Format', 'Conditional Formatting', I've tried using 'Cells Value is' and 'Formula is'.


View Replies!   View Related
Conditional Formating For Multiple Cells Using VBA
i have a list of customers with various information, i would like to rank these customers 1 to 5 and change the colour of the cells dependant on there rank.

Conditional formatting allows me too do this but is limited to 3 formats.

I have Columns A-K filled with data and want the condition to be set on column B if Column B meets a requirement it will then colour that Row (A-K)

So for example Row 3:

B3 = the requirement to change colour to red so cells A3:K3 will turn red
B4 = the requirement to change colour to gree so cells A4:K4 will turn red

and this must go down to row 1000 or whatever it may be.

View Replies!   View Related
Conditional Formate VBA Based On Other Cells
I am trying to convert the following macro to a Worksheet (section change) format, but cannot figure out how to have the cells B87:M150 change based upon the text (red, green, white, amber, na) in O87:o150.

Sub ordinate()
Dim r As Range
For Each r In Range("o87:o150")

If r.Value = "RED" Then
r.EntireRow.Interior.ColorIndex = 3

ElseIf r.Value = "AMBER" Then
r.EntireRow.Interior.ColorIndex = 44

ElseIf r.Value = "WHITE" Then
r.EntireRow.Interior.ColorIndex = 2

ElseIf r.Value = "GREEN" Then
r.EntireRow.Interior.ColorIndex = 4

ElseIf r.Value = "NONE" Then
r.EntireRow.Interior.ColorIndex = 2

ElseIf r.Value = "NA" Then
r.EntireRow.Interior.ColorIndex = 15
End If
Next

Range("N86:IV132").Interior.ColorIndex = 2
Range("A86:A132").Interior.ColorIndex = 2
End Sub

View Replies!   View Related
Conditional Formatting With Reference To Different Cells
As shown in the image given below, I have 2 tables. In Image 1, Vacation details are being fetched from a different worksheets. There can be 13 possible values in a particular cell. These 13 entries are,

V
SL
CO
T
PH
W
ML
PL
AD
BE
CTW
EM
LOP
MAR
OTR

Excel Jeanie HTMLSheet2 *BCDEFGHIJKLMNOP2Feb-09*Feb-093MonTueWedThuFriSatSun*MonTueWedThuFriSatSun4**************15VSLCOTPH***23456786WMLPLADBE***91011121314157EMLOPMAROTR****161718192021228CTW*******232425262728*9*************** Excel tables to the web >> Excel Jeanie HTML 4

WHAT I NEED :

If the value in cell B5 is V then J5 is being colored with PINK background. Similarly if any cell in Image 1 has value SL ( for e.g. C5 ) then K5 is being colored with orange. Similarly there should be 13 different types of color coding for all the above values which I mentioned.

WHY I NEED THIS ?

As you can see in Image 1, If I apply these colors to Table 1 only, then the user will not be able to see the value V or SL or any values belong to which date ?

Note - I am doing this for the whole year. But if someone can send me VBA code then I can do it for the rest.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved