New answers tagged

1

Reading the HTMLBody property is considered a security risk, as malicious code could read your inbox. You might want to look at 3rd party products like Outlook Redemption. As you rightly determined, setting the property with a pre-constructed HTML string will avoid the need to read the HTMLBody property, and it will likely be more efficient too.


1

You can do it even without using macros, this formula checks for you whether somebody had 5+ paid sick days in the last year: =IF(B3<>"",IF(COUNTIFS($A$2:$A2,">="&$A3-365,C$2:C2,"=yes")>=5,"no","yes"),"") (note: I've just check last 365 days instead of one calendar year, you may want to use different calculation).


0

The code below works for me, it copies the cell (background) colors into other cells in a different sheet. Sub copyFormat() Worksheets("Sheet1").Activate Range("a1:b1").Copy Worksheets("Sheet2").Activate Range("a1:b1").PasteSpecial (xlPasteFormats) End Sub


0

I did a quick test on Excel 2013 (German language). I inserted some random pictures. They got automatic names like yours i.e. "Grafik 4" To delete this shape, I would use this technique: Sub test() On Error Resume Next Set image = ActiveSheet.Shapes("Grafik 4") On Error GoTo 0 Debug.Print image.Name If Not image Is Nothing Then ...


2

Ok, I took the code by @EngineerToast and worked with that, as in principle it is correct but flawed (and does not produce the correct results). I haven't touched VBA in literally a decade but I decided to drive in here because I needed this for a spreadsheet. First order of business, we need an Option Explicit at the top! Never bother writing a single ...


0

Seems you need a little more VBA, so I have created the code below that incorporates the method described at the link. I do not know how you intend to use this, so the FormatCurrentCellPrice sub is just there as a proxy for your code that will point to the cell you want to format. The FormatPrice sub determines the location and length of the text you want ...


0

You can't format specific characters of your cell's text if the text is returned by a formula. You can, however, use VBA's Evaluate function to set the text and then access the Range.Characters collection of the target cell to set specific characters in bold.


1

This blogpost helped me in rectifying this error: https://dynamicsgpland.blogspot.in/2009/11/vba-error-class-not-registered-looking.html VBA Error: Class not registered. Looking for object with CLSID:{AC9F2F90-E877-11CE-9F68-00AA00574A4F} I know, I know, it's such an obvious error, right? I mean, really, is there anybody left in the Dynamics GP ...


0

Without VBA you can use Absolute References for M9 and M42 like the following: ="InterContinental Vienna @ "&$M$9&" "&$M$42&" p/room p/night" Format the cell All Bold


0

Have you added a reference in your VBA project to the "Microsoft Excel 14.0 Object Library"? (NB: The 14.0 may be different depending on which version of Excel you have installed) In your VBA window, from the Tools menu, select References to bring up the References - Project dialog. Then scroll down the list and look for the Microsoft Excel library and ...


0

The solution seems a little clunky, but without knowing everything about what you are trying to do I will not suggest extensive changes. The solution is pretty simple, you just need to initialize n to the last value in the list before the for loop. The way it is written now, n gets reset to zero every time you run the macro. Something like the following ...


1

I answered my own question - all I had to do was remove the duplicate code in my second If statement. MsgBox(cstrPrompt, vbQuestion + vbYesNo) =


0

I think it's because of your loop For i = 1 to 4 But you never reference i, so it's doing everything four times. You should use it like this instead - If Sheets("Data").cells(21,1+i).Value2 = "Open" Or Sheets("Data").cells(21,1+i).Value2 = "WIP" And ... I'm not entirely sure what the second part of your if is referring to, but you get the gist.


0

In your Move_Between_Sheets() you do select the sheet, but you don't activate it. The switching will only be done after activating it.


0

So instead of trying to do math, lets just step every 2 on the columns starting with the third column. This makes the math a little simpler: Sub NewLayout() Dim ws As Worksheet Dim i As Long, j As Long Dim intCount As Long For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row For j = 3 To 7 Step 2 If Cells(i, j) <> ...


0

An alternative that uses named arguments, and is, therefore, much clearer,is something like this. ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate A positive rowOffset moves the selection down the sheet, a negative rowOffset moves up the specified number of rows, and a zero offset leaves the row in which the selection resides unchanged. Likewise,...


1

I would instead, urge you do understand the logic of the functions. You know what a Sum is, that should be easy for you to replicate. The things you don't know - like a vlookup or stdev have some sort of logic - whether purely mathematical or operational (like transpose). Learn how those work and then design your code around the logic.


2

Microsoft Excel is a closed-source product, but as you indicated, source code of alternative to Excel will suffice, too. You can examine the source code of OpenOffice Calc which closely resembles Microsoft Excel (see screenshots). You do not need to install the product. On the download page, focus to section labeled Source and grab the archive with zip ...


0

LPChip put me on the right path and I thank you much. The process I used is as follows: on the sales person sheet, set lock attr for the whole sheet as false on the table for that sales person, set the lock attr for the complete table as true We created a custom range, as LPChip suggested, of all columns that we did not to be editable by anyone. Did this ...


1

For macros to be available to all documents you open, they are stored in the default template for Word, Normal.dotm. This file is not stored in a commonly accessible location like My Documents or on a shared drive. It is typically stored at the user level in C:\Users\username\AppData\Roaming\Microsoft\Templates. So, when you changed computers, you ...


0

Without seeing the VBA code, it is hard to determine how the locking/unlocking occurs. I assume it is done by setting the locked attribute for a cell, and using the worksheet protect and unprotect feature. What you would need to do, is select all the sells that are allowed for editing, and create a Custom Range for them. I don't have excel handy, but I ...


1

I don't see any code to wait for the page to finish loading... It might not be necessary, but it wouldn't hurt to put this at the top of your module and then call it after navigating to the website+stockticker. Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long) Public Sub PauseApp(PauseInSeconds As Long) Call AppSleep(...


0

A better approach would be to test your data with an if statement to ensure your data is valid. If not, do not proceed with the code block that may generate an error. In your example, this may work... change this: 'find the information that corresponds to each unit/test condition combination Intersect(pt.PivotFields("Plant").PivotItems(Unit).DataRange....


0

I ended up answering my own questions, by continuing to look through old posts etc., I found http://www.cpearson.com/excel/errorhandling.htm to be extremely helpful. It turned out I was trying to use two goto commands, first to goto the error handler then goto next iteration. What I needed to do was change the second goto, to resume. Thanks for all the ...


0

You can handle errors without goto in VBA like this: Sub ErrorHandling() Dim A, d On Error Resume Next REM Line that throws an error A = A / 0 REM Store details about your error before it gets cleared d = Err.Description On Error GoTo 0 REM You see and can handle your error message here MsgBox d End Sub On Error Resume Next Disables throwing ...


0

I found a solution thanks to another user. I added an additional IF THEN statement to the function calling the form, which is looking for a flag (either 1 or 0) in cell AI14 (which starts as 1): Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("AI14") Then If Selection.Count = 2 Then If Not Intersect(Target, Me....


3

You can do it with a helper column, just enter a new column with a formula: =MOD(ROW(),30) This will assign number 1 to rows 1, 31, 61, 91 ... 2 to rows 2, 32, 62, 92 ... ... 0 to rows 30, 60, 90 ... So if you wan't to keep e.g. rows 2, 32, 62... then just filter out 2 in your new column and delete what's visible.


0

Best way I found to do that was to write a VBA macro. It's probably not optimized for performance, but it does the job. =) Sub ConvertLeadingTabsToIndents() On Error GoTo Error Application.ScreenUpdating = False Dim found As Boolean found = FindNextTab() While (found) Selection.MoveRight Unit:=wdCharacter, Count:=1 ...


0

The solution i use where you have only a few cells that i want both calculations and the abillity to enter values to override the formula or delete what they entered and have the formula work is. Setup your worksheet as you want.. Then for the cell you want to override setup an identical cell in a column off screen, ie column az and cut and paste your ...


2

I download many files. To me, the obvious danger is the file automatically running a miscreant macro by itself. To prevent this, I disable Events before I open the file: Sub GetFile() Application.EnableEvents = False Workbooks.Open Filename:="C:\TestFolder\suspicious.xlsm" End Sub This gives me the opportunity to review all macros before running ...


0

This worked for me without manually adding the reference: Private Sub Workbook_Open() Dim success As Boolean Dim myAddIn As AddIn ' Load and install new XLAM Set myAddIn = AddIns.Add(Filename:=ActiveWorkbook.Path & "\path\to\myAddin.xlam") myAddIn.Installed = True ' Load known XLAM For Each myAddIn In AddIns If ...


0

Sub loopchange() Application.ScreenUpdating = False Application.EnableEvents = False Dim level As Integer Range("AF3").Select Do ActiveCell.Offset(0, -31).Select If ((Right(ActiveCell.Value, 1)) = 2) Then ActiveCell.Offset(0, 28).Select level = Left(ActiveCell.Value, 4) ActiveCell.Offset(1, -28).Select End If If ((Right(ActiveCell.Value, 1)) = ...


0

The issue was with the newer file, but not the sub. Someone else (shared master file) had apparently left data in a few of the cells. I'm not sure why this didn't cause an error on the sheet it was on but did after being copied over to the older file. The data wasn't a space or 0 that wasn't displayed so the formulas didn't know how to deal with it and ...


0

Sub loopchange() 'select the blank column Dim level As String Range("AF2").Select Do 'move to A2 cell' ActiveCell.Offset(0, -31).Select 'find out which is level2 MsgBox ("cell = ..2?" & (ActiveCell.Value = "..2")) If (ActiveCell.Value = "..2") Then 'move to column AD ActiveCell.Offset(0, 28).Select ...


0

It depends on what Outlook Object Model features you're using in your VBA. Each Outlook version introduces new objects and/or functions that are not exist in previous versions. So, always see "Applies To" in MSDN articles to make your VBA work for Outlook 2010-2016.


1

There are more efficient solutions, but this one is fairly straightforward and makes it relatively easy to see exactly what your code is doing: Look at "OFFSET" and "END(XLDOWN)" OFFSET(+/-# of rows,+/-# of columns) Example: selection.offset(-1,0) refers to the cell directly above the selected cell, while selection.offset(1,1) refers to the cell one down ...


0

It is not at all clear what assistance you need. This code will create a button on the worksheet: Sub Creator() Dim shp As Shape Set shp = ActiveSheet.Shapes.AddShape(1, 100, 10, 60, 60) shp.TextFrame.Characters.Text = "Press Me" shp.Name = "MyButton" End Sub After the Shape has been created, assign this macro to it: Sub ButtonPressCode(...


0

This VBA code will work: Public Sub quantityProducts() '********************** ' variables sourceSheet = "Sheet1" 'name of the source sheet destSheet = "Sheet2" 'name of the destination sheet titleRow = 1 ' Number of Title Row firstRowSource = 2 ' First row of source data firstRowDest = 2 'First row of data in destination sheet ...


0

Hack: copy entire column to clipboard, paste into notepad, copy all from notepad, paste back into excel. Make sure your computer date/time format is matching the one used there, otherwise excel won't interpret it.


0

Step through the code with F8. When you get to the critical line of code, hover over the "ActiveCell.Value" and see the pop up. Is the value of the active cell what you expect? Your code seems to be running fine, but the data may be wrong.


2

You've correctly identified the problem: your references to the worksheet are hard-coded, i.e., you use the name of the sheet in your code rather than a dynamic reference to a sheet. As a result, when you run the code, it affects the worksheet named "Leads_1464523080" instead of the active sheet. To make your code sort the active sheet instead, you need to ...


0

This code in VBA will do it: Public Sub ReqandAppr() '**************** ' variables SourceSheet = "Sheet1" 'name of source sheet DestSheet = "Sheet2" ' name of destination sheet columntocheck = 5 'Column to check "Requested" or "Approved" firstrow = 2 'first row in source sheet destrow = 1 'first row in destination sheet '...


1

=B53/$B$65 Should work. In VBA Dim x as Double x = Range("B53").Value / Range("B64").Value Just format the cell as percentage.



Top 50 recent answers are included