Microsoft Excel




Articles & Videos



Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

i have this solution but need to improve with vba
to count the triples from 4 numbers
SharePoint Admin?
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

I have been attempting to use this Macro that I found through VBA Express.  The Macro successfully disables the ability to right-click cut/copy/paste but the keyboard shortcuts are still active.  Any help is greatly appreciated.  I have very little knowledge about writing Macros and usually go to Google to find already existing codes to use.  Please see below:

'*** In a standard module ***
Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
I had this question after viewing Produce the count value next to each filterable item in a spreadsheet.

I am using this macro with data from another sheet and the results are cut-off due to all the data not fitting in dialog.

If possible, the results dialog should automatically expand as necessary to fit all the text to the right, and be in a smaller font size.

I have come across a wonderful (ready-to-use) Excel sample file that allows multiple values to be selected (in single cell) via a drop-down box.   I found the example at:

I would like to modify the code provided by Sumit Bansal.   Specifically, I need multiple drop-down (i.e., for every record in an XLS, I need the drop-down for assigning POCs).

Sumit's code below allows to use the drop-down for cell C2 only though (line: "If Target.Address = "$C$2" Then").   For testing purposes, I tried modifying the code with only two cells.   The proposed VBA "If Target.Address = "$C$2" Or "$C$3" Then", however, does not work.

My question:   If I wanted the drop-down to work for, e.g., 50 rows, how can I change the code?   That is, "If Target.Address = "$C$2:$C$51" Then" does **not** work.

Thank you in advance.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Oldvalue = Target.Value
      If Oldvalue 

Open in new window

I would like to add another condition to the current formula in Column C, which is based on the entry in Col B. The current formula is:

=IFERROR(IF($B2="","",IF(RIGHT($B2,3)="YES","",TEXT(TODAY(),"dd mmm yyyy"))),"")

The format for Col B is always:

I would like to add the following condition to the above formula, based on the entry in Col E:

