Questions tagged [vba]
Visual Basic for Applications; a subset of Visual Basic used in all Microsoft Office applications. Consider using this tag together with the tag for the specific application in question. Good questions on VBA generally contain some code and asks for solution of specific issues. Questions asking for entire solutions (please give me the code / how to do this and this with VBA) are off topic and will be closed as too broad.
3,743
questions
3
votes
2
answers
18k
views
How to implement MATCH and INDEX functions together in Excel VBA?
I am trying to find the row number of the first cell that is less than zero.
So, by browsing through the net I was able to come up with this formula which works perfectly fine:
=MATCH(TRUE,INDEX($...
- 63
3
votes
1
answer
3k
views
VBA to save document in Excel and Word periodically (AutoBackup)
I want to create a VBA script that would save a copy of the document every 5-10 minutes with a new name. The script would keep like 10-15 copies in the same folder, incrementing a number each time. If ...
- 16.2k
3
votes
3
answers
13k
views
Excel: conditionally format a cell using the format of another, content-matching cell
I have an Excel spreadsheet where I’d like to be able to create a “key” of formatted cells with unique values, and then in another sheet format cells using the key formatting.
So for example, my key ...
- 133
3
votes
2
answers
19k
views
How to add items on Outlook ribbon that are not "listed" commands?
I use custom tabs on the ribbon of all my MS Office 2010 applications. I would like to add some items that are not in the listed commands on the "Customize the Ribbon" dialog. For example, I'd like to ...
- 131
3
votes
2
answers
48k
views
Is there a way to automate goal seeking formulas in Excel?
I have to use the following formula:
=POWER(b,(d+1))-N*(b-1)-1
I need to find b, so every time I run this I have to select the values for d and N, and then goal seek to make b the calculated value....
- 2,319
3
votes
2
answers
3k
views
3
votes
1
answer
567
views
Automatic reply in Outlook when out of office
For my e-mail account on a Microsoft Exchange Server, I would like that, for each email received where the following two condition are met :
I am in the to: or in cc:
I have an "out of office&...
- 31
3
votes
1
answer
3k
views
Keep as Unread when open/ double click email on outlook
I would like to keep the Read/ Unread Status while I open/ double-click the e-mail on outlook. Unfortunately, It seems that there is not exist any default setting/ option to define the function.
For ...
- 85
3
votes
1
answer
297
views
Transpose unique rows to columns while IF statement conditions are met but do not consolidate
Current State:
A B C D E
FG Component FieldKey Cost$ Qty
7120 7120 1220 $4.71 9,783
7120 9907062 1220 $7.80 4,061
7120 ...
- 45
3
votes
2
answers
306
views
Change multiple full-stop separated sentences in a sentence to Sentence case
I want to convert multiple texts in a sentence to sentence case that includes a full-stop. For example,
this is a Sentence case. this is also a sentence case.
should be
This is a sentence ...
- 53
3
votes
2
answers
10k
views
Is there a way to delete the text within multiple PowerPoint shapes?
I use PowerPoint almost daily as part of my job for reporting. Instead of using text boxes, we like to use Shapes with text inside it to put across ideas e.g. text statements or percentage figures. ...
- 35
3
votes
1
answer
738
views
Microsot Excel - two way mirrored/linked cells with diferent values (one in percentage / another in absolute value)
I have A1 cell formatted as currency (e.g. EUR) - in which I manually enter
base value - e.g. 1000 EUR of which I want make further calculations in B1 and C1 cells.
B1 cell is formatted as percentage (...
- 33
3
votes
1
answer
2k
views
Change excel formula language using VBA
I need to be able to use english formula on my french laptop. Is it possible to use this without installing a language pack?
I have seen this answer from Microsoft:
https://answers.microsoft.com/en-...
- 158
3
votes
3
answers
105
views
Consolidating data from columns to rows
I have a spreadsheet:
Company 1 | ID1
Company 1 | ID2
Company 1 | ID3
Company 2 | ID4
Company 2 | ID5
Company 2 | ID6
Company 3 | ID7
Company 3 | ID8
Company 3 | ID9
I need to combine each company ...
3
votes
1
answer
337
views
Changing the formatting of white-space to match the adjacent formatting, intelligently?
Microsoft Word affords the ability to search for formatted text and to replace it and the formatting. This feature is great in my line of work, where I have to convert Word documents into online ...
3
votes
2
answers
17k
views
VBA - Regex & Replace
I am very new to VBA. Below is my code to identify & remove only those dots which are appearing after dates and not after the text. But it does not seem to work.
Sub simpleRegexSearch()
...
- 31
3
votes
1
answer
3k
views
Logging all changes done by a user into a new sheet with username
I have an excel sheet database, several users log in through different windows accounts to fill fields, is there a way to log changes and entries into a separate sheet in the same excel file.
if the ...
- 31
3
votes
2
answers
167
views
How to make Loop stop
I would appreciate some help with my code below. The code will go thru all the rows and if it finds a certain text string it will execute some code. Then it will continue to the next cell that ...
- 181
3
votes
2
answers
2k
views
How to hide Excel rows based on the criterion all cells in multiple, non-consecutive columns in that row are blank
In Excel, how can I hide rows if and only if all cells in multiple, non-consecutive columns in the row are blank? My worksheet has about 300 columns, so I cannot individually click on every other one ...
- 31
3
votes
2
answers
877
views
How to call command line using vba to list file names
I'm trying to write some vba code that sends a line of code to the command prompt and executes it. I have that part down, but I need help getting the actual code to work.
I want to list all of the ...
- 51
3
votes
1
answer
1k
views
Protect PDF from editing after convert from Excel
Below is the VBA code for converting an Excel file to PDF with the required name of the file. How can I modify this macro to protect the PDF from being edited?
Private Sub CommandButton2_Click()
'...
3
votes
1
answer
2k
views
Calculating partial values for use in a weighted average
I have a spreadsheet with the following information:
Column A contains a list of courses (eg Course 1, Course 2, etc)
Column B contains the number of credits available to each course
Column C ...
- 33
3
votes
1
answer
19k
views
Excel Large Function with Duplicates
I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In ...
- 518
3
votes
1
answer
643
views
Data transformation in MS Excel
looking for help on converting data from one format to another -
If you want to see data in sheet is here -
https://docs.google.com/spreadsheets/d/1yO90XjjN2c80ReYMIXTdS7e8SLb2NNMRNqiXbqR8J2k/edit?...
- 39
3
votes
1
answer
3k
views
Word VBA of CrossReference
I am using Word 2010 and want to create a VBA script to insert a cross-reference.
I often insert a cross-reference of table numbers and figure numbers. So, I want to firstly show a dialog to answer ...
- 61
3
votes
1
answer
2k
views
Excel VBA - User Defined Function to turn string into formula
I'm looking for something similar to the solution posted here:
Excel function that evaluates a string as if it were a formula?
Problem is this does not seem to work with a function in there.
This ...
- 71
3
votes
3
answers
2k
views
Custom Sort using Excel without Custom List
I want to be able to sort excel rows where data looks like
Column - 1 Other Columns
701-GBL-1843-MLMK blah
566-JJB-2785-MYJW
254-WYD-3220-NAND
884-GLE-2843-FRYA
And I want ...
- 33
3
votes
2
answers
9k
views
Automatically save attachments from mail on a network drive
Every morning I receive an automatically generated e-mail from a server, it includes a spreadsheet attachment.
I made a rule in outlook that captures these emails. Now, I would like to make a macro ...
- 31
3
votes
1
answer
4k
views
How to change the default language for all slides in a PowerPoint presentation?
I tried the trick explained in a related discussion, but could not change the proofing language for all texts in all slides of a PowerPoint 2007 presentation.
Could this be a feature of PowerPoint ...
- 3,813
3
votes
1
answer
8k
views
"The visual basic for applications (vba) macros in this workbook are corrupted and have been deleted"
Recently, I found an Excel 2007 Macro Enable Spreadsheet are keep getting the following error message:
The visual basic for applications (VBA) macros for this workbook are
corrupted and has been ...
- 31
3
votes
1
answer
3k
views
link cell text to file of the same name
i have a workbook in excel and in one column i am putting the names of images.
i want to create a macro/vba code so that when i type in the file name into the column e.g. IMG_1234 it will ...
- 33
3
votes
1
answer
2k
views
Random Sampling in Excel
I have an Excel sheet as follows:
NO NAME AMOUNT
1 A 50
1 B 50
2 A 100
2 C 100
3 D 70
3 B 70
4 A ...
- 185
3
votes
2
answers
22k
views
Excel Name.RefersToRange: how to check if Name object refers to a range?
The Microsoft documentation says about RefersToRange:
If the Name object doesn't refer to a range (for example, if it refers
to a constant or a formula), this property fails.
I'm trying to ...
- 3,747
3
votes
2
answers
5k
views
Word macro: how to replace strings in a text, based on a 2 column-table?
So I have a series of strings that I'd like to search and replace in Word documents.
My strings would be displayed as a two-column table. I'd probably create the table with Word, then would convert ...
- 66
3
votes
4
answers
43k
views
How can I create proportionally-sized pie charts side-by-side in Excel 2007?
I have a pivot table with two sets of data as follows:
2011 2012
Slice A 45 20
Slice B 33 28
Slice C 22 2
I am trying to present two pie charts side-by-...
- 805
3
votes
3
answers
4k
views
How to add URL in a cell comment using VBA?
I am using Microsoft Excel 2003 wherein i have a cell which has a comment. The comment has text as well as URL. Is it possible to create a hyperlink (URL) in comment box where user can click and it ...
- 545
3
votes
2
answers
797
views
Word VBA: Hyperlink .Range.Font.TextColor.RGB and compatibility mode
Could someone please let me know why the first line of code below throws error 445 (Object doesn't support this action) when run on a Word document saved in compatibility mode? The second line works ...
3
votes
1
answer
287
views
I need help creating a macro to change my default image settings
My most often used wrap and image settings in Microsoft Word are:
Select Layout options
Select 'through' layout
Click on the more tab
Set horizontal to relative to page
Set vertical to relative to ...
3
votes
1
answer
1k
views
Excel: VBA reports different shape width when the same file is opened on different computers
I'm scratching my head why. Both computers are running Win7 pro 64 bit, and the same version of Excel (Version 1708, Build 8431.2236). There are 8 images inserted in the spreadsheet. This is what I ...
- 133
3
votes
1
answer
426
views
Remove break lines in wrapped cells
I want to get rid of unnecessary break lines from cells and leave the ones I need to separate wrapped text. I'm outta ideas how to distinct them from each other and remove some of them which are ...
- 355
3
votes
1
answer
3k
views
Writing Numeric Values from Excel Cells to Text File without Scientific Notation in VBA
I'm having some trouble with a VBA module in Excel. I have a script that references cells in my spreadsheet and writes the values in comma separated lines to a text file.
The way I do it boils down ...
3
votes
1
answer
7k
views
Excel VBA - Run macro if cell in range is clicked and selected
I need a routine that can identify if a cell was clicked in a range and then run a macro. I've got this solved partly.
My problem is:
I need to toggle a value whenever the cell is clicked, not ...
3
votes
3
answers
4k
views
Excel VBA filter String contains Numbers
I have created a Formula in Excel VBA. which Extract String containing Number from a Cell.
for example a string contains : "121A Nariman Street" it will extract "121A"
Below is the Code
Function ...
- 133
3
votes
2
answers
4k
views
Apply Conditional Formatting VBA to All workbook Sheets
I am very new to this, I am making conditional formatting to highlight numbers above the average of all values in a row.
I need to apply this formatting to all rows in all sheets in the workbook.
...
- 31
3
votes
1
answer
1k
views
VBA Pulse Animation in PowerPoint 2013 using Excel 2013
I hope I'm in the right SE. The posts I found directed me to post here.
BLUF: I am trying to use an if/else statement to apply/remove the pulse animation on a specific object in PowerPoint.
...
- 41
3
votes
3
answers
397
views
How can I make a script wait before continuing?
I found this fantastic vba script which takes an address and returns the latitude and longitude using google's maps API. However, google has rate-limiting features, and I want to do this for a large ...
3
votes
1
answer
3k
views
Upgraded to Windows 10 - Macros Broken
Problem:
I finally upgraded to Windows 10 and a few macros in Excel 2010 that I use frequently no longer work. They crash instantly when I run them and all I receive is an "automation error". They do ...
3
votes
1
answer
3k
views
Regex to find commas, excluding commas inside a string demarcated by double quotes
I would like to manipulate a closed .csv file without opening it through a button on an Excel AddIn. I am able to complete all the steps but am having a problem with finding a regex that would ...
- 31
3
votes
1
answer
6k
views
Freeze multiple columns in excel using macro
Under the Freeze panes, Excel currently offers Freeze panes, Freeze first row and Freeze first column options.
I was looking for an option where I can freeze the first N-columns.
I did a record macro ...
- 4,074
3
votes
1
answer
1k
views
Excel "Smart" Dropdown / Data Validation
In Excel, is there a way to create a dropdown menu or data validation field that
Allows the user to enter a value that is not currently on the list (like a combo box)
Save that value in the dropdown ...
