VBA stands for Visual Basic for Applications, a subset of Visual Basic used in all Microsoft Office Applications. Consider tagging your question with the application as well as [VBA]
0
votes
0answers
13 views
Runtime Error 1004 Unable To Set the Locked Property of the Range Class
Have searched around but I could not find the answer. The only thing it pointed to was merged cells however I do not have any. If someone wants to look at the file, I can only email as its too big to ...
2
votes
1answer
55 views
How to automate transforming a table into a matrix
I've been looking for a way to automate transforming data in an Excel table into a matrix.
For example, from the left table into the matrix on the right, as shown in the following image:
Apparently ...
0
votes
0answers
18 views
How do I control MS Access navigation pane by macro or VBA?
I would like to use either macro's or VBA to programatically control the Access Navigation Pane (i.e. hide and unhide it). I've found plenty of resources to hide the Navigation Pane (e.g. ...
0
votes
1answer
42 views
Select activation method from dropdown instead of writing it yourself
Answer at bottom of post
I got the code below to work but a question remains. When I copy and paste the code into my project it won't work. But inserting the outline of the call from the dropdown ...
0
votes
1answer
38 views
Is it possible to build a cumulative formula that will sum on new worksheet added daily?
I have a worksheet that the user opens each day and clicks a button that copies the current sheet to a new sheet and renames (Day 1, Day 2, etc.).
I want to keep a cumulative count in some of the ...
0
votes
0answers
34 views
Why does my Excel macro fail when run from a hotkey and work when run from the macros ribbon?
I am really confused by what is going on with a macro I created to parse out space delimited files. The macro runs successfully as long as I select the macros ribbon and the macro from the dialog box. ...
1
vote
0answers
20 views
Can VBA be used to get the creation/modified date of attachment itself, not the email?
I would like to see the creation and modified dates of an Outlook attachment using VBA. No such property seems to exist.
Public myAhmt As Attachment
Public crDt As date
crDt = myAhmt.CreationDate 'no ...
0
votes
0answers
16 views
Get rid of the warning box when save an Excel macro-enabled file? [duplicate]
When save such .xlsm file, I got the below snapshot warning which is quite annoying every time I hit Ctrl-S to save.
How can I get rid of it?
-1
votes
0answers
36 views
Using VBA to automatically assign gender in excel spreadsheet [closed]
I have to do an analysis on an online membership database within excel, but for some reason, some of the cells under the Gender column remain unfilled. I have found moderate success by downloading an ...
2
votes
1answer
37 views
Excel visual basic function across workbooks?
This seems like it should be a common issue, but I couldn't find anything on it, maybe I'm just searching for the wrong thing.
Basically I get sent a lot of workbooks that need specific modifications ...
0
votes
0answers
10 views
How do you wrap text in an Active X Listbox?
I created an 11 column Active X Listbox. Is there any way to wrap text in a specific column? I have a column that includes customer verbatim from a survey that needs to be wrapped. Some customer ...
0
votes
1answer
44 views
Optimally positioning pie chart data labels in Excel with VBA
I have a worksheet where I create a pie chart from data from a database. The chart changes based on the data it is displaying so the slices often change size.
I want to display the data labels ...
-1
votes
1answer
18 views
How do I use VBA to choose a range depending on what's in another range?
I'm trying to figure out VBA and Excel and I've ran into some problems. I'm trying to select a range, and depending on if another column(P) is empty, I'll choose either column N or M to select.
...
1
vote
1answer
34 views
LINEST function in excel vba
I am trying to figure out the 2 order equation (Y = ax^2 + bx + c) for a set of data values. When I use the inbuilt function in excel
=LINEST(C2533:C2537,B2533:B2537^{1,2})
I get the following ...
1
vote
2answers
75 views
Excel 2010 Autofilter does not filter
I have been struggling with this for several days now and I would appreciate any help you may be able to provide. I have a sheet with 69 columns and 6600 rows called Raw Data. I also have a sheet ...
1
vote
1answer
30 views
Excel - How to change code to Workbook and not worksheet
I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Set KeyCells = Range("C9")
Set isect = Application.Intersect(KeyCells, Range(Target.Address))
If Not isect ...
0
votes
0answers
30 views
VBA - how to read out the objects of an object library
I would like to read out the objects of an object libarary (MSWORD.OLB) into a file, so that I can not only view each object individually (as it can be done using the object catalogue F2 in the ...
0
votes
0answers
49 views
How can I copy/move values below the matching pattern to another column?
I have found this script from this site which moves matching cell values to another column.
Sub MoveData()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("B:B", ...
2
votes
2answers
271 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:
...
0
votes
1answer
61 views
Change VBA Editor Color Scheme to something darker
Is there a way to change the color scheme in the VBA editor that comes with Microsoft Office? I have been spending several hours lately working with VBA in Excel, and the bright white background is ...
-2
votes
1answer
51 views
macro problems with loop [closed]
if I have in cell "A1" any value like " my name " i have to separate tis value to many cells to be shown at cell "A2" (M) , cell"A3" (y), etc. . after that i have to look up for every cell value from ...
0
votes
3answers
75 views
How to update cell references when setting the Formula property in VBA?
Here is a snippet of my code:
For i = 1 To k
If Range("D2").Offset(i, 0).Interior.ColorIndex > 0 Then
Else
Range("D2").Offset(i, 0).Formula = Range("D2").Formula
The ...
0
votes
0answers
44 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 ...
1
vote
2answers
120 views
Passing Range to ABS function in VBA
I would like to perform a function which looks like this
Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))"
Since my range keeps varying I would like to pass the range as a variable which looks ...
0
votes
1answer
44 views
Excel popup window after using macro
This is the excel sheet i am using
When i run this macro
Sub Value_fla()
Dim k As Long
With ActiveSheet
k = .Cells(.Rows.Count, "D").End(xlUp).Row
End With
...
-3
votes
1answer
29 views
It is VB.net what I am looking? instead of VBA
I was programing in VBA for a couple of months but sometimes I need some feautures there are not integrated in VBA and I was thinking maybe to start to programming in VB.net and use the excel's cells ...
0
votes
0answers
56 views
Simple VBA program to pick a calendar folder and dump all the contents into excel file
I am going to customize an outlook application through VBA. My requirement is such that, my colleagues shared a calendar with me and i want to see their calendar status through automation in outlook. ...
0
votes
2answers
72 views
Can't save Excel VBA as xla
I have my VBA code ready to have others test, and i was going to give it to them as an Add-in (.xla). I had tested this with some other code, to make sure i could do it, and was able to create an .xla ...
1
vote
0answers
56 views
Bring back the object browser in Word 2013
In previous Word versions there was a very handy feature called "object browser" that allowed you to quickly navigate a document. Here is a very simple explanation of what it does: ...
0
votes
1answer
123 views
Word macro that loops through a table and stops at the end
I have movie scripts. First column is time code, second column is speaker, third column is dialog.
I want to loop through the entire script, but only doing something to the third column (dialog).
I ...
0
votes
2answers
43 views
How can I hide column when range is shown (not hidden) in Excel
I'm looking for a way that will hide row "I" if range: QTYCALCON is shown.
So far I came up with this.
Sub Hide_Show()
With Range("QTYCALCON").EntireColumn
.Hidden = Not .Hidden
End With
End Sub
...
0
votes
1answer
63 views
Is it possible to create a macro in PowerPoint 2007 that controls the size and location of chart objects
I'm trying to add a little visual basic to a PowerPoint slide. I usually work in Excel and was surprised to learn PowerPoint doesn't offer a record feature. My slide currently has six charts on it ...
2
votes
0answers
56 views
Fitting images to document's margins in a docx file
I've got a docx file with many figures, all do not fit the margins of the document. I can manually adjust the sizes of the figures in the file, but would love to have some way to automate this ...
1
vote
1answer
66 views
Office 2007: How to sort an Excel Table from within a Word macro
I have written a Macro in WinWord2007 which lists styles and their properties in an Excel table. The table is made accessible by the following 3 lines:
Dim Oxl_Sheet As Object
Set Oxl_Sheet = ...
0
votes
1answer
68 views
creating multiple plot per level of factor (trillis graph) in excel
Is there way to automate the process to make graphs like the following (also known as trallis graph) in excel ? I know there are some R interface with Excel (some of them need to be purchased), but I ...
0
votes
2answers
106 views
How can I turn a Calculated Column back into a data column in an Excel Table?
I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...
0
votes
1answer
107 views
How to count instances a certain style has been applied via VBA?
In Word one can click the pull-down menue of a style in the style-window and it shows how often that style has been applied throughout the active document. So this is an information which is readily ...
0
votes
1answer
103 views
In Excel 2007, in VBA editor, why when I type Worksheets(1). , Excel does not list the members of the Worksheet object? (and how to fix this)
The picture that explains the question:
Notes:
I have Tools: Options: Auto List Members option selected
Ctrl+Space fails
I know that I can use the Object Browser to see, what members I can use ...
1
vote
1answer
67 views
How to inspect / view an Excel macro before “enabling macros” for a sheet?
I'm receiving occasionally an xls file that contains a macro which I should use. If I don't have complete trust in the sender, I wish to look at the macro's source in order to verify that it indeed ...
0
votes
1answer
84 views
Cutting and Pasting in Excel Macro
I have approximately 9,000 columns (goes to column MGN) which have 14 rows of data points. I need to get the data in a single column with the format Column B below A, C below B, etc.
Is there any ...
0
votes
1answer
68 views
Extract original file name from a file
We have a bunch of Excel documents which gets tossed around a lot. Every time somebody makes a copy of a file, in windows it gets generated as 'Copy of - <filename>.xlsm'. We would like to ...
0
votes
0answers
38 views
Unmerging vertically in excel using VBA
I have an excel sheet which contains merged data cells.
multiple classification is being done using the merged cells.
I want to unmerge tham and dump the raw data into anotehr sheet.
this is my task.
...
1
vote
1answer
34 views
Need to input four numbers and output a formatted list
I've got a handwritten list of about 500 coordinates for sprites on a sprite map in the form X1 Y1 Width Height
I need them outputted into a table with format {X1, Y1, X1+Width, Y1+Height}
I would ...
0
votes
1answer
99 views
How to select all empty cells below current selected until first non-empty cell in VBA
I am trying to write a macro that copies the current cell and pastes the text into every empty cell below it until it reaches the first non-empty cell.
So if I had "Apple" in cell B2 and "Orange" in ...
3
votes
2answers
269 views
Excel - concatenating cells where reference number matches [duplicate]
I have a worksheet with many rows. Some of the rows share reference numbers (contained in, say, column A). What I'd like to do is, where a row shares a reference number with another row, I'd like to ...
0
votes
1answer
133 views
Word: update document location field, that has not been saved yet
So since VBA doesn't has a onSave or afterSave event i have troubles finding a way to save my document AND updateing the Document-Location (after it has been saved).
So the most common way this ...
0
votes
1answer
82 views
Nested for each loop 10 by 10 gives over 1000 loops
I'm seriously dumbfound by this issue, I have a nested for-next loop that gives over a thousand loops I know this from seeing this code:
'find row ranges for department 1-10
For i = 1 To 10 Step ...
0
votes
1answer
152 views
Find Related Messages in Outlook 2010 with New/Popup Window like in 2007
In Outlook 2007, to help clear the inbox, finding related messages would open a new small window that would allow to open the messages or to move them into folders easily, then go back and do the same ...
0
votes
2answers
253 views
Automatically import a macro to Excel and run it
I have made a module i.e. a .bas file which does the work I want to do in Excel. I have to work with this same module in four different files everyday.
Now I want, let's say, a script which ...
0
votes
1answer
123 views
IE8: Pressing 'Open' in Download Dialog saves file to a temporary, non-writable location
I have a specific problem with IE8. Our users download excel files via IE8. Those excel files contain macros which create pivot tables. In order to do that, excel 2007, needs the file to be writable ...
