Search results for query: *

I've just made my first venture into using Power Query after years of procrastinating. All I want to say is Wow! Why haven't I been using this all these years.

Excel provides the GETPIVOTDATA() function. GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...) Microsoft defines the pivot_table parameter this way: "A reference to any cell, range of cells, or named range of cells in a PivotTable. This information is used to determine...

I have a simple macro enabled excel template (.xltm) that prompts for a file name and saves the file in the desired location when a new file is created. Sub SetMyName() Dim FileName As String Dim MyPath As String If Sheets("Expense Report").Range("J41").Value = 0 Then 'only if no paid...

Consider a list of n items = ['a', 'b', 'c', ... n] Each item can take a boolean state, i.e. True or False. Represented as a list [True, False] How can I generate a list of lists of all the combinations of states. For the example of items = ['a', 'b', 'c'] states = [True, False] The desired...

The VBA editor has always kindly made suggestions about what methods and properties are available while writing a line of code. For example: Public Sub Do_Something() Worksheets(1). End Sub When I type the "." I expect a drop down list of the available objects, methods and properties...

I have a spreadsheet with protection active and some ranges set to allow editing. The goal is to prevent accidental changes in the protected areas, yet allow editing of the ranges set to allow edits. In the allow edit ranges I can edit cells individually. As expected. However, I cannot use...

I have an Excel spreadsheet that contains hyperlinks to Word documents. The hyperlinks are created using the Hyperlink() function. When clicked the desired document opens in Word. However, Word opens in a window that is the same size as the window of the Excel instance that contains the...

How can the "code" of a function be returned as a string? I'm sure it's possible, but I'm not at all familiar with this aspect of the environment object model. Something like: Function GetFunctionCode (MyFunction as ???) as string GetFunctionCode = MyFunction.{the complete text of the...

I'm trying to restrict data entry in a cell to a list of values from a dropdown. Easy enough. But the values that I'd like to populate the cell with are all abbreviations. I'd like dropdown to to be the fully spelled out phrase, not the abbreviation. I have a Table with two columns. Full...

I'm trying to add a digital signature to a Word document via VBA. So far the closest I have been able to to come is: Target.Signatures.AddNonVisibleSignature This displays the digital signature dialog so that the user can manually select the certificate to use and apply the digital signature...

I have a problem with the tab order on a userform that I cannot understand. My form has the following controls of interest: ComboBox1: Tab order = 0 TextBox1: Tab order = 1 TextBox2: Tab order = 2 CommandButton1: Tab order = 3 CommandButton2: Tab order = 4 Frame1: Tab order = 4 All have...

How do I define a function to return an Array? Seems like is should be straight forward, but I can't find the right syntax. Public Function DueDate(Received As Date, ReplyinDays As Integer) 'Returns an array of three dates: [Due1, Due2, Due3] based on Due3 = Recived + ReplyinDays 'Everything...

I'm trying to figure out how to copy a range out of Doc1 into Doc2 while preserving Styles This works: 'Get M9Content from G9R With G9RDoc 'A Document Object BeginRange = .Bookmarks("Mark2").End EndRange = .Bookmarks("Mark3").Start Set G9R_M9Content = .Range(BeginRange, EndRange)...

Using Windows Explorer to browse the DropBox directory on a local machine there is a Right-Click option to get a public URL for each file. Anyone know how to get this URL via VBA?

We have a SharePoint based document management system that generates assignment emails including a URL on the SharePoint site. SharePoint being SharePoint, things are only fully compatible with Internet Explorer. However the links open in the default browser, which may not be IE. Is there...

For readability and self-documentation I thought I'd try to use UDT's in some code. I get the error "ByRef Argument Type Mismatch" and help isn't helpful, just telling me that data types need to match. So, reduced to a trivial case for the purpose of trying to figure out what to do: Public...

Public Sub MakeAppointment(usingApplication As Object, aStart As Date, aEnd As Date, aAllDay As Boolean, aBody As String, aSubject As String, aLocation As String, aBusy As Integer, aRemind As Boolean) Dim olApp As Object Dim olApt As Object Set olApp = usingApplication Set olApt =...

Reference thread68-1652782 So if you wanted a list of styles that are currently applied to text in a document how would you go about generating it?

I have several styles defined to facilitate writing comments to submitted materials. Things_Number_Style Things_Name_Style 1. Comment_Style 2. Comment_Style 3. Comment_Style etc. Things_Number_Style Things_Name_Style 4. Comment_Style 5. Comment_Style 6. Comment_Style Comment_Style...

This little bit of code in Word inserts a cross reference to the paragraph number of the paragraph where the cursor is located. The cross reference is bracketed by some fixed text, so that the result is: [CDRL crossref] Then it marks that text as a TOA entry. It works, but as you can see...