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...................................
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?
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.
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.
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
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.
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 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?
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!
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)
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.
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.
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?
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
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.
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?
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.
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
