Macro To Create Empty Comments And Resize Them To Uniform Size?
Nov 28, 2013
I am a novice user and not very familiar with Macros or VBA. I took a macro I found on contextures and got it to resize comments that already exist. However, I am trying to get it to also create the comment (with no content) if the comment does not already exist, and then resize it. This is what I have so far:
Code:
Sub ResizeCommentsInSelection()
Dim mycell As Range
Dim myRng As Range
Dim lArea As Long
Set myRng = Selection
[code].....
View 2 Replies
ADVERTISEMENT
Apr 2, 2009
when i use a macro to add a comment to one of my cells, if i put in too much text the box doesn't resize itself and you can only read part of the comment. how to resize the comment box to the size of the text i put in?
View 2 Replies
View Related
Nov 23, 2006
open the attachment & click the button. I'd like you to please help me in reducing both the height & width of the picture. So that when the button is clicked the pic. overall size will appear smaller.
View 9 Replies
View Related
Sep 29, 2011
I have a form in vba with two tabs. Both the tabs are different sizes.
The first tab is quite large and the 2nd tab is smaller.
When I click the 2nd tab it shrinks the form but then when I click back to the first form it makes it the same size and the 2nd tab and cutting out key info.
Is there anyway I can resize the 1st tab again to go back to its original size?
View 3 Replies
View Related
May 13, 2014
I am inserting through a vb script code, comments to cells... But the thing is, that certain comments are larger than others... Is there any code that let the yellow popup resize to show all the content ? Coz I started thinking in incrementing it's height according to the number of lines.
View 6 Replies
View Related
Jul 26, 2008
I want to create a comments box whenever a cell is double clicked in sheet "Settlements"
View 9 Replies
View Related
Dec 19, 2009
Currently we have an excel sheet in which it contains four sheets(sheet1,sheet2, sheet3 and sheet4). In this sheet1,sheet2,sheet3 and sheet4 there is some text data in the columns.
what we are looking is we want a comment box to appear when one moves the cell focus in a sheet (sheet3 or sheet4) to a cell in the field column. In the comment box it should display the data which is in the other sheets(sheet1,or sheet2). The items in the comment box should be data field column,fight type,Defect colour, and list in english column.
The comment should be temporary..i.e it appears when focus is on the particular cell in column field and disappers when focus is moved to another cell.
View 14 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
Oct 20, 2005
Macro to CHANGE number of rows “3” to a variable range
(end.xlDown) on the macro below.
Sub GotToRangeResizeCopyPaste()
Application.Goto Reference:="MyRange"
ActiveCell.Resize(3, 10).Select
Selection.Copy
Range("OtherRange").Select
ActiveSheet.Paste
End Sub
View 11 Replies
View Related
Dec 25, 2012
I am seeking to analyse loading cycles on a beam.
I have half hourly data for five years, in two columns A: Date and B: Load in Kg. The load on the beam is not uniform over time and shows a cycle of load.
In Column C I have an AVERAGE function that returns the average load based in the 4 hour period (2 hours backward looking and 2 hours forward looking) if it is less than 50% of the maximum load.
The data in Column C returns sets of continuous vertical cells of data showing periods of low loading and empty cells where the load is greater than 50%. The sets can be as small as one cell (i.e. one hour) to a set of contiguous vertical cells for one week of low load.
My objective is to use Excel to analyse all the sets of contiguous cells in Column C to determine the time period from the first cell of each set to the last cell of that set and drop the result into another work sheet (pivot table) to further analyse these results.
View 5 Replies
View Related
Dec 2, 2011
I have one shared book having different tabs from my team member.
in every tabs i got Dates written in various forms like,,,,2/12/2011, 12/2/2011, 12-2-2011 etc......
I want to get uniform date format from all the tabs at once.
View 6 Replies
View Related
Feb 6, 2007
I would like to click on my chart, which then gets resized automatically depending on its current size.
Situation:
My main macro creates various charts, all of them I connect to the macro "chartMakro". After the main macro has finished successfully, I can click on such a chart and the macro "chartMakro" is launched, supposedly resizing the chart. unfortunately i do NOT know how to tell the macro which is the active sheet, therefore my macro fails to work.
Question:
When a click on a chart launches a macro, how can I tell the macro which chart called it?
'appending a macro to a chart
' MAIN MACRO
'[...]
activeChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.OnAction = "chartMakro"............
View 4 Replies
View Related
Sep 12, 2012
I have a matrix that calculate lenght of the sides in a triangle, this is for the purpose of a camera and the pixelsize/inchsize.
Is it possible to create a chart that will give the user an visual control on how the cameras detection area looks like?
View 2 Replies
View Related
Dec 11, 2008
I have a user form on which I need to create a textbox every time the user clicks a button. There are too many to create them all in advance and make them visible when needed. The sample attached, from this forum, is good, except, the numbers in the text box just scroll along and I need to see all the text typed into my textboxes. I would like the textboxes to be multiline, wordwrap and have them resize according to the amount of text.
View 6 Replies
View Related
Jun 25, 2012
I'm looking to generate correlated random unifrom numbers between 0 and 1 using the excel random number generator, rand(), and a user defined correlation matrix - without the use of a plugin. I realise there are plenty of risk plugins I could use, but was looking for something excel/vba reliant so I could distribute it without the user having to install anything.
I've tried using a cholesky matrix, but most of time it resulted in one of the numbers being 1, and higher that all the others.
View 2 Replies
View Related
Aug 7, 2013
I have a very large dataset (~5000 points in a 2D array) which I need to plot and fit a linear trendline to. There are 90 subjects each of which has a daily binary event checked over its lifespan. eg:
Day: 1 2 3 4 5 6 7 8 9 10
subj 1 0 1 0 0 0 (dead)
subj 2 1 1 1 0 1 0 1 0 0 0(dead)
subj 3 0 0 1 0(dead)
I simply averaged the number of events per day per subject and plotted them but near the long-lived end of the data the variability gets really high. I have normalized the lifespan for each subject such that instead of the event occurring on day 10, it occurred at 0.2 of its total lifespan (dying on day 50). Now that my data is in this format, I want to create an average for all subjects over the domain 0-1 representing birth-death.
The trouble is that now each subject's domain has different intervals between points (eg. 0.1 for subjects living 10 days and 0.006993 for those living 143 days). How to now space out the cells (somewhat) automatically so I can average the columns? My array is 90 x 150 so doing it manually is not really possible. Or, alternatively, is there a way to create a continuous average rather than a discrete one? Some sort of function that goes through each infinitely small (or even a discrete really tiny interval) point of the domain (0-1) and averages the binary y-value?
View 2 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
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
Jun 2, 2008
I have a short VBA program that either outputs a number to the cell or nothing. Now, the problem is that for it to output nothing to the cell ie. give the appearence of a empty cell i just write [program name] = "". However I want to be able to multiply my cell with other cells and this generates an error when I have "" in the cell. If I instead output a zero to the cell it works fine, but I dont want a zero, I want empty cells.... for estetic purposes.
I've tried to format the cell as number,
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 19, 2014
I have created in VBA a formula to look for the last empty cell in the "SBV INFO" sheet and it works.
I now need the VBA to go the the DCWC sheet and only extract the data and amounts from Columns K and M where there is a value more than "0" in column M.
The the data in Column K where the value in Column M is more than "0" must go to the first empty sell (found in the SBV INFO sheet) as per the VBA and the corresponding amount in Column M must go to Column AA (also in the SBV INFO sheet)
In other words from the attachement I need the following:
Column M in SBV INFO sheet 1st empty cell (this will vary which is why i did the formula)
SBV DURBAN WHL
SBV George RET
SBV PEW WHL
Column AA
237,030.00
248,000.00
150,000.00
This might change in future. which means I would have to be able to transfer 1 to 15 of these names and amounts depending on the value in Column M from the "DCWC" sheet.
View 7 Replies
View Related
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
View Related
Mar 11, 2009
I am trying to create a loop which stops once a cell is empty.
Assuming I have declared proper DIM, my code is
Row=1
Do
Row = Row + 1
SheetName = Cells(Row, "A")
Sheets.Add.Name = SheetName
Loop Until IsEmpty(SheetName.Offset(1, 0))
View 9 Replies
View Related
Sep 5, 2007
Im trying to find a formula that when a cell is empty ie this cell is linked to another cell on another worksheet and info has not been entered or is 0 then i want the cell to display the the current date ie =NOW()
View 10 Replies
View Related
Apr 12, 2007
I am using the following macro to creat comments based on the contents of certain cells. Everything works except the it resizes the Comment Boxes exponentially. The Last comment box in the range seems to be the default size, the next to last a little bigger, then it start getting even larger and larger until the first comment in the colomn.
Sub InsertComm()
Dim RgPartnumb As Range
Dim NRg As Range
Set RgPartnumb = Range(Range("B2"), Range("B2").End(xlDown))
On Error Resume Next
For Each NRg In RgPartnumb
With NRg
.Comment.Delete...................................
View 9 Replies
View Related
Feb 12, 2009
If I run macro, the xls size will increaze from 80 kilo to 2.4 mega!
I don't want that because I need the size of the xls maximum 200 kilo!
open attached file, check file size (80 kilo) run the macro and you will see that the macro size will be 2.4 mega.
This is the
View 14 Replies
View Related
Aug 27, 2009
I have a working macro where I use a Forms Command Button w/ a Macro that copy/paste specific variables and range in the workbook to a pre-defined PowerPoint template file (in the same directory as the workbook file). See code below: Questions:
1) Is there a better script to reference a value in a cell value in Excel and Paste in PPT template?
2) Is there away to auto scale the copy range from Excel to PPt? Curently I have to manually adjust the height/width parameters.
View 3 Replies
View Related
