How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
I have an excel spread sheet and on the last column on the right hand side I have some comment boxes but when I hover over them they pop up to the right of the cell, and then they are off the screen.
Can I alter it to make the comment box pop up to the left side of the cell.
[Code] ....
Is it possible to adapt this code to get me the desired results or will it have to be a separate one.
Also i'm not sure if I have the code right because if I run the macro then open a comment box in the (M) column as in the code the comment box is the default size then if I run the macro again once I have the little red tick in the corner of the it changes to what the code says.
we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
Recently I have been practising writing my own macros in VBA. I am using Excel 2010.
I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.
I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..
I would like to accomplish 2 things in my Excel 2010 spreadsheet by click a cell which already has a number and formatting in it.
1. How do I change the color of the cell, the color of the number in it, and the border around it. (Make it look like I just pressed a button by clicking it.)
2. At the same time have the text in different cell and the number in the selected cell appear in another different cell. (Text in a different cell = A , and the number in the selected cell = 23, the value in the resulting cell be "A 23") Everything I would like to happen at the same time by clicking the selected cell. I would also like this to be done several times by clicking different cells and not changing the previously selected cells.
I am working on a database and trying to remove duplicates. When there are duplicates, I want to take the contents of one cell and copy them as a comment on the cell above (or below).
I tried to work on a small macro to do so, but the contents of the cell cannot be copied (it comes out as a blank).
The long way is to 'double click' in the cell, Ctrl+A, cut, click the cell above, Ctrl+F2, paste. This way is very long as I have thousands of duplicates.
I am trying to make the contents of a cell into a chart title.I know I can do it like this...
ActiveChart.ChartTitle.Select
Selection.Caption = "=tab_name!R2C1"
However, I am trying to use the same macro on several different tabs in the same EXCEL worksheet. Obviously, I can't have the tab name in the macro. But, I don't get a title at all when I try to remove it like this...
ActiveChart.ChartTitle.Select
Selection.Caption = "=R2C1"
or
ActiveChart.ChartTitle.Select
Selection.Caption = "=A2"
Is this possible? I am (kinda new at) using EXCEL2010.
I'm using excel 2010 and windows XP with a moderate amount of experience tinkering with macro programming. I know what I need is very doable but I can't get my head around what the code would look like. I must not be wording my searches correctly because most of what I'm getting for results are iterative programs based on a cells value which isn't what I need.
I'm trying to build a macro that will check a cell (C3) and based on the contents of that cell copy a column (I) to one of 12 different columns (K:V). So if the value in C3 is 1 it should copy I to K, if the value is 2 it should copy I to L, and so on.
I am using Excel 2010 and trying to change/edit the color of the tabs in my workbook to turn green or red based on a y or n placed in a cell (the same cell on each tab). I have tried variations on several themes others have asked about as well for Excel 2007 and attempted to adapt them to fit my situation but none seem to work. Here's what I was starting with:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is < 2.5
Sheet1.Tab.Color = vbRed
Case Is > 2. 5, Is < 4
Sheet1.Tab.Color = vbGreen
End Select
End Sub
The cell I'm using is F2 and my cell value is simply y or n. I realize the example above references numeric values and greater than/less than options, but I'm not sure how to correct this for my need.
S/W Environment: Excel 2010
Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?
if its possible to link contents of a cell to a comment.
IE - Whatever the cell contents in sheet 1 A5 would reflect in the comment in sheet 2 A5 Im happy if its coded that would work.
I was wonder if there was a way to automatically turn the contents in cell B2 into a comment for cell A2?
Is there a formula or some kind of automated macro or function?
When a change is made in cell L11, cell N11 should be locked automatically for typing. This is true for the cells between L11:L25, and cells N11:N25.
I have been trying to come up with a code that will:
1 - start my macro when f.ex. L11 is > 0
2 - lock N11 when L11 is filled out
3 - unlock N11 when the input in L11 is deleted
This is my attempt so far to put together a code:
[Code] .....
I'm using Excel 2010.
I am trying to extract contents of cell to a comment.
Column K is Overtime Hours
Column L is Regular Hours
I have managed to create the following macro that will copy the contents of a cell in Column K and put it as a comment in Column L. But I am needing the macro only to create a comment if the value of the cell in Column K is greater than 0
I would like to Insert the contents of a Range of Cells into a single Comment. ie:
The contents of Range A1:A50 into a Comment placed at F1. Is there a Macro that will do this?
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
I wish to be able to change the default appearance of a cell's comment. Currently, the default font size is too small to be useful - is there a way of changing this setting, without reverting to a VBA tidy-up?
View 2 Replies View Relatedhow to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
Im trying to find out is there a way to remove the shadow around comment boxes in excel 2010.
View 7 Replies View RelatedI am looking to have a select few cells generate a comment when the user changes their value. I don't want the entire sheet to be affected as it will only apply to the header row of columns J:P.
I'm not sure if it matters but I have the worksheet protected and the cells in question are unlocked.
Below is an example of the code I'm trying to get to work.
Option Explicit
Public preValue As Variant
Private Sub worksheet_change(ByVal Target As Range)
End Sub
If Target.Count > 1 Then Exit Sub
Target.ClearComments
[Code] .......
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.
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies View RelatedI have worksheet with 5000 rows data's including the comments in one coloumn.My problem is when I edit the comments,the comment box shows somewhere else is not showing to near the related cell.I am using excel 2007,its happen after the upgradation of 2003 to 2007.
View 2 Replies View RelatedI'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;
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)
I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001
q9002
q9003
q9004
q9005
q9006
etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
(Using Excel 2010)
Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.
Excel 2010
A
B
C
1
Helper Column
Desired Result
[Code].....
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
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