In MS Excel, I find that when the zoom is dropped to 50% the fonts in the
list become unreadable. Is there anyway that the fonts in the list have the
ability to have their own formats?
In the attached excel sheet you can find 2 subheadings named Safety and Waiting. Like this i have many sub headings in my excel sheets. Each sub heading contains some rows. The below is the code i used to change the Font color based on the date values.
Every thing works fine. My requirement is if any rows added under subheading "Safety" then i want to run the loop from 1 st row to last row under this subheading which will change the color based on the given date.
How to identify under which subheading the row is added and which is first row and which is last row.
Sub CycleThrough()
Dim Counter As Integer
If ActiveCell.Column = 3 And ActiveCell.Row >= 3 And ActiveCell.Row <= 8 Then
For Counter = 3 To 8
If IsDate(Worksheets("Sheet1").Cells(Counter, 3).Value) = True Then
If Date - Worksheets("Sheet1").Cells(Counter, 3).Value >= 100 Then
Worksheets("Sheet1").Cells(Counter, 3).Font.Color = 255
ElseIf (Date - Worksheets("Sheet1").Cells(Counter, 3).Value >= 70) And (Date - Worksheets("Sheet1").Cells(Counter, 3).Value <= 100) Then
Worksheets("Sheet1").Cells(Counter, 3).Font.Color = 82400
ElseIf Date - Worksheets("Sheet1").Cells(Counter, 3).Value < 70 Then.......................................
On a worksheet I want 4 cells to have validation, when a selection in the first cell is made this limits the choice in the second, when the second is selected then the third is limited by 1 and 2 and the forth will be limited by 1,2, and 3.
In the attached example data , if I choose a Region, I have a choice of Countries. When I choose a Country I am limited to valid Cities and when I have chosen a City then a list of applicable Agents will be available. I have used NamedRanges for a two dimensional lookup but for four and my list will be far bigger than the sample, I do not fancy maintaining numerous NamedRanges.
I need help urgently for parsing a text file to have a specific format. The text file is of format mentioned below: ...
View 9 Replies View RelatedI have a scenario where a cell has to be restricted based on the user input of another cell, also it should allow only Yes or No values.
ex:column A
column B
pending response
submitted
User should be allowed to enter a value in column B as "yes" or "No" only when column A has a value "Submitted"
I referred the thread to restrict entering the data [URL]...
However not able to add list as well.
I have created a Data Validation list in one of my spreadsheets which works fine. The list itself has no duplicates which is fine but when I select my entries in the other spreadsheet, I want to make sure they cannot select the same value twice.
I tried selecting "List" in data validation and using an AND with COUNTIF but it doesn't work.
=AND(Employees, countif($A$1:$A$20,A1)=1)
I like to use the combo box (Active X control). where I need to provide the range/List in the properties. While inputing the value in properties it is disappeared.
View 1 Replies View RelatedI am working with a class module and some fields require specific inputs that another coder may not intuitively know. How can I create a list of acceptable conastants that will appear as a drop down when the user is coding with the custom object?
For example:
I am creating an Email Engine object that will handle all my emailing needs in code, because coding emails is such a pain. Then I can just create the blasted thing, import the class module where needed and never have to worry about it again. Some coworkers are interested in using the module for their projects when I am finished. I want to put an option for message flags in the object, but only certain colored flags are allowed. How can I get the VBE to show a little drop down with the acceptable flag constants when another user types
[Object Variable].MessageFlagColor = {drop down list of options}
I started with
Public Property Let MessageFlagColor(Value As Constants)
End Property
But that hasn't gotten me anywhere.
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
on VBA in regards validation rules in Excel?
View 9 Replies View RelatedThe thread title is the entire email message I got from a customer (thru the help desk). I have sent back asking her the following:
Is this happening in all excel documents you try or only some? Are you getting any sort of error message? Can you send me an Excel document you are having trouble with and tell me the exact actions you take?
While I am waiting for her answers, I thought I would ask here if anybody knew:What would cause this to happen?What sort of things I should look for?What other questions I could ask her?At the moment I do not know if she was in an Excel workbook that had vba code behind it or not. So my questions are for both what could be in vba code and what she could have done (without knowing) to cause this phenomenon
On a tab, i have one column where information is entered. what should be entered is based on a dropdown list (which i have setup on a validation). the other thing that should be allowed to be entered is a an 8 digit number. when something else is entered, i need to have a popup message appear to tell the user to fix the error.
I have no idea how to effectively enter in the restriction in VBA for this.
Below is the VBA i have started:
1. The problem is that if one of the drop-downs is chosen, then i get this error message (because the dropdown list has text that is more than 8 characters in length).
2. The one other problem is that if the cell is left blank, i still receive the popup message. how do i avoid this from showing up.
3. I am also looking to do something like this for a range of cells (from F5:F100).
Private Sub Worksheet_Change()
Dim stue As String
stue = "f5"
Application.EnableEvents = True
If Len(Range("f5")) 8 Then
MsgBox "there is an error in cell " & stue & " "
End If
End Sub
how to create two buttons so that I can have a data entry specific protection and then a lock all button. Here's the link to the thread:
http://www.excelforum.com/excel-prog...-a-button.html
And here's the code I'm currently using:
I wanted to stop users from using cut and paste as well as drag and drop so I found something in Ozgrid to do that. (http://www.ozgrid.com/VBA/disable-cut-copy.htm)
Problem now is I am trying to run a macro that will copy and paste certain values, but because of the code I have added in the above link, whenever I click on a cell and copy it, when I click any other cell, the copy area gets cleared which of course disallows me from pasting it.
Private Sub Worksheet_Activate()
Dim oCtrl As Office.CommandBarControl
With Application
.CopyObjectsWithCells = True
.CellDragAndDrop = True
.CutCopyMode = True 'Clear clipboard
End With
'Enable all Cut menus
For Each oCtrl In Application. CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl.....................
I need to edit multiple validations in a worksheet. There are over 990 validation for each month. I want to change the word in the sentence. Example: April to May. Right now I have to edit each one seperatly. I have tried a macro but it changes to whole sentence. Find and replace do not allow editing in a validation.
View 9 Replies View RelatedI have an excel spread sheet, Col I is a Date, Col K is a Date, Col P is a date and Col T is a text(with list validation) I have validation that if if T = "As is" and P <> I or K (which ever one has a value) then Error. Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=IF(T2= ""Accept as is"",(IF(P2=(IF(K2<>"""",K2,I2)),TRUE, FALSE)), TRUE)"
But if the user changes T to some other value then it is ok to change P, But they could put any value in that field. I Need to validate that P is a DATE also
I was trying to do a validation Greater than > 01/01/2007 but I can not do multiple validations on the same cell. How can I validate P is a date also
I'm trying to get one more feature added to this priority queue tool that a forum member created. Essentially, this attached spreadsheet is a dynamic task/priority list that will change on the fly. See the original thread here: [URL]
The only missing feature is the ability to delete an entry and have all other entries below it change up a rank.
The attached spreadsheet is the almost completed solution
Example:
Original
-----------
Item D - 1
Item B - 2
Item E - 3
Item A - 4
Item C - 5
Remove item B
-----------
Item D - 1
Item E - 2
Item A - 3
Item C - 4
if it is possible to have data validations on a cell within Excel but allow a user to input a formula? The data validation is a decimal between 0 and 1 and the formula would be a vlookup.
View 4 Replies View RelatedI have a workbook that has a macro that needs to run before it is closed. I have a button on my sheet that runs that macro and then closes the sheet. Is there a way that I can disable the "X" at the top right hand of the workbook and force the user to close the workbook via my button?
View 3 Replies View RelatedI've created a worksheet with a number of dropdowns / data validations. Now the thing with these validations is the source range apparently has to be in the same sheet (well it can be another one but much simpler if in the same). I've just realized I created them in the wrong place, so need to erase them. Pressing the delete key isnt working though, they're still there. I could right-click delete but that then requires one to move cells up/right etc and that could mess with my formulas.
View 7 Replies View RelatedI am using a modified version of Debra Dalgleish's filtercopy macro in which 3 data validations are being used (she used 1 in her downloadable example ProductList. zip) and it works just fine when the validations are used in order as they should be. However, in looking over the data I am trying to present in my report I think it would be very valuable to use the first and third validations at times (skipping the 2nd) to discern information in that way. Long story short, I can't seem to get this to work and don't know if it's even possible because I'm not a VBA expert.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 3 And Target.Column = 4 Then
Worksheets("StoreData").Range("O2").Calculate
Worksheets("StoreData").Range("Database") _
. AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("StoreData").Range("O1:O2"), _
CopyToRange:=Range("A6:K6"), Unique:=False
End If
If Target.Row = 3 And Target.Column = 6 Then...............................
I have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.
View 2 Replies View RelatedI have very limited VBA skills and I have tried to get a vlookup working on a userform. I need data from a range selected in ComboBox1 to drop into Textbox1. I have tried but it doesn't work.
I also need if possible a button that pushes the result of TextBox1 to a cell and another button that allows the user to edit the TextBox1 value in situ.
I am using a spreadsheet for a consignment sales program. I took over the spreadsheet already in use but want to improve on it.
Here are the important cells:
=(SUM(E2:M2)-SUM(N2:Q2))*1.35 This gives us the tentative order for 2013. First sum is amount sent, second is amount returned. The 1.35 is our built in sales expansion.
=ROUND(T2,0) Rounds the above result
Now I need to have a minimum of 5, so anything for tentative 1-5 would be a 5, and there are some negatives under tentative, need those to read 0. Can I get both of these to happen in the round column of step 2 above?
I have a simple bike log that lists out each day of the month in rows with a summary row between months. Like this...
January Summary
Jan 1
Jan 2
Jan 3
....
Jan 31
February Summary
Feb 1
Feb 2
Feb 3
....
Feb 28
and so on. Each column has a data field where I enter in miles and distance and so on. I frequently add rows if I do two rides in a day so two rows may start with "January 23rd". I would like to have a chart that shows me my weekly ride summary. But I have some issues:
1. With the field summary I don't know the best way to do a chart that doesn't include the summary (the summary data would throw the chart way off)
2. If I have multiple rides in a day I don't know how to make the week include those days
3. I may want the week to start on Monday and not Sunday. Is it possible to have the user choose and have the chart change automatically?
I have a text box. I want it to pull the data from Cell B2 and list it in the text box. The problem is that I want to be able to change the data if the user clieck inside the box and changes it.
View 3 Replies View RelatedIn my table there is something wrong in array formula so that I'm not getting correct output in the table.
View 14 Replies View RelatedI have created a data entry spreadsheet for staff.
I need to format all data entry field to uppercase.
I'm trying to have it output certain symbols such as "Ó" and I can't quite figure out how to do it, i want it to basically write in the selected cell:
"Ó of " & a & "'s = " & b
where a and b are strings... my problem is i cant figure out how to have it output the sigma but keep the letters from going into symbol or windings...
I have a chart graphing different series, which includes a legend to label each series. The labels are linked to particular cells in the worksheet.
Most of the chart has Arial font formatting, but I need to change the formatting of a few characters in the legend:
I know that i can change the formatting of the whole legend, and even different entries in the legend. Is it possible to change the font of just a couple of characters in one legend entry?
Elements I need to change are:
Font type (eg from arial to symbol)
Size (varying from 8 to 14)
Bolding etc.