Customized Cell Backgrounds
May 20, 2009
Is there a way to have customized cell backgrounds that are more complex that Excel's preset colors and patterns?. For instance, I have a merged cell (it has to stay merged) with a value that is located at the bottom-center of the merged cell. I want to have either a fading gradient (the top of the cell is white fading to a blue bottom towards the value), or just have the bottom portion of the merged cell colored. I have Excel 2003 so I don't know if any other version has this feature.
View 3 Replies
ADVERTISEMENT
Aug 18, 2004
i need to change the background of the worksheet, yes i can do it using the tools bar but it does it to the whole sheet i just want to do it to a slected area. Problem is if i then import it as a pic or object it does not go behind the text on the work sheet or the combo boxes, can any one suggest a way forward. The file is then to large when the whole work sheet is used
View 9 Replies
View Related
Sep 20, 2013
I have a sheet where some data is entered in A1 to C1 and i use concatenate to string them together. The problem is in cell C1, where I used cell format to customize the number of zeros in front of the number.
EG:
A1: Photos
B1: 2013
C1: 00001
the concatenate result is Photos2013\1, instead of the desired result of Photos2013\0001.
View 3 Replies
View Related
Apr 17, 2009
Having searched and read lots of posts, but without finding the answer.
Is it possible to creat a macro that toggles a cell between [empty], [x], [o] and [n/a], with the [x] cell having a background colour of green, the [o] cell having a background colour of red and the [n/a] with a background of yellow.
The above cells won't have anyother function as they will be used as indicators in a progress chart.
View 12 Replies
View Related
Mar 26, 2007
I need to have all cells locked and protected, but I need to be able to select all cells in column A only. With 2003 I can chose to be able to select locked cells in the Protection Properties, but that allows at the same time to select all cells and I want a property to allow selection in column A only.
View 9 Replies
View Related
Nov 14, 2008
I have an txtBox in a userForm. I need the value of the userform in a customized format i.e. the user will be able to enter only 5 digit numbers and after the value has been entered the txtBox should dispaly the value with prefix "A".
For example:
1. If user enters values as 56, then txtBox should display as A00056.
2. If user enters values as 12346, then txtBox should display as A12346.
View 9 Replies
View Related
Nov 20, 2012
I have a piece of code from a form command button. its supposed to colect data and put it to appropriate cells.
VB:
FoundColumn = 0
For Each c In Sheet19.Range("A5:A33").Cells
If c.Value = student1.Value Then
'check if date allready exists
For Each cc In Sheet19.Range("c4:nc4").Cells
If Calendar1.Value = cc.Value Then
[Code] .....
I need this for Sheet19 and to 10 more worksheets like Sheet20, Sheet21 etc. I can manually copy paste the code and change the Sheet19 to whatever but it just does not seem the right thing to do. I tried:
VB:
For i = 1 To 6
naming = "Sheet" & i
MsgBox naming.Cells(1, 2).Value
Next i
but that gives an object required error.
View 2 Replies
View Related
Apr 14, 2009
I have a table
item_desc
a
b
c
a
b
c
a
b
c
As you can see, a,b and c is repeated times.
How do you make it so that it would look like
item_desc
a
b
c
item_desc2
a
b
c
item_desc3
a
b
c
Is there a shortcut in doing this instead of copy pasting it?
I have a record just like this with about 572+ rows and 13 repeating items with that 13 items having their own column so copy pasting is really a tedious task. Kindly please help me solve this?
View 14 Replies
View Related
Aug 23, 2013
I'm making a task sheet for my employees and would like them to see the option in the ribbon automatically that allows you to add a picture. I have made my custom ribbon with the "picture" command right under the Home tab. I want to email my workbook as an attachment to my employees and have that picture button right there in their ribbon when they open up the attachment. Is this possible?
View 3 Replies
View Related
Jan 21, 2010
I have created a custom submenu in Excel that uses the "SheetBeforeRightClick" event in the "ThisWorkbook" object to initialize. In the custom submenu, I want to use one of the choices to load a form that I have created. I am trying to do this directly when selecting the submenu entry, by using "MyForm.Show", but this doesn't work. As a workaround I use an intermediary subroutine, "Sub Load_MyForm" where I put the same command, and then it works! Anyone who knows how to load the form directly from the submenu click event without going through an intermediary subroutine?
The workaround (which works) in "ThisWorkbook":
View 9 Replies
View Related
Feb 17, 2012
I have been using a scroll bar in my project to select a value from 0 - 3
Is it possible to have a customised slider (whether a form control or something created from scratch) whereby the numbers 0 1 2 3 are displayed in a grey, red, yellow and green box respectively adjacent to eachother and a slider can be moved over the top to select a value?
View 7 Replies
View Related
Apr 1, 2008
I have a workbook.
I would like to automate with code the process of extracting one worksheet from that workbook, and saving it as a separate workbook, with a file name equal to the text value of a cell (date formatted dd-mm-yy) from within that workbook. (ie d1="10/07/08" save file with one worksheet 10-07-08.xls)
View 9 Replies
View Related
Aug 30, 2006
I wrote my function as it is shown below:
Function MyFunction1(r, n, xrange, yrange)
If r <= n Then
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i)
Next
Else
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i) * yrange(i)
Next
End If
End Function
This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})
However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............
View 3 Replies
View Related
Mar 5, 2008
so i'm building an application that'll allow users to manipulate records in Excel with just a GUI, using Userforms, Modules and Class Modules. it's all working but i'm feeling like i skipped a little on structuring it properly.
for example (from my Java work in college), you'd call just one object from the main method, which would create a GUI object, and create/manipulate instances of the different Classes when buttons are pushed. basically you one object whose main created other objects, who ran procedures, etc. what i'm hoping for is to make it as modular and easy to maintain as possible. would anyone have a good resource for optimising a medium-sized application? (the tips Excel/VBA Golden Rules. These Should NOT Be Optional were very good, by the way.)
View 3 Replies
View Related
Apr 11, 2013
1.On Man' work sheet I Have a Project No (actual one is a big one) and project code,i have made the project code because it cannot be entered on the time sheet as the original project no is very big and i have legends,then Empl nos and OT Rates
Now on the time sheet if i enter the Project Code A to C it should be counted as "P" present for work, rest as usual.
2.On Summary' work sheet Columns D to J should calculate it automatically based on employee no or name and Project No.
View 6 Replies
View Related
Apr 25, 2013
I have created a spreadsheet that requires the buttons on a customised tab/ribbon in order to use it.
If this is on my machine where I have customised the ribbon it works fine. However I need to email this spreadsheet to another person - but wanted them also to have my customised ribbon. Is there anyway to do this - or would I have to go and customise their ribbon on their machine?
View 1 Replies
View Related
Jan 10, 2012
I'm trying to work with a user form with a customized SUM button.
What I want is that the user can select a range and then press SUM and it will sum (and store) the range and then the user can click another button to place the sum in any other cell.
Here is the code I have on the SUM button but it fails
Code:
Private Sub CommandButton1_Click()
MsgBox (Application.Sum(ActiveWorkbook.ActiveSheet.Selected))
End Sub
I'm using a userform1.show vbmodeless so the user can click off the form. I tested it being able to work cross workbooks and sheets and I could see where it was returning the activeworkbook/worksheet but I don't understand the range/selected part.
Excel 2007
View 6 Replies
View Related
Aug 16, 2013
I'm using the following code to create a customized userform, and it works perfectly on my computer, as I've enabled all macro settings.
Code:
Function GetOption(OpArray, Default, Title)
Dim TempForm 'As VBComponent
Dim NewCheckBox As MSForms.CheckBox
Dim NewLabel As MSForms.Label
Dim NewCommandButton1 As MSForms.CommandButton
[Code] .......
However when my coworkers need to use the code it creates an error, as the VBA project is unsafe.
Now my question is, can you write some code that enables all macro settings temporarily, in such a way that my coworkers can use this code?
View 4 Replies
View Related
Jul 4, 2008
My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.
Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 2008-07-03) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.
This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.
Here is a copy of my formulas:
=IF(AND(F8)=0,""*(IF(AND(F8)
View 9 Replies
View Related
Jun 26, 2008
I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?
View 9 Replies
View Related
Jan 26, 2006
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies
View Related
Aug 16, 2013
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
View 4 Replies
View Related
Dec 12, 2012
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies
View Related
Apr 18, 2013
When I use the mouse pointer to select a cell I can't use the arrow keys to move to another cell while the pointer is over the cell and I can't edit the cell while the pointer is over the cell. If I move the pointer away from the cell then I can move around and edit as normal therefore I don't think this is a scroll lock issue.
This issue also happens when I select a tab. If I select a tab and then leave the pointer over the tab I selected then I can't use the arrow keys to move around the worksheet or edit a cell; if I move the pointer away from the cell then I can move around and edit as normal.
I am using MS Excel 2010.
View 1 Replies
View Related
May 6, 2013
I would like to add a date to one cell (say A6) and have this do two things:
#1) this would add "Closed" to a given cell such a A5.
#2) and this would add a color to a group of cells like A1 through A8.
View 4 Replies
View Related
Sep 24, 2013
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
View 2 Replies
View Related
Jun 19, 2008
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.
Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3
Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob
So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week
Example.
Week1
1Jon
2Bob
3Fred
Week2
1FredUp 2
2BobNot Moved
3JonDown 2
Can Excel calculate/show the actual movements of league positions?
View 9 Replies
View Related
Sep 8, 2006
I have three cells in A2:C2 which require user to input some data.
What I want to achieve is to combine the data from A2:C2 in D2.
C2 is a field which user will input the date. He might key in 21/08/06 or
21/08/06, 30/08/06
I have tried using below formulas in D2 but without success.
=A2&" " &B2&" "&(C2)
=A3&" " &B3&" "&DAY(C3)&"/"&MONTH(C3)&"/"&YEAR(C3) (doesn't work if there are 2 dates.
I have attached a file which shows 3 scenarios if user input 1 date and 2 dates.
View 7 Replies
View Related
Apr 26, 2008
I have the following range of cells B2:Y3700. I want that when a value from 1 to 9999 is inserted in one cell of the above mentioned range, to copy the row number of that cell in the cell that is located in column Z and row is the number inserted in that cell. For example, if the number "1234" is inserted in one cell from range B2:Y3700, to enter in cell Z1234 the number of the row where is located the respective cell. At same the time is very important to me to not permit to insert the same number in B2:Y3700, so i believe that is need a macro code to check the cell from column z to see if value already exist .
View 5 Replies
View Related
Mar 25, 2013
formula to copy a cell to a new cell if the previous cell meets the condition.
i have attached an example work book.
so what im looking for is on sheet 1 there is a table and cell G9 shows Apples. on Sheet 2 is a second table where the information is in slightly different rows.
I want to be able to [When Sheet 2 Column C = Sheet 1 Column G Then Paste Sheet 2 Column D into Sheet 1 Column H (next to Apples)]
Then i would like in Cell J9 to look something like [When Sheet 2 Column C = Sheet 1 Column G Then Paste Sheet 2 Column F into Sheet 1 Column J]
and i would like Cell I9 to look something like [When Sheet1 Column J = Sheet 1 Column L Then Paste Sheet 1 Column M into Sheet 1 Column I]
G9 H9 I9 J9 L M <-- hidden C D E F
Sheet 1 |Apples| 20 | Hot |Average | Hot | Average Sheet 2 |Apples| 20 | AP | Average
View 1 Replies
View Related
