Autosize Cell Comments/Shapes Via Macro Code
Aug 25, 2006
Among many attempts to insert and autosize a comment using VBA manually, which errored, i recorded the following bit. When running the macro, it does format the cell - text, not the comment - text and errors in the line highlighted in red.
Range("A14").Select
Range("A14").AddComment
Range("A14").Comment.Visible = False
Range("A14").Comment.Text Text:="Ladida"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
I just want to add
.AutoSize = True
or something to that effect to the bit below. Any suggestions?
Range("A14").Comment.Text "Ladida"
View 5 Replies
ADVERTISEMENT
Apr 23, 2007
When turning on sheet protection, I get an error with the following code that I'm using to make sure rows adjust their height when lines wrap.
Rows.EntireRow.AutoFit
What can be done to eliminate this conflict? All of the cells I want available for editing are unlocked.
View 3 Replies
View Related
Jan 3, 2014
I have a worksheet that has user fillable cells from B11-AR53. I want the user to be able to attach comments to cells and then to be able to print them on secondary sheet. I know that excel already can perform this function, but it references the cell address and I don't like that method. I want to have the comments on the printout indexed and tied to an index number in the cell. Creating another worksheet with the comments only is perfectly fine. I am currently using this formula (below) to index the comments on the worksheet that they are attached to.
Ideally my report/second worksheet would list-
1 - Comment that is indexed as 1 would be here
2 - Comment that is indexed as 2 would be here
....and so forth
Sub CoverCommentIndicator()
Dim ws As Worksheet
Dim cmt As Comment
Dim lCmt As Long
Dim rngCmt As Range
Dim shpCmt As Shape
[Code] .......
View 5 Replies
View Related
Mar 17, 2009
I have the code below, and it works perfectly on my Mac. But... on my PC it does not.
The cell comment does not resize horizontally when I enter text. So if I enter a long comment I get a comment box that extends far to the right to fit the contents in. Vertically it is fine, nothing cut off and no extra space.
Any ideas why this would be? Any ideas how I can force the text to wrap in the cell comment box?
Code is below:
Sub AddCom()
Const USERNAME As String = "Greer:"
Dim strCommentName As String
Dim cmnt As String
Dim NoMore As Boolean
Dim Pos As Long
cmnt = InputBox("Please enter a comment")
strCommentName = cmnt & vbLf & Now
On Error GoTo 0
With activeCell...........
View 9 Replies
View Related
Jan 2, 2007
I am trying to add comments in a selection of cells and where there is a value then the comment should show the formula value of the cell ie if the formula in cell A1 is = 9 + 10 then the comment should show this. This I have got to work, but if I have a comment already the macro fails and I have tried to get round this but I am not clever enough.
Sub InsertingComments()
Dim r As Range
For Each r In Selection
If r.Comment = True Then
View 9 Replies
View Related
Nov 25, 2006
How do i draw shapes on the fly on forms?
View 4 Replies
View Related
Nov 25, 2011
I have the following code:
For Each wShape In ActiveSheet.Shapes
wShape.Delete
Next wShape
This works great to delete the buttons and rectangles I have on my worksheet, but it is also deleting charts. Is there any way to avoid the chart deletion?
View 5 Replies
View Related
Mar 9, 2014
1.) Create shapes (customize oval shape), I was able to create one but I think the code needs improvement to have the shape a fixed name. - see attached excel file.
2.) Move shapes into corresponding cell. Example: (Oval shape1, should be in cell g6). - see attached excel file.
Customize Heatmap creation.xlsm
View 1 Replies
View Related
Aug 30, 2013
Im trying to create shapes Rectangles and Squares with different colours within an excel sheet, where the length and height of the shapes is generated by input values.
Also is it possible to create 3D shapes, again where the size of the shap is generated by input cell values.
View 1 Replies
View Related
Apr 26, 2009
I searched for a code to add pictures to comments with VBA.
I only found this:
View 14 Replies
View Related
Jul 8, 2014
I was working on a presentation for work where I wanted to build a 'Family Feud' type board to play a game. In the board I am using activex text boxes with code that would hide the text box to reveal the answer underneath or it would show a custom shape (an X in a box) and play the buzzer sound. The code is pretty simple, first I would make the shape visible, then I would play the sound then the shape would be made invisible. If I step through the macro everything works fine, but when I run the macro, you never see the shape. It's almost as if the sound plays before the shape shows up and then it is made invisible again. I tried putting a wait and a sleep command between making the shape visible and playing the sound but that made no difference. If I remove the code to hide the shape at the end and run the macro, the sound plays and then the shape appears. Is there anyway to have the shape appear prior to or at the same time as the sound plays?
Code:
Private Sub CommandButton8_Click()
Application.ScreenUpdating = True
ActiveSheet.Shapes("First Strike").Visible = True
Play_Strike_Sound
'The previous line refers to another macro that has the code commented below. The sndPlaySound32
'function is one I picked up from cpearson.com
'sndPlaySound32 "C:\_Fin SysSoundsff-strike.wav", SND_SYNC
ActiveSheet.Shapes("First Strike").Visible = False
End Sub
View 3 Replies
View Related
Jul 15, 2006
Does anyone know code to bring up a comments box automatically when a certain word is entered into a cell? I have an If statement but I just don’t know how to open a comments box in the same cell.
View 4 Replies
View Related
Feb 28, 2013
how to create a function in VBA that works exactly as SUMIF, except it will also add a comment in the cell that references the formula, where the comment would be a list of the individual cells being added? Basically the comment would read "2+4+5+6" or "2,4,5,6" or something along that.
View 1 Replies
View Related
Jun 3, 2009
my macro processes a lot of data and puts a comment in certain cells (several thousand of them). I would like to autosize the comments so that the text can all be read by default.
I found the following on the web:
View 2 Replies
View Related
Mar 3, 2008
I have a spreadsheet in which double clicking on any of the cells in the range A4:C17 changes the contents of cells F4:I33. The following code tells me which is the active cell and then pulls the values for F4:I33 from a lookup table based in this.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Range("B100") = ActiveCell.Address()
Cancel = True
End Sub
code to stick in this (probably after the third line) that autosizes rows 4 to 33. There are no merged cells involved.
View 3 Replies
View Related
Jul 10, 2006
I have a shape that will have no macro. How do I prevent this shape
from being selected and prevent the message that no macro can be found,
when it is clicked?
With shapes that do have macros how do I disable them ?
How do I keep them visible but not have the mouse cursor change on mouse
over and not have the macro run on a mouse click.
I tried to use Enabled = false but apparently that property cannot be used
for a shape.
I need to toggle between enabled and disabled.
View 9 Replies
View Related
Aug 2, 2006
I'm trying to automatically resize the width of a combo box based on the width of the longest item in the combo box. The data in the rowsource of the combo box is dynamic.
View 5 Replies
View Related
Dec 2, 2006
I have two lines (in a sheet) that I am trying to move based on a formula. The wierd thing is I can get the first one to work fine, but when I select the next line it will not work. I have Line 2, and Line 10. Basically the code is:
ActiveSheet.Shapes("Line 2").select
Selection.ShapeRange.Top = 159
ActiveSheet.Shapes("Line 10").select
Selection.ShapeRange.Top = 300
For some reason it just moves Line 2 no matter what I do.
View 6 Replies
View Related
May 29, 2014
I have a report that has a set template except that the number of columns change.
I have 3 rectangular shapes with text boxes inside each rectangle. The Shapes take up space vertically between row 8 and 15. In some reports the columns will change, the size of columns might change due to longer text. Is there a macro that will select all the shapes and text boxes and center them column wise between the print area?
View 1 Replies
View Related
Sep 14, 2008
I have one master worksheet named "Season" and 30 other worksheets named "1,2,3,4 and so on to 30". I ideally want to copy the shapes (msoShapeOval) from the worksheets- "1-30" to worksheet- "Season". When the shapes (msoShapeOval) are copied from worksheets "1-30"
I want them to keep thier position that they were in when copied to worksheet- "Season".
The shapes (msoShapeOval) are in range "A1:AZ43" in worksheets "1-30" and would be placed in worksheet "Season" range "A1:AZ43"
View 9 Replies
View Related
Feb 10, 2007
I am trying to find out if it is possible to do the following
I have an excel workbook with comments in a number of worksheets. I have created a macro so I can list the comments and the value of the cells with comments. The macro creates a new worksheet and list the comments and value this great but I can only get it to create the comments from the sheet which is active. Is there a way of getting all the comment and cell value from all the worksheet (the entire workbook)
this is the code for the macro
Sub shoppinglist()
Application. ScreenUpdating = False
Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Set curwks = ActiveSheet
On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error Goto 0
If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If ...
View 6 Replies
View Related
Oct 8, 2009
When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell. This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell. Why does my macro treat an empty cell as if it has a comment?
View 4 Replies
View Related
Jul 24, 2007
I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action. I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.
View 6 Replies
View Related
Dec 17, 2008
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains.
Cell B5, for example, would contain the text:
Range("B13").Formula = "SUM(D12:D14)"
I need a macro to "execute this code", as if it were in the macro itself.
I have assigned the above to a variable, but am not sure how to execute it.
EG.
Dim the_calc
the_calc = Range("B5").value
Now, how do I run the_calc ?
View 9 Replies
View Related
Jul 12, 2007
Reference the archives at:
http://www.mrexcel.com/archive/VBA/17448.html
How can I modify this sub to skip cells that are empty? I only want cells that are populated within the range to actually display the comment indicator, with the cell text transferred to the comment.
Ideally, I'd also like the text to be moved (not just copied) to the comment and leave an 'X' in the cell, in place of the text that was transferred to the comment, but I'll settle for the action above.
View 9 Replies
View Related
Oct 24, 2007
I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.
I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).
View 9 Replies
View Related
Jul 3, 2014
I made a little macro to change the color of a circle whether my cell value is positive or negative.
However, I'm looking for a way to code it in ThisWorkbook sheet so that the code runs by itself every time the cell value changes.
[Code] .....
View 1 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
Aug 8, 2012
I have a map of the US composed of 50 shapes (one for each state). I need each shape to appear or disappear based on a value in a corresponding cell. I can't use VBA for this as it needs to function for users in a high-security Excel 2003 which doesn't allow macros.
View 1 Replies
View Related
Apr 17, 2008
Can Excel Draw Shapes or Angles based on Data? I have a radiation Excel file that solves for Radiation intensity using Distance and shielding as reducing factors. Similar to a circle of light being less intense with distance so is radiation. I need excel to draw a circle from a point specified based on a value I give. For example at 1 ft I might have 500 R in 1 hour (life threatening!) At 16 feet I have about 2 R in 1 hour (not safe for too long). These circles would be overlayed on an map. I would like to draw a new circle at various distances, simialar to a bullseye.
I would also like a triangle drawn based on angles and SIN / COS / TAN formulas
View 2 Replies
View Related