If Col E = "Cancelled" OR "Deferred", then populate Col C with date from Col E (formatted as above — TEXT(TODAY(),"dd mmm yyyy")

The format for Col E is always:
Cancelled-DD MMM YYYY
Deferred-DD MMM YYYY

Lastly, if both Col B and Col E have entries, then I would like the date from Col E to be entered in Col C, not the date from Col B.

I have attached a sample spreadsheet to show the results I am hoping for.

I have an excel sheet with 6 different room types in a hotel. These room different items and quantities of those items in each room. One room may have 2 lamps, where as the other may have 1. I need to insert the aggregate data into 1 table in order to make an accurate budget of all the items that I will order.

I have a macro that allows for new sheets to be created based off the amount of rows in the "Work Item" column in the posted excel database. Right now the "work item" updates into the worksheets created from the "template" spreadsheet, but the "description of work item" does not update into the "description job"  column in the templates, and I would like it to. Could someone please help me with this? Thanks!
In Excel 2007, how do I change for only one cell from dot to comma as separator for decimal value?

I want to keep the global language setting to English and keep global dot as decimal separator, and only change for this cell to a comma as decimal separator. Is that possible?
Thanks to Shums solution to my last question, I have now moved forward to the final question. The attached worksheet has a routine to add a new line on the first line where there is no value in column B, after B15. This is done with the Add a Line button. There is also an unused button now titled "Compound the Interest."

This routine needs to add a line using the same date in column B as the previous line AND copy the contents of the previous line columns C, F, G, H, I, J and K to the new line AND copy the amount of the previous line's column I to the new lines Column D and Column J.
I have a frame which contains quite a lot of detail, and which has a vertical scrollbar. I want to set the scroll position of the frame, so that the most important content is visible to the user (at the bottom of the frame's scroll-able content), before the user scrolls away to other details in the frame.

I use vba.

I'd settle for presetting the slider to the bottom or top of the scollbar, but would be interested to know if I can pre-position it to any vertical position.

On Demand Webinar: Networking for the Cloud Era
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

In L24, I have a total value for a horse's total number of starts in harness races. In G26, H26, I26, J26 and K26 I have in each cell a value that together add up to the total value in L24.

For example:
L24: 75
G26: 10
H26: 5
I26: 30
J26: 25
K26: 5

I want that in G26:K26, there is also displayed a percentage value within parenthesis after the integer value, and this percentage value is a calculation of the cell's integer value divided with the total value in L24.

In L24, I want that there is also displayed an integer value within parenthesis after the first integer value (in this case 75), as well as a percentage value within parenthesis (this percentage value is a calculation of (G26+H26)/L24).

So in my example, the display would be this:

L24: 75 (15) (20 %)
G26: 10 (13 %)
H26: 5 (7 %)
I26: 30 (40 %)
J26: 25 (33 %)
K26: 5 (7 %)

Is this possible to achieve with the concatenate function (or &), or by using a textbox?

For the moment I will fill in the values manually in these cells, but later I will have each value retrieved/calculated from other worksheets.
So, I have to look through a worksheet and identify where groups of numbers match.

Could someone help me with code which will do this for me please ?

Also, would it be possible to identify each matching group of number with their own colour ?

I have attached a worksheet to illustrate

I want users to be able to add a line in the attached worksheet. Currently, there is a crude macro (Ctrl Shift L) that adds the line. In order for the macro to work, you must be on the first blank like in Column A.

I want the macro to automatically find the first cell below B15 that is blank (in the of the attached worksheet, B46) and automatically copy C45 to C46, F45 to F46, G45 to G46, H45 to H46, I45 to I46 and K45 to K46 and the cursor ends up, once again in the case of the attached worksheet, in that first blank cell in Column B, B46.

I am wondering if there is a way to set a column of a spreadsheet to not show when printing. The column is in the interior of the spreadsheet and I don't want the user to have to manually select a range for printing that does not include this column. Is there some VBA code that could work for this? It's column C on a spreadsheet called summary.

Thanks so much!

How do you modify Excel VBA macro code (containing a keyboard timestamp shortcut) so that, each time the macro is invoked, it reproduces the actual shortcut keystrokes (and therefore the current time) rather than the "absolute" time value the shortcut produced when the macro was originally recorded?

After clicking to Use Relative References (ie Developer > Use Relative References), the Excel macro recorder was used and created the following code:

Sub Macro12()
' Macro12 Macro

    Application.Goto Reference:="DateEnd"
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "11:59:00 AM"
    ActiveCell.Offset(0, 3).Range("A1").Select
End Sub

Open in new window

The relative actions (keystrokes) appear to have been recorded properly except for the following row:
ActiveCell.FormulaR1C1 = "11:59:00 AM"

Open in new window

At this particular part of the recording, the shortcut [Ctrl+Shift+;] was used to enter the current time. As is shown above, the time when the macro was being recorded was apparently 11:59 AM. However now, each time the macro is run, it does not enter the current time as it should but enters 11:59 AM.

So how must that row of code be modified so that each time the macro is run in the future, it will reproduce the keystrokes shortcut and post the current time then instead of always entering 11:59 AM?

By the way, I …
I have a pivot table which doesn't seem to sort chronologically. I want it to go by month and year.
See second tab (AMARG).
I am trying to strip the decimals from a negative number. In this instance it is -102.60055560 (a longitude) and everything I try Excel rounds it to -103. That of course would put it in a different county. I am just looking to have the -102 result, without rounding
This formula looks right to me, but Excel returns an error saying too few arguments...

This formula is in cell K8:

=IF(   OR(         IFERROR(      VLOOKUP($G8,'[The Box Plan 06-19.xls]The Box Plan'!$G$5:$O$113,5,FALSE)      )  ,  IF(VLOOKUP($G8,'[The Box Plan 06-19.xls]The Box Plan'!$G$5:$O$113,5,FALSE)=0)),   "",
VLOOKUP($G8,'[The Box Plan 06-19.xls]The Box Plan'!$G$5:$O$113,5,FALSE))

The intent of the formula is that if either the IFERROR logical condition OR the IF(VLOOKUP(...) condition is met, then blank cell K8; otherwise run the VLOOKUP formula and put its result in K8.

I've looked this over and cannot find the error in it. Can one of you tell me what to do to fix the error condition?


I've created a questionnaire in Excel for team members to complete, that is then assembled into an Excel report.  To pull the data from each of the questionnaires and create the report I've had someone create a macro for me.  However, I realised it doesn't work quite as intended and haven't been able to contact the macro author.  The macro is meant to copy a range of cells (always F1:F40 on the "Copy" sheet) in a set of 'source' workbooks and paste them in, next to each other on the "Input Sheet" of a 'target' workbook, starting at F5, then G5, H5 etc.  The macro is run from the target workbook.  All the source workbooks will be held in the same folder as the target report workbook, so the macro opens every other wookbook in the folder, copies the data, closes it, and moves on to the next workbook to copy that one, and so on.
The macro almost works but has two issues:
It seems to copy the range of cells on the first workbook correctly, but then copies the next column to the right (G1:G40) which is blank) from the next workbook, rather than the same range F1:F40 .  This is probably because it also has to paste into the next column the right in the target workbook.
The second problem is that the macro is pasting the formulas rather than the values from the cells and after pasting some of them appear to be referencing the wrong cells, though I'm not sure why.  Is it possible to have values rather than formulas pasted?

Could anyone shed some light on what is going wrong and…
[Webinar] How Hackers Steal Your Credentials
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

I have a spreadsheet (Office 2013) in which I have data down 2 columns. The issue is that the data needs to be split into more a database structure of columns with corresponding data under each column. I have included a copy of the type and format of the data. The sections are separated by one blank row too.

Name      Microsoft.VCLibs.140.00
Publisher      CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
Architecture      X64
Version      14.0.22929.0
PackageFullName      Microsoft.VCLibs.140.00_14.0.22929.0_x64__8wekyb3d8bbwe
InstallLocation      C:\Program Files\WindowsApps\Microsoft.VCLibs.140.00_14.0.22929.0_x64__8wekyb3d8bbwe
IsFramework      TRUE
PackageFamilyName      Microsoft.VCLibs.140.00_8wekyb3d8bbwe
PublisherId      8wekyb3d8bbwe
IsResourcePackage      FALSE
IsBundle      FALSE
IsDevelopmentMode      FALSE
============Blank Row============================================      
Name      Microsoft.VCLibs.140.00
Publisher      CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington, C=US
Architecture      X86
The position of the pixel is given as number of pixels from top left corner of the computer screen. (column, row) I'd like to get some number / text based on the pixel color so that I can see if a point on the screen changed color. (for automation, I'd like to see changes in the state of the program, eg when it stopped loading).

It doesn't matter too much if it's a slow method, I'm only planning to read <10 pixels at a time.

Solutions I found online seem to be for different versions and don't work.
I tried right-clicking on it, and it says it is locked. So I unclicked that option, and closed the dialogue. But still when I try to delete it, the cursor just jumps two rows down and nothing happens.

I have an excel database that estimates project costs, I have attached the database after removing all the confidential information and just added arbitrary values. In the main summary table, there are work items and for each work item there are costs. In the database I posted I just showed one example of how the work item costs are being calculated and how the tables relate to each other.

Basically, for each work item I need a sheet to be created like the template in the database I posted. So I am trying to create a button that creates the same number of new sheets as there are work items in the "Work Item" column, instead of the user having to create all the sheets themselves and copy and paste in the template. Because in an actual estimation there could potentially be like 60 work items... But for the case of the database I posted there would be 4 sheets after the summary table, including the one that is already there, that each provide a summary of the how the costs for that work item were calculated.

Is there code that I can utilize for the button for this to happen? To calculate the amount of values in the "Work Item" column and then create that number of sheets all with the same layout as that template tab? Eventually I am going to need to also figure out how to get each of these new sheets to relate back to the summary table like how the template does now..

Any help is appreciated! Let me know if I need to explain myself better.
I have for example line items with different quantity amounts and wanted the very first line item (row) to have the total and delete the rest of the rows.  In this case I have 1000's of line items.

Please See attachment
Here's a softball for you Excel experts: I want to extract the string between the dashes ("-"), e.g.:

"AC00710003 - Process Air Distribution - FS Review & Approve in AZDocs Pre-ACO" should return "Process Air Distribution"

What is the correct formula?

Microsoft Excel




Articles & Videos



Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.