Comment Box Size - Macro
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
ADVERTISEMENT
Oct 14, 2009
I need to default size to be bigger. Is there a way to do this so I do not need to re-size the box every time?
View 2 Replies
View Related
Oct 26, 2013
Is there a way to adjust the font size in a comment. The default seems way to small. All thoughts on the subject .
View 4 Replies
View Related
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
Sep 16, 2013
The comment boxes keep changing shape and location in my spreadsheet. Some are completely smashed together, while others are extraodinarily wide. I had them all to the right of the cell they applied to; but now they are all over the place.
View 1 Replies
View Related
Sep 26, 2009
Is it possible to change the default text size on comments I insert throughout my spreadsheets? I have to enlarge 100% of them.
View 2 Replies
View Related
Jan 18, 2014
I am wondering within the Excel interface, is it possible to auto-fit the size of a comment box to its content?
I have searched the internet and found that a macro as follows could do the job:
Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
c.Shape.TextFrame.AutoSize = True
Next c
End Sub
However, this Macro applies to and resize all of the the comment boxes in the active sheet.
I would like to just auto-fit the comment box only to the selected cell, or even better, to multiple selected cells.
View 4 Replies
View Related
Mar 13, 2014
how 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.
View 6 Replies
View Related
Nov 6, 2012
how can i run a macro after a comment is inserted?
View 6 Replies
View Related
Dec 4, 2006
Need a macro for the following:
C | D | F (Column)
10 | 100 | Yes (data)
If column F = Yes then
column G = "abc = 10, def fgh = 100"
where 'abc =' is additional text, '10' is C value.
Loop till the last row of F.
Basically, I want to autofill column G with the data derived from column C & D + some text.
View 3 Replies
View Related
Apr 5, 2013
I want to design a macro to check if the cell has a comment in it. If so, the macro should read the comment and store it in a variable.
View 2 Replies
View Related
Jun 3, 2006
I wanted to make a simple macro forinserting a comment, given certain parameters: margins & alignment...but its not working...how would i get this to function properly
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=""
With Selection.Font
. Name = "Tahoma"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False...............................
View 2 Replies
View Related
Jul 28, 2006
I know that there is a way to delete macros in a file using another macro in order to save the file as a macro-free copy. I was wondering though if there is a way to comment and uncomment code using a macro, as well. The reason I need to do this is that I have a module that builds Excel files for me using a template, but that template has to have certain lines of code commented first in order for the module to run correctly. After the module is done building the file, I must uncomment those lines manually and was wondering if there is an automatic way to do this.
View 3 Replies
View Related
Mar 19, 2007
I need a quick function or a Macro which can add cell comment based on selected cell.
let me explain in detail
Column
a1 Mr A wrote
a2 Mr B Checked
a3 Mr C authorised
etc etc.
now i want the same in Cell B as its cell comments
so after b1 comment will be Mr. A wrote & so on
View 4 Replies
View Related
Jul 26, 2009
I have comment blocked the code that causes Excel to crash.
View 4 Replies
View Related
Oct 3, 2009
please have a look at the attached file.
I want to sort the data by the comments in Column B, specifically by comments that do contain a telephone number, whether it is "Telefon" ( e.g. in B17 ) or "Phone" ( e.g. in B24 ) or "Téléphone" ( e.g. in B16 )
I know a Macro written in VB could do that.
View 8 Replies
View Related
Jul 11, 2013
I am in need of a macro that will copy the text from A1, and paste it to a comment on every cell that contains text within sheet 1.
View 4 Replies
View Related
Nov 2, 2009
I really appreciate help from the excel gurus and expert here on my little problem. I'm rarely using excel and have a very little knowledge on VBA programming.In short, I've been requested to develop a shift schedule for my department that can automate the shift staff schedule on the monthly basis.
I've created two worksheet of which the first worksheet is represent as master data list (INPUT Worksheet) that contain with formulas. While the other worksheet is the automated shift data (REPORT Worksheet) that linked from the master data list (INPUT Worksheet).
Attached herewith is a sample of my work. As you can see,I wanted to copy comments from the input worksheets (on DUTY column) so that it will then automatically appears or updated in the shift schedule. Whenever the dropdown selection change, the data will change accordingly. On the REPORT worksheet, each cells, range D14:AH38 are referring to DUTY column month by month. I'm able to link it but it fails to copy over the comments as well. I believe that macro can resolve this, can someone help me on this?
View 12 Replies
View Related
Sep 11, 2007
I have created an attendance tracker for a team of people at work. It simply involves a grid of the days of the month and on each 'day' (or cell) I have set up the Validation command to prompt the the user to select whether they were at work, on holiday, off sick etc. etc from a drop down box. If they do not change the cell, its defaulted value is that they are in work.
When a user selects the days that they are planning a holiday in the future, they are meant to write a comment on the cells they have changed, to advise when they had 'requested' the holiday.
What I would like is (a macro?) which makes the comment box automatically 'pop up' when they change a cell from its default value prompting them to fill in the details rather than relying on them to 'add comments' manually.
In addition, what would be the best way to 'restrict' them from booking holiday within the next 7 days - we have a problem with people booking holiday on 'lastminute.com' for 2 days time, booking off the holiday on the spreadsheet and saying that they had it planned for weeks!
View 9 Replies
View Related
Jun 25, 2009
making a macro that will scan for duplicates, length & empty cells.
I have the sample file below that does conditional formatting but it doesnt help as much because I want to show the reason for the highlight's on a comment instead.
sample file : [url]
file that might help out: [url]
checks would be:
column A - duplicates and/or length should not be over 100 characters
column B - duplicates
column F - should only contain 2 comma's(or 3 keywords)
all columns - check if no entries are found(empty cells)
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
Jan 14, 2010
I have a workbook that grows in size on every use. I import a text file, manipulate the data, manually export it and delete the data from the workbook.
So why does it continue to grow in size? By manipulating I mean, add rows and text to the new rows. Attached is a copy of the workbook in native form (have not ran the macro) and a sample file to import, the excel file is 59kb in size and the test file is 9kb in size. I have seen my original file grow to over 500kb from repeating the same macro on the same file many times or running many different files for testing.
There is a CommandButton on Sheet1, it will open a userform. Click in the largest textbox and it will open a file dialog box in "C:". Place the supplied text file in "C:" or some other folder of your choice as you can navigate to any folder or drive from the dialog box. The macros that add lines and or text are "JumpToLabelName" (this runs first) and "InsertRows" which runs second. These two macros do all the work.
Take note:
I store information in the Registry, "GeoMeasure" under "VB and VBA Program Settings" (Normal windows key entry). You can search for "GeoMeasure" and delete it if you decide to test run the file.
View 5 Replies
View Related
Dec 18, 2007
I have an excel file that is about 1,400 kB in size. This file is the backend for a complex model with many inputs, and I now want to build a front end for it using userforms and VBA. However, when i created the first userform, my excel filesize blew out to about 7,000 kB. When I added all of my userforms (about 16 of them because there are lots of inputs, organised into categories), the filesize grew to 77,000 kB.
I have tried everything to reduce file size, including clearing all unused cells, avoiding pivot tables of any sort, and have also exported then re-imported all of my VBA modules and userforms. But the situtation stays the same: no userforms or VBA and my filesize is about 1,400kB, add in all the userforms, and filesize is 77,000 kB. Is there a way to keep file size down with userforms, or is that just the way it is? Is the only way to keep file size down to have a single userform, all questions on that one form, perhaps with visible and invisible controls depending on the 'page' of questions being viewed?
View 7 Replies
View Related
Mar 23, 2009
Please see attached two books,
The first book is a report that is generated on my reporting systeM (Report I use everyday)
The second report is the one that I have created myself using a macro (macro report)
I have to manually manipulate this report everyday and extract some information from it to
a custom report(macro report) that we use everyday.
I have created a macro for this - please see my code below.
I need some help with some small issues I am having,(Highlighted in yellow)
1) I need to include a Total line, but the size of the report varies everyday from say
around 30 rows to over a 100 rows. Is there a way i can make the total always 2 rows below
the report, please see attached for an example.
2) I need to hardcord a formula in on Column F and G. But again the report varies in size,
the columns do not change but there could be lots more rows. how do i take this into
account
The formula i need to hard code into column F is
=(IF(($D$60>=0)*AND($D$60<=5000000),0.0009))+IF(($D$60<=10000000)*AND($D$60>5000000),0.0007
)+IF($D$60>10000000,0.0005)
D60 in the above formula is just the total of all the other months summed together.
For Column E the formula is just =F*E But again the size in the report varies.
View 13 Replies
View Related
Sep 9, 2012
I have a macro-enabled workbook, in sheet1 i have two columns Country and Codes, they are in column C and D respectively. I used vlookup formula in generating the codes of the countries pasted in column C, they are being looked-up in sheet2 in column C and D as well. I already have 100000+ rows in sheet 2 under column C and D that's why the file is now heavy. Is it possible to encode column C and D in sheet 2 but still can be looked-up? or is there any other way to minimize the file size.
View 7 Replies
View Related
Jul 7, 2014
I have this macro that works perfectly. My boss wanted the subtotal lines within the report to be in a smaller font, so I added lines to the macro to do that. It won't work with those lines in there! It says the reference is not valid on the red line in the code below. The 2 blue sections are the ones I added to change the font size.
Code:
'Add subtotals at the end of each age bucket
For i = 10 To LR Step 1
fr = Range("E" & i - 3).End(xlUp).Row
If Range("B" & i).Value = "Totals" Then
Range("E" & i).Formula = "=SUBTOTAL(9,E" & fr & ":E" & i - 2 & ")"
With Range("E" & i & ":R" & i).FillRight
[code].....
Why would the font size make a difference? Is there some other way to change the font size that would work better?
View 5 Replies
View Related
Dec 10, 2008
I have this formula that checks a line of values to see whether it is within the constraints of a separate list of values.
=if(AND(Data1!A4="count1",Data1!D4>="bin1",Data1!D4
View 9 Replies
View Related
Sep 13, 2006
I have a macro that takes a worksheet and copies it to another workbook. It works, but the graph that is contained in the new worksheet ends up a little screwy; for instance, the Plot Area is larger than it should be (height-wise along the screen), and the chart's legend isn't in the exact same place as in the original chart.
ThisWorkbook.Worksheets(tempSheet).Copy After:=wb.ActiveSheet 'before:=ThisWorkbook.Sheets(1)
RenameTemplate
'unlink copy from template
'must unprotect before unlinking. We'll leave it unlocked cuz we don't care.
wb.ActiveSheet.Unprotect
'cells
wb.ActiveSheet.UsedRange.Copy 'ThisWorkbook.Sheets(tempSheet).UsedRange.Copy
wb.ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValues ......................
View 3 Replies
View Related
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
View Related